Hi
i can use on below dataset. My problem same user and same destination see a few times. How can edit this dataset for this problem?
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
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 ihsan,
You can see the same user and destination because the time is different. It seems that the dataset is build to show the highest bandwidth usage for the user and time when it happend.
You can remove the time section to get only one user and one destination with the summary bandwidth:
SELECT 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 user_src, srcip, website, web_cat HAVING SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0))>0 ORDER BY bandwidth DESC
AtiT
Hello ihsan,
You can see the same user and destination because the time is different. It seems that the dataset is build to show the highest bandwidth usage for the user and time when it happend.
You can remove the time section to get only one user and one destination with the summary bandwidth:
SELECT 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 user_src, srcip, website, web_cat HAVING SUM(COALESCE(`sentbyte`, 0)+COALESCE(`rcvdbyte`, 0))>0 ORDER BY bandwidth DESC
AtiT
thanks for support. dataset is working.
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 |
---|---|
1688 | |
1087 | |
752 | |
446 | |
228 |
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.