Help creating custom reports & Issue with Fortiview Filtering via Policy ID

[Rajesh0032]

Sir , we are using FOrtiAnalyzer VM64 with multiple Fortigate FW's   .kinldy help me create following Reports 1) Create Daily Total Bandwidth Used   ( Inbound   & OutBound ) Like Downloaded :  X GB Uploaded  :   X GB Currently there are reports which showing Top 20 Bandwidth Users 2)In Fortiview Tab when filtering  via Proxy Policy ID 571  it is showing other connection Blocked events also instead of showing Just Events  with Proxy Policy ID 571  , how to 3)In threats Reports its showing Malware source IP , Hostname   , Is is possible to show MAC Address also  [when i go to fortiview >Select malware > Select IP > i am able to see details like source Hostname & Mac address 4)Reports of  VPN /P2p Users Like    catagory = Utorrent Hostname & Ip address Hostname & Ip address catagory = Vuze Hostname & Ip address

or

Hostname - IP - Utorrent

thank you

[hzhao_FTNT · Accepted solution]

>is there any way to link that information also so it looks like Source IP - Source Mac - Destination Domain-Malware Types - Count -Malware Names > eg > 192.168.10.1 AA:BB:CC:DD:EE:FF - X.X.X.X - Virus- 10-Zeus Virus --- Yes, just add a field to my previous dataset: select srcip, srcmac, domain, (case when virus_s like 'Riskware%' then 'Spyware' when virus_s like 'Adware%' then 'Adware' else 'Virus' end) as malware_type, sum(total_num) as total_num, virus_s from ###(select unnest(string_to_array(virus, ',')) as virus_s, srcip, srcmac, coalesce(nullifna(hostname), ipstr(`dstip`)) as domain, count(*) as total_num from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and virus is not null group by virus_s, srcip, srcmac, domain order by total_num desc)### t group by srcip, srcmac, domain, malware_type, virus_s order by total_num desc

>i think somewhere there is some issue with obtaining data ( i was running for query for time "Today" , Even check for yesterdays only in the above query its giving output of TB's . can i limit this query to perticular port only , say on Port 20

--- This is a special dataset, you have to create a chart based on it. When add the chart into layout, you MUST specify a dstintf filter on it. For example, dstintf=wan1, then run report to see the output

> 2)In Fortiview Tab when filtering via Proxy Policy ID 571 it is showing other connection Blocked events also instead of showing Just Events with Proxy Policy ID 571 , how to

---Unable to reproduce on 5.2.7, could you upgrade your faz?

[hzhao_FTNT]

Hi there,

1. In FAZ 5.4.1, we have a predefined chart. If you have a 5.2 FAZ, try below dataset: log type: traffic with qry as (select dom as dom_s, devid as devid_s, vd as vd_s, srcintf, dstintf, total_sent, total_rcvd from ###(select $DAY_OF_MONTH as dom, devid, vd, srcintf, dstintf, sum(coalesce(sentbyte, 0)) as total_sent, sum(coalesce(rcvdbyte, 0)) as total_rcvd, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as total from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(srcintf) is not null and nullifna(dstintf) is not null group by dom, devid, vd, srcintf, dstintf having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by total desc)### t) select dom, unnest(array['download', 'upload']) as type, unnest(array[sum(download), sum(upload)]) as bandwidth from (select coalesce(t1.dom_s, t2.dom_s) as dom, coalesce(t1.devid_s, t2.devid_s) as devid, coalesce(t1.vd_s, t2.vd_s) as vd, coalesce(t1.srcintf, t2.dstintf) as intf, sum(coalesce(t1.total_sent, 0)+coalesce(t2.total_rcvd, 0)) as download, sum(coalesce(t2.total_sent, 0)+coalesce(t1.total_rcvd, 0)) as upload from qry t1 full join qry t2 on t1.dom_s=t2.dom_s and t1.srcintf=t2.dstintf group by dom, devid, vd, intf) t where $filter-drilldown group by dom order by dom

3. srcmac is not available in virus log, try below custom dataset log type: traffic select srcip, srcmac, coalesce(nullifna(hostname), ipstr(`dstip`)) as domain, count(*) as totalnum from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(virus) is not null group by srcip, srcmac, domain order by totalnum desc

4. For application control we do have app category "P2P", but do not have VPN. See [link]https://fortiguard.com/appcontrol[/link] If you are looking for a application user drilldown chart, for FAZ 5.4.0+, try to use our predefined chart "Top 10 Applications Bandwidth by User Drilldown" and "Top 10 Applications Sessions by User Drilldown", add a filter appcat=P2P to report or chart.

Regards,

hz

[Rajesh0032]

hzhao_FTNT wrote:

Hi there,

1. In FAZ 5.4.1, we have a predefined chart. If you have a 5.2 FAZ, try below dataset: log type: traffic with qry as (select dom as dom_s, devid as devid_s, vd as vd_s, srcintf, dstintf, total_sent, total_rcvd from ###(select $DAY_OF_MONTH as dom, devid, vd, srcintf, dstintf, sum(coalesce(sentbyte, 0)) as total_sent, sum(coalesce(rcvdbyte, 0)) as total_rcvd, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as total from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(srcintf) is not null and nullifna(dstintf) is not null group by dom, devid, vd, srcintf, dstintf having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by total desc)### t) select dom, unnest(array['download', 'upload']) as type, unnest(array[sum(download), sum(upload)]) as bandwidth from (select coalesce(t1.dom_s, t2.dom_s) as dom, coalesce(t1.devid_s, t2.devid_s) as devid, coalesce(t1.vd_s, t2.vd_s) as vd, coalesce(t1.srcintf, t2.dstintf) as intf, sum(coalesce(t1.total_sent, 0)+coalesce(t2.total_rcvd, 0)) as download, sum(coalesce(t2.total_sent, 0)+coalesce(t1.total_rcvd, 0)) as upload from qry t1 full join qry t2 on t1.dom_s=t2.dom_s and t1.srcintf=t2.dstintf group by dom, devid, vd, intf) t where $filter-drilldown group by dom order by dom

3. srcmac is not available in virus log, try below custom dataset log type: traffic select srcip, srcmac, coalesce(nullifna(hostname), ipstr(`dstip`)) as domain, count(*) as totalnum from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(virus) is not null group by srcip, srcmac, domain order by totalnum desc

4. For application control we do have app category "P2P", but do not have VPN. See https://fortiguard.com/appcontrol If you are looking for a application user drilldown chart, for FAZ 5.4.0+, try to use our predefined chart "Top 10 Applications Bandwidth by User Drilldown" and "Top 10 Applications Sessions by User Drilldown", add a filter appcat=P2P to report or chart.

Regards,

hz

thank you will check , but how to import this to fortianalyzer ? (i tried opening one of the existing Reports >Exports> Open in Notepad ++ but its not readable

[hzhao_FTNT]

Rajesh0032 wrote:

thank you will check , but how to import this to fortianalyzer ? (i tried opening one of the existing Reports >Exports> Open in Notepad ++ but its not readable

You need create a custom dataset based on my query, then create a custom chart and insert it into layout.  Please check online help for more info

[abelio]

[link=https://forum.fortinet.com/tm.aspx?m=138423#138457] [link]https://forum.fortinet.com/tm.aspx?m=138423#138457[/link][/link]

[Rajesh0032]

Analyzer version =5.2.2   build 0706

 (select dom as dom_s, devid as devid_s, vd as vd_s, srcintf, dstintf, total_sent, total_rcvd from ###(select $DAY_OF_MONTH as dom, devid, vd, srcintf, dstintf, sum(coalesce(sentbyte, 0)) as total_sent, sum(coalesce(rcvdbyte, 0)) as total_rcvd, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as total from $log

syntax error at end of input
LINE 1: ...oalesce(rcvdbyte, 0)) as total from "mgrtmp_r_7fa7ca41ef50" 
                                                                       ^

[hzhao_FTNT]

Rajesh0032 wrote:

Analyzer version =5.2.2   build 0706

 (select dom as dom_s, devid as devid_s, vd as vd_s, srcintf, dstintf, total_sent, total_rcvd from ###(select $DAY_OF_MONTH as dom, devid, vd, srcintf, dstintf, sum(coalesce(sentbyte, 0)) as total_sent, sum(coalesce(rcvdbyte, 0)) as total_rcvd, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as total from $log

syntax error at end of input
LINE 1: ...oalesce(rcvdbyte, 0)) as total from "mgrtmp_r_7fa7ca41ef50" 
 ^

"with qry as (" is also a part of query.

[Rajesh0032]

3. srcmac is not available in virus log, try below custom dataset
log type: traffic
select srcip, srcmac, coalesce(nullifna(hostname), ipstr(`dstip`)) as domain, count(*) as totalnum from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(virus) is not null group by srcip, srcmac, domain order by totalnum desc

Is it possible to add Malware Type also in this table

SrcIP  - SrcMac - Domain - Total Number - Malware Name

4

4. For application control we do have app category "P2P", but do not have VPN. See https://fortiguard.com/appcontrol
If you are looking for a application user drilldown chart, for FAZ 5.4.0+, try to use our predefined chart "Top 10 Applications Bandwidth by User Drilldown" and "Top 10 Applications Sessions by User Drilldown", add a filter appcat=P2P to report or chart.

is there any alternative for v5.2.2

so i can pull reports of users using applications 

Like

srcip hostname destination Category        [category = Proxy  ]

[hzhao_FTNT]

Rajesh0032 wrote:

3. srcmac is not available in virus log, try below custom dataset

Is it possible to add Malware Type also in this table

4

is there any alternative for v5.2.2

so i can pull reports of users using applications 

3. try below query:

select srcip, srcmac, domain, (case when virus_s like 'Riskware%' then 'Spyware' when virus_s like 'Adware%' then 'Adware' else 'Virus' end) as malware_type,  sum(total_num) as total_num from ###(select unnest(string_to_array(virus, ',')) as virus_s, srcip, srcmac, coalesce(nullifna(hostname), ipstr(`dstip`)) as domain, count(*) as total_num from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and virus is not null group by virus_s, srcip, srcmac, domain order by total_num desc)### t group by srcip, srcmac, domain, malware_type order by total_num desc

4. try below query

select app, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(`sentbyte`, 0)+coalesce(`rcvdbyte`, 0)) as bandwidth from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(app) is not null and appcat='P2P' group by app, user_src order by bandwidth desc

Then create a chart, Chart Type: table, table type: drilldown. Column1 data binding "app", column2 data-binding "user_src", column 3 data binding "bandwidth"

[Rajesh0032]

hzhao_FTNT wrote:

Rajesh0032 wrote:

 3. srcmac is not available in virus log, try below custom dataset Is it possible to add Malware Type also in this table

 4 is there any alternative for v5.2.2

so i can pull reports of users using applications 

3. try below query:

select srcip, srcmac, domain, (case when virus_s like 'Riskware%' then 'Spyware' when virus_s like 'Adware%' then 'Adware' else 'Virus' end) as malware_type,  sum(total_num) as total_num from ###(select unnest(string_to_array(virus, ',')) as virus_s, srcip, srcmac, coalesce(nullifna(hostname), ipstr(`dstip`)) as domain, count(*) as total_num from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and virus is not null group by virus_s, srcip, srcmac, domain order by total_num desc)### t group by srcip, srcmac, domain, malware_type order by total_num desc

4. try below query

select app, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(`sentbyte`, 0)+coalesce(`rcvdbyte`, 0)) as bandwidth from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(app) is not null and appcat='P2P' group by app, user_src order by bandwidth desc

Then create a chart, Chart Type: table, table type: drilldown. Column1 data binding "app", column2 data-binding "user_src", column 3 data binding "bandwidth"

Thank you very much

4  . Now i am able to identity users using proxy application and IP's generating P2P traffic easily thank  you

3. Currently its showing Source IP - Source Mac - Destination Domain (which is very helpfull ) -Malware Types - Count

But its not showing which type of malware ( in threat report it shows Zeus Virus /XYZ botnet )  

is there any way to link that information also so it looks like

Source IP - Source Mac - Destination Domain-Malware Types - Count -Malware Names

eg

192.168.10.1 AA:BB:CC:DD:EE:FF -  X.X.X.X - Virus- 10-Zeus Virus 

i guess its the limitation of this Version or fortianalyzer ? will upgrading to higher version solve this issue?

1.

with qry as (select dom as dom_s, devid as devid_s, vd as vd_s, srcintf, dstintf, total_sent, total_rcvd from ###(select $DAY_OF_MONTH as dom, devid, vd, srcintf, dstintf, sum(coalesce(sentbyte, 0)) as total_sent, sum(coalesce(rcvdbyte, 0)) as total_rcvd, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as total from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(srcintf) is not null and nullifna(dstintf) is not null group by dom, devid, vd, srcintf, dstintf having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by total desc)### t) select dom, unnest(array['download', 'upload']) as type, unnest(array[sum(download), sum(upload)]) as bandwidth from (select coalesce(t1.dom_s, t2.dom_s) as dom, coalesce(t1.devid_s, t2.devid_s) as devid, coalesce(t1.vd_s, t2.vd_s) as vd, coalesce(t1.srcintf, t2.dstintf) as intf, sum(coalesce(t1.total_sent, 0)+coalesce(t2.total_rcvd, 0)) as download, sum(coalesce(t2.total_sent, 0)+coalesce(t1.total_rcvd, 0)) as upload from qry t1 full join qry t2 on t1.dom_s=t2.dom_s and t1.srcintf=t2.dstintf group by dom, devid, vd, intf) t where $filter-drilldown group by dom order by dom

using above query i am able to get output  as

Date UPload/download - Bandwidth used

but when i compare the report with Bandwidth and Applications Report (default) its showing

Total Sessions 2,371,367  Total Bytes Transferred  99.59GB

but the above query gives me

 1    2016-08-11    download    2.17 TB  2    2016-08-11    Upload        9.35 TB

i think somewhere there is some issue with  obtaining data ( i was running for query for time "Today"  , Even check for yesterdays only in the above query its giving output of TB's  . can i limit this query to perticular port only , say on Port 20

@thank u again all for helping

 2)In Fortiview Tab when filtering  via Proxy Policy ID 571  it is showing other connection Blocked events also instead of showing Just Events  with Proxy Policy ID 571  , how to