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

Help with custom report

Hello! I have a report that calculates the total amount of traffic. Please help correct the SQL query so that the report displays the total time that the user spent viewing the site, allowed sessions, blocked sessions and amount of sessions. Total traffic, total time, allowed sessions, blocked sessions and amount of sessions should be displayed in one report. And it would be great to see in the report amount of requests to the site.

Is it possible to limit the sampling of data by the hour in a fortianalyzer? For example, I need data for the last 7 days, but in the period from 8 to 19:00. My query: select domain, string_agg(distinct catdesc, ', ') as agg_catdesc, user_src, sum(bandwidth) as bandwidth, sum(traffic_in) as traffic_in, sum(traffic_out) as traffic_out from ###(select coalesce(nullifna(hostname), ipstr(`dstip`)) as domain, catdesc, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(rcvdbyte, 0)) as traffic_in, sum(coalesce(sentbyte, 0)) as traffic_out from $log-traffic where $filter and (logflag&1>0) and utmaction!='blocked' and (countweb>0 or ((logver is null or logver<52) and (hostname is not null or utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter')))) group by domain, catdesc, user_src having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 /*SkipSTART*/order by bandwidth desc/*SkipEND*/)### t group by domain, catdesc, user_src order by bandwidth desc

2 REPLIES 2
chutter_FTNT
Staff
Staff

Hello,

You could use "WHERE ($hour_of_day>'07:00' AND $hour_of_day<'20:00')" in your dataset

This should give you the timeframe from 8:00 to 19:00.

 

Modified your dataset:

 

select from_itime(itime), domain, string_agg(distinct catdesc, ', ') as agg_catdesc, user_src, sum(bandwidth) as bandwidth, sum(traffic_in) as traffic_in, sum(traffic_out) as traffic_out from ###(select itime, coalesce(nullifna(hostname), ipstr(`dstip`)) as domain, catdesc, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(rcvdbyte, 0)) as traffic_in, sum(coalesce(sentbyte, 0)) as traffic_out from $log-traffic where $filter and (logflag&1>0) and utmaction!='blocked' and (countweb>0 or ((logver is null or logver<52) and (hostname is not null or utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter')))) group by itime, domain, catdesc, user_src having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 /*SkipSTART*/order by bandwidth desc/*SkipEND*/)### t WHERE ($hour_of_day>'07:00' AND $hour_of_day<'20:00') group by itime, domain, catdesc, user_src order by bandwidth desc

 

Christian

Alexandr_e

Hello! Much appreciated! But it's not exactly what we need. In this kind of report a lot of duplicates. We need a report Top 50-100 users. Total outgoing traffic, total incoming traffic, total traffic, total time, allowed sessions, blocked sessions, amount of sessions and amount of requests should be displayed in same report. And this data should be from 8 till 19:00 time period.

Labels
Top Kudoed Authors