Skip to main content
fran1942
fran1942
New Member
November 23, 2017
Solved

Dataset to filter out IP address sources ?

  • November 23, 2017
  • 1 reply
  • 4773 views

Hello, I have the following dataset which shows top bandwidth users. I am converting source IP addresses to FSSO usernames for ease of interpretation. I want to strip out any lines with source address users that do not correlate to FSSO usernames i.e. those that show up as source IP addresses instead of usernames.

How would I do that ?

Thank you kindly for any help.

 

select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(rcvdbyte, 0)) as traffic_in, sum(coalesce(sentbyte, 0)) as traffic_out, count(*) as sessions from $log where $filter and logid_to_int(logid) not in (4, 7, 14) group by user_src having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc

 

screenshot attached.

 

 

    Best answer by AtiT

    Hello,

    You need some changed.

     

    1) coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src

    - change it to: `user` as user_src

    2) add a filter to show only users where the username exists - is not NULL:

    nullifna(`user`) is not null

     

    Should look like this:

    select `user` as user_src, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(rcvdbyte, 0)) as traffic_in, sum(coalesce(sentbyte, 0)) as traffic_out, count(*) as sessions from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(`user`) is not null group by user_src having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc

    1 reply

    AtiT
    AtiTAnswer
    New Member
    November 23, 2017

    Hello,

    You need some changed.

     

    1) coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src

    - change it to: `user` as user_src

    2) add a filter to show only users where the username exists - is not NULL:

    nullifna(`user`) is not null

     

    Should look like this:

    select `user` as user_src, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(rcvdbyte, 0)) as traffic_in, sum(coalesce(sentbyte, 0)) as traffic_out, count(*) as sessions from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(`user`) is not null group by user_src having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc

    Terms & ConditionsAccessibility statement