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.
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"
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
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"
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.
Ah, excellent, glad you found that!
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!
Excellent, glad you found that! Happy Splunking!
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?
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.
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....