Hot!How to create a basic report

Author
Hub
New Member
  • Total Posts : 5
  • Scores: 0
  • Reward points: 0
  • Joined: 2018/06/21 05:25:09
  • Status: offline
2018/07/09 09:06:00 (permalink)
0

How to create a basic report

Hello,
I have to ask something that IMO should be very easy but i don't know how to do it.
I just want to see 4 columns :
Source IP , Destination IP, Port, Session count
And group the lines by source and destination.
 
It should look like this :
source ip            dest ip            Port                   Session
192.168.1.1       172.17.5.1      80,                     700
                                              443,                   800
                                              389                    200
 
192.168.1.1       172.17.5.3      80,                     700
                                              443,                   800
                                              389                    200
 
192.168.1.2       172.17.5.1      80,                     700
                                              443,                   800
                                              389                    200
 
192.168.1.2       172.17.5.5      80,                     700
                                              443,                   800
                                              389                    200
 
Did someone allready do something like that. I'm really bad in Postgre and nothing in fortiview or log view allow me to show things like that.
 
Thank you in advance for all your help.
 
Regards,
#1

6 Replies Related Threads

    brazz_FTNT
    Bronze Member
    • Total Posts : 13
    • Scores: 8
    • Reward points: 0
    • Joined: 2018/02/20 15:09:34
    • Status: offline
    Re: How to create a basic report 2018/07/09 11:18:52 (permalink)
    0
    Hello, 
     
    You may try using below dataset. 
     
     
    SELECT ipstr(`srcip`) || ' | ' || ipstr(`dstip`) as srcip_dstip, dstport, COUNT (*) AS sessioncount FROM $log WHERE $filter and dstport IN (80,389,443)  GROUP BY  srcip_dstip, dstport  order by sessioncount desc
     
    After you created this dataset you need to create a chart ;  select chart type  as  table, and  then select drill-down . You need to insert it to your report. 
    Generating reports--->P{92}
     
     
    Regards 
     
     
     
     
    post edited by brazz_FTNT - 2018/07/09 11:35:41
    #2
    Hub
    New Member
    • Total Posts : 5
    • Scores: 0
    • Reward points: 0
    • Joined: 2018/06/21 05:25:09
    • Status: offline
    Re: How to create a basic report 2018/07/10 02:14:22 (permalink)
    0
    Thanks a lot brazz,
    With some very little tuning from my side, it's perfect.
    Do you have an idea to how i can add the bandwidth used for each port ?
    #3
    brazz_FTNT
    Bronze Member
    • Total Posts : 13
    • Scores: 8
    • Reward points: 0
    • Joined: 2018/02/20 15:09:34
    • Status: offline
    Re: How to create a basic report 2018/07/11 08:04:11 (permalink)
    0
    Hey , 
     
    I suggest using below dataset :
     
     
    SELECT ipstr(`srcip`) || ' , ' || ipstr(`dstip`) as srcip_dstip, dstport, COUNT (*) AS sessioncount, SUM(`sentbyte`+`rcvdbyte`) AS bandwidth FROM $log WHERE $filter and dstport IN (80,389,443) GROUP BY srcip_dstip, dstport order by sessioncount desc
     
    Let me know how it goes. 
     
    Cheers
    #4
    Hub
    New Member
    • Total Posts : 5
    • Scores: 0
    • Reward points: 0
    • Joined: 2018/06/21 05:25:09
    • Status: offline
    Re: How to create a basic report 2018/07/11 08:25:22 (permalink)
    0
    Hello,
    Thank you again,
    My needings changed since yesterday, so i created this dataset (with your help) :
     
    SELECT srcip, dstip, srcintf, dstintf, dstport, COUNT (*) AS sessioncount, SUM(`sentbyte`+`rcvdbyte`) AS bandwidth FROM $log WHERE (( `srcip` <<= inet('192.168.1.0/24'))  AND (lower(`dstintf`) = lower('npu0_vlink1'))  AND ((`dstport` < 10240 ))) GROUP BY  srcip, dstip, srcintf, dstintf, dstport ORDER BY srcip
     
    Which give me something like this :
     
    srcip     dstip     srcintf     dstintf     dstport     sessioncount     bandwidth
    192.168.101.4     172.17.10.11     DMZ     npu0_vlink1     80     8     24932
    192.168.101.4     172.17.10.12     DMZ     npu0_vlink1     80     24     1175722
    192.168.101.4     172.17.10.12     DMZ     npu0_vlink1     443     123     3976571
    192.168.101.4     172.17.10.20     DMZ     npu0_vlink1     123     2     384
     
    Which is perfect for me.
     
    Thank you again.
    #5
    brazz_FTNT
    Bronze Member
    • Total Posts : 13
    • Scores: 8
    • Reward points: 0
    • Joined: 2018/02/20 15:09:34
    • Status: offline
    Re: How to create a basic report 2018/07/11 08:35:49 (permalink)
    0
    Thanks for the update. 
     
    Great! ;)
    Just one point, after "WHERE" add "$filter AND ..." just in case if you decide to use the filter section on your Reports. 
     
    Cheers
    #6
    Hub
    New Member
    • Total Posts : 5
    • Scores: 0
    • Reward points: 0
    • Joined: 2018/06/21 05:25:09
    • Status: offline
    Re: How to create a basic report 2018/07/11 08:41:06 (permalink)
    0
    Ok, i add it.
     
    #7
    Jump to:
    © 2018 APG vNext Commercial Version 5.5