Splunk Search

How to compare search results to a list?

Log_wrangler
Builder

I have waf logs for an application that provides userID and srcIP which I pipe to geoinfo to get the city state country info.
I have an "active_list" that provides userID, account_city, account_state, account_country.

index=someApp sourcetype=WAF | [inputlookup active_list.csv | fields userID] | table srcIP userID| geoip srcIP

Now I want to find a way that I can compare the geoip info, which outputs a "srcIP_city" to the active_list "account_city" and see all matches and non-matches.

I have been testing some methods but no luck. I am thinking I need results to go to an outputlookup and then compare the two lists.

I have tried this but I am not sure if this is correct

index=someApp sourcetype=WAF  (some key words) | table userID srcIP | geoip srcIP | table usrID srcIP srcIP_city srcIP_state srcIP_country | lookup active_list.csv userID

Here I am running my basic search first and listing field by userID, which makes it easy to see when the srcIP_city and account_city doesn't match.
But I am not sure if this is correct. Please advise, thank you.

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

index=someApp sourcetype=WAF  [ | inputlookup active_list.csv | fields userID] | table srcIP userID
| geoip srcIP 
 lookup active_list userID OUTPUT account_city
| eval result=if(account_city!=srcIP_city,"City Not Same","Same")

View solution in original post

0 Karma

somesoni2
Revered Legend

Give this a try

index=someApp sourcetype=WAF  [ | inputlookup active_list.csv | fields userID] | table srcIP userID
| geoip srcIP 
 lookup active_list userID OUTPUT account_city
| eval result=if(account_city!=srcIP_city,"City Not Same","Same")

View solution in original post

0 Karma

Log_wrangler
Builder

I really needed the OUTPUT example, and It works ( I added a pipe before the lookup) but the results are not consistent. I think there is a formatting issue or something because the result says "City Not Same" when they are...

0 Karma

Log_wrangler
Builder

after eval src_ip and userID to upper, it works... fyi

0 Karma