How to filter out hostnames in report ?
- August 11, 2017
- 1 reply
- 4197 views
Hello I have the following dataset which returns a drill down of top users websites based on bandwidth (please see attached screenshot). I would like to filter out all results that include any hostname with 'google' or 'microsoft' in it i.e. %google% or %microsoft%.
This is because Google and Microsoft site visits are business justified. I only want to see what sites users are browsing of their own free will that is causing high bandwidth usage.
n.b. I have already tried to enter multiple 'not equal to' filters in the report 'Settings-Filters' GUI, however that filters out whole users, rather than individual hostnames from users stats.
select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, hostname, sum(coalesce(`sentbyte`, 0)+coalesce(`rcvdbyte`, 0)) as bandwidth from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(hostname) is not null group by user_src, hostname order by bandwidth desc
