Splunk Search

How to search to match 2 fields from my raw events with  2 fields from a CSV file and output to a different field?

neerajs_81
Contributor

Hi, how do i craft a search to match 2 fields from my raw events with  2 fields from a CSV file and output if one of the fields is different  ?

 Requirement is to match the country_name and email from raw events versus to what is there in the csv file.  Basically If the country_name in the raw events in DIFFERENT as in if it does not match the "Country" field in the lookup (based on user's email) then display those results only.  

Lookup file structure:

Email_id Country
   
   


The Raw events have fields called email and country_name.
Below is what i am trying but its not working.

 

 

 

index=xxx 
| search [inputlookup file.csv ] 
where (country_name != Country)  AND (Email!=Email_id) 
table displayName, Email_id country_name

 

 

 

 

Labels (1)
0 Karma
1 Solution

PickleRick
Ultra Champion

OK. Let me rephrase it.

If you have an event with the fields email and country_name and a lookup with fields Email and Country. And now you want to find all combinations where (using SQL-like notation) event.Email=lookup.Email and event.country_name!=lookup.Country? Are you aware that - depending on your lookup contents - this could multiply your initial results if you had several  entries for the same email with different countries?

And @ITWhisperer 's response is not gonna provide that (my previous one neither, for that matter).

The lookup command will only return one match from your lookup table so if by chance it happened to fulfill your "where" condition, it would show in results, if not - it wouldn't.

EDIT: On second thought, my previous solution should work. You just have to adjust the field names to match your fields in events and lookup so the effective generated query would be built from the fields in the lookup but would reference the fields in the event. But still, if you have a big lookup table, the resulting subsearch would result in a big ugly set of conditions...

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Try this:

index=xxx
| lookup file.csv Email_id as email
| where Country!=country_name

neerajs_81
Contributor

Thank you for responding.  For some reason, the where condition isn't working as expected. 
For example,  in my raw events , under "Interesting fields" there is a field called country_name having the value "United States" (without quotes)   and from  the lookup we have Country  also having the value "United States" (without quotes)

My original requirement is for  

| where Country!=country_name

 
But out of curiosity  even if i do a 

 

| where Country=country_name

 

It shows 0 results.  Why is the where clause failing here despite both fields having the same value - United States  .  Ideally it should have shown me the matches because both fields have the same string value. 

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

If you are not getting matches that you were expecting, it is possible the lookup failed, perhaps there are trailing/leading blanks, or some other reason why there is an unexpected mismatch?

0 Karma

neerajs_81
Contributor

I have verified there aren't any blank spaces or trailing characters in the lookup file data.
Is it failing because there is a space in between United and States  ?  OR does where clause know how to deal with spaces ?   

United States



Tags (1)
0 Karma

PickleRick
Ultra Champion

It's not how you use lookup.

The condition of a field Country in the lookup being equal to the field country_name in the event is fulfilled by the lookup command itself. That's what you use the lookup for.

If you use the condition

| where Country!=country_name

it means that you're searching for results which at this point of processing pipeline have two fields - Country and country_name and those fields values are not equal.

The question is what you really want, because that's a bit vague from the beginning. Try to be a bit more specific - show some (anonymized if needed) samples of events, of lookup rows and what you want as the result.

neerajs_81
Contributor

My original requirement is show me those results ( events ) where the country_name ( coming from Raw events) is different from the field: Country as defined in lookup table for that user .   Basically i am searching for if a user has logged in from a different country other than his base location i.e.  Non matches.
I am using the Email field to correlate between my raw data and  the lookup file.
I saw your and ITWhisperer's response.  His search as shown below, this seems good enough to my use case.   

index=xxx
| lookup file.csv Email_id as email
| where Country!=country_name


Based on your 2nd response, is there any thing wrong with the above where condition ?

Secondly just out of curiosity i tried to check for matches using

| where Country=country_name

It shows 0 results.  Per your response this will fail because of the way processing pipeline works.   But  Does that also mean that  

| where Country!=country_name

 will also not work ?

How to compare for non matches and matches for in that case ?

0 Karma

PickleRick
Ultra Champion

OK. Let me rephrase it.

If you have an event with the fields email and country_name and a lookup with fields Email and Country. And now you want to find all combinations where (using SQL-like notation) event.Email=lookup.Email and event.country_name!=lookup.Country? Are you aware that - depending on your lookup contents - this could multiply your initial results if you had several  entries for the same email with different countries?

And @ITWhisperer 's response is not gonna provide that (my previous one neither, for that matter).

The lookup command will only return one match from your lookup table so if by chance it happened to fulfill your "where" condition, it would show in results, if not - it wouldn't.

EDIT: On second thought, my previous solution should work. You just have to adjust the field names to match your fields in events and lookup so the effective generated query would be built from the fields in the lookup but would reference the fields in the event. But still, if you have a big lookup table, the resulting subsearch would result in a big ugly set of conditions...

neerajs_81
Contributor

Thank you for the detailed explanation

0 Karma

PickleRick
Ultra Champion

OK, so you don't want to use the lookup table as a lookup (evaluate field(s) based on values of other field(s)), but as a base for filtering events?

Since you want a negative match on one field, fooling around with format command won't help much.

You need to manually compose set of conditions and return them from subsearch.

So you need to go from here:

EmailCountry
a@a.comA
b@b.comB
......

 

To here:

((Email=a@a.com AND Country!=A) OR (Email=b@b.com AND Country!=B) OR ... )

So you need to list the contents of your lookup, transform it so that each line is formatted into a proper condition (or rather pair of conditions), join them and return that string as an output from the subsearch.

index=xxx [ | inputlookup file.csv
   | eval combined="( Email=\"".Email."\" AND Country!=\"".Country."\" )"
| stats values(combined) as combined
| eval joined="(".mvjoin(combined," OR ").")"
| return $joined ]
| table displayName  Email_id country_name

As usual with such hand-stitched searches - it will fail if for some reason the field values contain quotation marks.

EDIT: OK, I re-read your original question and I'm a bit puzzled beause you mentioned only a negative match on country but in your search attempt you tried to do negative match on both fields. If that's what you're trying to do - rule out all entries which have the combination of both fields in your lookup, there is a much easier way to do so. Simply run

index=xxx NOT [ | inputlookup file.csv | table Email Country ]
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...