Skip to main content
ihsan
ihsan
New Member
August 5, 2015
Solved

Help For User Dataset !!

  • August 5, 2015
  • 2 replies
  • 3415 views

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

    Best answer by 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

     

    2 replies

    AtiT
    AtiTAnswer
    New Member
    August 6, 2015

    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

     

    ihsan
    ihsanAuthor
    New Member
    August 7, 2015

    thanks for support. dataset is working.

    Terms & ConditionsAccessibility statement