Dataset to filter out IP address sources ?

[fran1942]

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.

[AtiT · Accepted solution]

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