Skip to main content
fran1942
New Member
August 11, 2017
Question

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

    1 reply

    fran1942
    fran1942Author
    New Member
    August 12, 2017

    Hello I have self-resolved this issue.

    I did it by using 'where hostname not like' filters in the dataset directly.

     

    Thought Leadership. Security Summit. Thursday, November 12th, PGA National Resort, Palm Beach Gardens, FL.
    Thought Leadership. Security Summit. Thursday, October 8th. Disney's Grand Californian Hotel & SPA, Anaheim, CA.