Splunk Search

How to compare search results to a list?

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

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

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

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

Builder

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!