Skip to main content
ihsan
ihsan
New Member
March 16, 2015
Solved

Dataset Help for Top Web Sites By User!!

  • March 16, 2015
  • 1 reply
  • 6222 views

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

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

1 reply

AtiT
AtiTAnswer
New Member
March 16, 2015

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

ihsan
ihsanAuthor
New Member
March 16, 2015

Hi Ati

 

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

Terms & ConditionsAccessibility statement