Splunk Search
Highlighted

Comparing different fields in different events to each other

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
Highlighted

Re: Comparing different fields in different events to each other

Motivator

You should be able to do this with some eval statements. First, eval fieldA from the event where alert="ONE" and fieldB 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

Highlighted

Re: Comparing different fields in different events to each other

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 Alert1FieldA, 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
Highlighted

Re: Comparing different fields in different events to each other

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
Highlighted

Re: Comparing different fields in different events to each other

Builder

There is still an issue. All transactions eval out to "DoNotKeep" 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(Alert1FieldA),Alert2FieldC,Alert1FieldA))
| 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
Highlighted

Re: Comparing different fields in different events to each other

Builder

So I've realized the problem is because the Alert1FieldA and Alert2FieldC 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
Highlighted

Re: Comparing different fields in different events to each other

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" Alert1FieldA" and "Alert2FieldC" from becoming multi value fields, allowing the where comparison to work as you had originally proposed:

| eval Alert1FieldA=if(alert="ONE", fieldA, null())
| eval Alert2
FieldC=if(alert="TWO", fieldC, null())

Thanks again!

0 Karma
Highlighted

Re: Comparing different fields in different events to each other

Motivator

Excellent, glad you found that! Happy Splunking!

0 Karma
Highlighted

Re: Comparing different fields in different events to each other

Motivator

Ah, excellent, glad you found that!

0 Karma
Highlighted

Re: Comparing different fields in different events to each other

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

Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.