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
Solved! Go to Solution.
Nominating a forum post submits a request to create a new Knowledge Article based on the forum post topic. Please ensure your nomination includes a solution within the reply.
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
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
Hi Ati
thanks for quick reply. This dataset works. thank you very much.
Select Forum Responses to become Knowledge Articles!
Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.
User | Count |
---|---|
1677 | |
1085 | |
752 | |
446 | |
226 |
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 2024 Fortinet, Inc. All Rights Reserved.