Hot!How to filter out hostnames in report ?

Author
fran1942
Bronze Member
  • Total Posts : 23
  • Scores: 0
  • Reward points: 0
  • Joined: 2016/12/21 21:56:19
  • Status: offline
2017/08/11 14:23:18 (permalink)
0

How to filter out hostnames in report ?

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
post edited by fran1942 - 2017/08/12 12:45:37

Attached Image(s)

#1

1 Reply Related Threads

    fran1942
    Bronze Member
    • Total Posts : 23
    • Scores: 0
    • Reward points: 0
    • Joined: 2016/12/21 21:56:19
    • Status: offline
    Re: How to filter out hostnames in report ? 2017/08/12 14:06:43 (permalink)
    0
    Hello I have self-resolved this issue.
    I did it by using 'where hostname not like' filters in the dataset directly.
     
    #2
    Jump to:
    © 2017 APG vNext Commercial Version 5.5