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

Dataset Help for Top Web Sites By User!!

Hi

 

I want to add user column below dataset. I didn't add user column. Can you help me for this dataset.

 

select domain, sum(bandwidth) as bandwidth from (###(select coalesce(nullifna(hostname), ipstr(`srcip`)) as domain, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter') group by domain having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc)### union all ###(select coalesce(nullifna(hostname), ipstr(`srcip`)) as domain, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log-webfilter where $filter and (eventtype is null or logver = 52) group by domain having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc)###) t group by domain order by bandwidth desc

1 Solution
AtiT
Valued Contributor

Hello,

I don't know if it is that you want but in my case when I want to see the top websites it means that these logs are also available in the webrilter log, so it is easier for me to search them for the report under webfilter.

 

What I am using for websites is the Webfilter Log type dataset:

 

SELECT TO_CHAR(TO_TIMESTAMP(`itime`)::timestamp, 'YYYY-MM-DD HH24:MI') AS time, COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, COALESCE(NULLIFNA(ROOT_DOMAIN(`hostname`)), IPSTR(`dstip`)) AS website, COALESCE(NULLIFNA(`catdesc`), 'No Category') AS web_cat, SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0)) AS bandwidth FROM $log WHERE $filter AND `action`='passthrough' GROUP BY time, user_src, srcip, website, web_cat HAVING SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0))>0 ORDER BY bandwidth DESC

AtiT

View solution in original post

AtiT
2 REPLIES 2
AtiT
Valued Contributor

Hello,

I don't know if it is that you want but in my case when I want to see the top websites it means that these logs are also available in the webrilter log, so it is easier for me to search them for the report under webfilter.

 

What I am using for websites is the Webfilter Log type dataset:

 

SELECT TO_CHAR(TO_TIMESTAMP(`itime`)::timestamp, 'YYYY-MM-DD HH24:MI') AS time, COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, COALESCE(NULLIFNA(ROOT_DOMAIN(`hostname`)), IPSTR(`dstip`)) AS website, COALESCE(NULLIFNA(`catdesc`), 'No Category') AS web_cat, SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0)) AS bandwidth FROM $log WHERE $filter AND `action`='passthrough' GROUP BY time, user_src, srcip, website, web_cat HAVING SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0))>0 ORDER BY bandwidth DESC

AtiT

AtiT
ihsan
New Contributor II

Hi Ati

 

thanks for quick reply. This dataset works. thank you very much.

Announcements

Select Forum Responses to become Knowledge Articles!

Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.

Labels
Top Kudoed Authors