Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
fran1942
New Contributor

Dataset to filter out IP address sources ?

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.

 

 

1 Solution
AtiT
Valued Contributor

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

AtiT

View solution in original post

AtiT
1 REPLY 1
AtiT
Valued Contributor

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

AtiT

AtiT
Labels
Top Kudoed Authors