Splunk Search

Comparing different fields in different events to each other

thisissplunk
Builder

This is the question I need to answer with Splunk:

"How can I determine when different unique events with alert="ONE" or alert="TWO" fire within 1 second of each other, where their hostname field is the same? AND where event alert="ONE"s field "A", matches event alert="TWO"s field "C"?

My normal solution for the first sentence is easy:

index=logs sourcetype=host_log alert="ONE" OR alert="TWO" | transaction fields="hostname" maxspan=1s |  | eval UniqueCount=mvcount(alert) | where UniqueCount > 1

But I cannot for the life of me figure out how to compare them after this point. I need to do something like you'd see in code here:

if ONE[field_A] = TWO[field_C] then show transaction event

I'm also not sure if the transaction command is the best way to go about this. I tried a subsearch but that loses the keying off of hostname within 1 second of each other, which is crucial here.

0 Karma
1 Solution

wpreston
Motivator

You should be able to do this with some eval statements. First, eval field_A from the event where alert="ONE" and field_B from the event where alert="TWO" into something you can recognize after your transaction command:

index=logs sourcetype=host_log alert="ONE" OR alert="TWO"
| eval Alert1_FieldA=if(alert="ONE", fieldA, null())
| eval Alert2_FieldC=if(alert="TWO", fieldC, null())

Then perform your transaction.

| transaction fields="hostname" maxspan=1s 

After the transaction, eval a test to see if the event is something your looking for or not, then search for the ones you want:

| eval Keep_Or_Not=if(Alert1_FieldA=Alert2FieldC, "Keep", "Do_Not_Keep")
| search Keep_Or_Not="Keep"

So the complete search would look like this:

index=logs sourcetype=host_log alert="ONE" OR alert="TWO"
| eval Alert1_FieldA=if(alert="ONE", fieldA, null())
| eval Alert2_FieldC=if(alert="TWO", fieldC, null())
| transaction fields="hostname" maxspan=1s 
| eval Keep_Or_Not=if(Alert1_FieldA=Alert2_FieldC, "Keep", "Do_Not_Keep")
| search Keep_Or_Not="Keep"

View solution in original post

jeffflynn
Explorer

you could try to create the transactions first then use a 3rd field to compare the 2 events and use a where statement to only show when A and B match.

| transaction startswith=("whatever starts") endswith=("whatever ends") | eval THIRDFIELD=case(fieldA=fieldB,1,fieldA!=fieldB,0) | where THIRDFIELD=1 | table fields

wpreston
Motivator

You should be able to do this with some eval statements. First, eval field_A from the event where alert="ONE" and field_B from the event where alert="TWO" into something you can recognize after your transaction command:

index=logs sourcetype=host_log alert="ONE" OR alert="TWO"
| eval Alert1_FieldA=if(alert="ONE", fieldA, null())
| eval Alert2_FieldC=if(alert="TWO", fieldC, null())

Then perform your transaction.

| transaction fields="hostname" maxspan=1s 

After the transaction, eval a test to see if the event is something your looking for or not, then search for the ones you want:

| eval Keep_Or_Not=if(Alert1_FieldA=Alert2FieldC, "Keep", "Do_Not_Keep")
| search Keep_Or_Not="Keep"

So the complete search would look like this:

index=logs sourcetype=host_log alert="ONE" OR alert="TWO"
| eval Alert1_FieldA=if(alert="ONE", fieldA, null())
| eval Alert2_FieldC=if(alert="TWO", fieldC, null())
| transaction fields="hostname" maxspan=1s 
| eval Keep_Or_Not=if(Alert1_FieldA=Alert2_FieldC, "Keep", "Do_Not_Keep")
| search Keep_Or_Not="Keep"

wpreston
Motivator

It looks like I have a typo in the answer, (I forgot to put the underscore in Alert2_FieldC in the last eval statement) sorry about that! I'll fix it in the above search.

0 Karma

wpreston
Motivator

Ah, excellent, glad you found that!

0 Karma

thisissplunk
Builder

And finally.... success. I did not use "null" in as you did in your example in the "ifs". I thought by tagging those nulls it was fixing a problem in my data which was borking the entire search. Turns out, a few fillnulls earlier in the search had solved the issue.

Therefor, returning the nulls back into the following prevented the" Alert1_FieldA" and "Alert2_FieldC" from becoming multi value fields, allowing the where comparison to work as you had originally proposed:

| eval Alert1_FieldA=if(alert="ONE", fieldA, null())
| eval Alert2_FieldC=if(alert="TWO", fieldC, null())

Thanks again!

0 Karma

wpreston
Motivator

Excellent, glad you found that! Happy Splunking!

0 Karma

thisissplunk
Builder

So I've realized the problem is because the Alert1_FieldA and Alert2_FieldC are multi value fields at the point of comparison. Not sure how to compare them besides mvjoin'ing them and doing some extraneous matching. Is there a better way?

0 Karma

thisissplunk
Builder

There is still an issue. All transactions eval out to "Do_Not_Keep" event though one should be "Keep". What I posted above your last comment works, and this works as well for some reason I do not understand:

| eval tmp=lower(if(isnull(Alert1_FieldA),Alert2_FieldC,Alert1_FieldA))
| transaction maxspan=1m hostname tmp
| where Alert1_FieldA==Alert2_FieldC

This also works:

| stats count by NewStartupSourceProcess, JavaEnvelopeHipsFiles
| where NewStartupSourceProcess==JavaEnvelopeHipsFiles

I'd much rather have access to the full event though.

0 Karma

thisissplunk
Builder

This was great thanks! That said, the last eval and search statement does not work! But if I replace them with this it does:

| top limit=0 Alert1_FieldA, Alert2FieldC
| where Alert1_FieldA==Alert2FieldC

Any ideas? The fact that I can get this far is enough, but I'm not sure why your last two statements along with the where command I tried does not work....

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...