topic Re: Comparing different fields in different events to each other in Splunk Search

Comparing different fields in different events to each other

thisissplunk — Fri, 12 Sep 2014 18:34:14 GMT

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.

Re: Comparing different fields in different events to each other

wpreston — Mon, 28 Sep 2020 17:34:29 GMT

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"

Re: Comparing different fields in different events to each other

jeffflynn — Fri, 12 Sep 2014 19:17:32 GMT

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

Re: Comparing different fields in different events to each other

thisissplunk — Mon, 28 Sep 2020 17:34:31 GMT

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....

Re: Comparing different fields in different events to each other

wpreston — Sat, 13 Sep 2014 00:54:32 GMT

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.

Re: Comparing different fields in different events to each other

thisissplunk — Mon, 28 Sep 2020 17:35:53 GMT

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.

Re: Comparing different fields in different events to each other

thisissplunk — Mon, 28 Sep 2020 17:35:56 GMT

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?

Re: Comparing different fields in different events to each other

thisissplunk — Mon, 28 Sep 2020 17:35:59 GMT

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!

Re: Comparing different fields in different events to each other

wpreston — Tue, 16 Sep 2014 18:50:38 GMT

Ah, excellent, glad you found that!

Re: Comparing different fields in different events to each other

wpreston — Tue, 16 Sep 2014 18:58:43 GMT

Excellent, glad you found that! Happy Splunking!