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
Solved! Go to 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?
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]
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
hzhao_FTNT 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 readableHi 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
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
[link=https://forum.fortinet.com/tm.aspx?m=138423#138457] [link]https://forum.fortinet.com/tm.aspx?m=138423#138457[/link][/link]
regards
/ Abel
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"
^
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.
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 ]
Rajesh0032 wrote:3. try below query:
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
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"
hzhao_FTNT wrote:Thank you very muchRajesh0032 wrote:3. try below query:
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
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"
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
User | Count |
---|---|
1923 | |
1144 | |
769 | |
447 | |
277 |
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.
Copyright 2025 Fortinet, Inc. All Rights Reserved.