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 · 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?

[Rajesh0032]

hzhao_FTNT wrote:

>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?

. i compared both queries and i find the term virus_s is used to grab specific virus names from logs , is there any similar

variables for firstseen or last seen

[i am checking sample on fortianalyzer 5.2.6 dataset reference pdf )

2. i checked in details issue was Malware threats and also Failed connection attempts event is also both having same policy ID , i was trying to filter so it shows only perticular types of events only  , so it was working good only just that malware and blocked connection also shares same policy ID

[hzhao_FTNT]

>   is there any similar variables for firstseen or last seen

yes, min(from_itime(itime)) as first_seen, max(from_itime(itime)) as last_seen

Please note both time will not be out of your specified time period

>it was working good only just that malware and blocked connection also shares same policy ID

maybe you can add a filter "-threattype=failed-connection"  to exclude "Failed Connection Attempts"

regards,

hz

[Rajesh0032]

select min(from_itime(itime)) as first_seen, max(from_itime(itime)) as last_seen, 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 last_seen, first_seen ,srcip, srcmac, domain, malware_type, virus_s order  by total_num desc

ERROR: syntax error at or near "last_seen"
LINE 1: ...rcip, srcmac, domain, malware_type, virus_s order last_seen,...
 

I am able to use exclude option to exclude like P2P botnet ,(all category which is single words) but its not working for

category like Information Technology/Malicious Websites etcs

[hzhao_FTNT · Accepted solution]

Hi there, you need put last/first_seen into subquery:

select min(first_seen) as first_seen, max(last_seen) as last_seen, 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 min(from_itime(itime)) as first_seen, max(from_itime(itime)) as last_seen, 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

P2P is an application category(log field appcat), but Technology/Malicious Websites are web category(log field catdesc). Please visit fortiguard for more info:

https://fortiguard.com/appcontrol

[link]https://fortiguard.com/webfilter[/link]

[Rajesh0032]

can i get one more help adding first_seen and last_seen to below query which shows's P2P users in network

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

[hzhao_FTNT]

try:

select min(from_itime(itime)) as first_seen, max(from_itime(itime)) as last_seen, 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