Dataset Help for Top Web Sites By User!!

[ihsan]

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

[AtiT · Accepted solution]

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]

Hi Ati

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