Splunk Search

How to use stats as a filtered self join?

pm771
Communicator

I have groups of events that have the same GroupID field.

For events matching given criteria I need to find another event with predefined EventName from the same group and then table together certain fields from both events .

Using join it will be something like this:

index=common_index sourcetype=common_sourcetype EventName=First somethingElse=whatever
| join GroupID [search index=common_index sourcetype=common_sourcetype EventName=Second | table GoupId keyField]
| table EventName keyField 

What would be the same type of query with stats?

I do not know how to do something like earliest(EventName==value)

somesoni2
Revered Legend

Give this a try

 index=common_index sourcetype=common_sourcetype (EventName=First somethingElse=whatever) OR (EventName=Second)
| eval EventNameToUse=if(EventName="First",EventName,null())
| stats dc(EventName) as reportedOnvalues(EventNameToUse) as EventName values(keyField) as keyField by GroupId
| where reportedOn=2
 | table EventName keyField  
0 Karma

nrohbock
Explorer

Since all of your sources are already indexed, I think it should be as simple as:

index=common_index sourcetype=common_sourcetype ((EventName=First somethingElse=whatever) or EventName=Second)
| stats values(keyField) as keyField by GroupID, EventName
| fields - GroupID
| mvexpand keyField

You may also want to dedup the table, but technically, I think this should give you the same result.

0 Karma

pm771
Communicator

Thanks for the reply.

My task is to select certain events and then match them (and only them) to another event from the same group.

Then I need EvenName from the first search and keyField from the second.

Your code is pulling 2nd event regardless if the 1st satisfied the criteria.

0 Karma

nrohbock
Explorer

Sorry for the false lead. I hope this is more helpfull.

index=common_index sourcetype=common_sourcetype
| streamstats count as order by GroupID
| eval myEventName=if(order=1 and somethingElse=whatever, EventName, null()), myKeyField=if(order=2, keyField, null())
| stats values(myEventName) as EventName, values(myKeyField) as keyField by GroupID
| search EventName=* keyField=*

0 Karma
Get Updates on the Splunk Community!

OpenTelemetry for Legacy Apps? Yes, You Can!

This article is a follow-up to my previous article posted on the OpenTelemetry Blog, "Your Critical Legacy App ...

UCC Framework: Discover Developer Toolkit for Building Technology Add-ons

The Next-Gen Toolkit for Splunk Technology Add-on Development The Universal Configuration Console (UCC) ...

.conf25 Community Recap

Hello Splunkers, And just like that, .conf25 is in the books! What an incredible few days — full of learning, ...