Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
Hub
New Contributor

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,

6 REPLIES 6
brazz_FTNT
Staff
Staff

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
New Contributor

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
New Contributor

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
New Contributor

Ok, i add it.

 

Labels
Top Kudoed Authors