How to create a basic report

[Hub]

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,

[brazz_FTNT]

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 

[Hub]

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 ?

[brazz_FTNT]

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

[Hub]

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.

[brazz_FTNT]

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

[Hub]

Ok, i add it.