topic Re: How to use stats as a filtered self join? in Splunk Search

How to use stats as a filtered self join?

pm771 — Tue, 28 Aug 2018 17:12:36 GMT

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)

Re: How to use stats as a filtered self join?

nrohbock — Tue, 28 Aug 2018 17:39:33 GMT

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.

Re: How to use stats as a filtered self join?

pm771 — Tue, 28 Aug 2018 17:57:08 GMT

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.

Re: How to use stats as a filtered self join?

nrohbock — Tue, 29 Sep 2020 21:04:48 GMT

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=*

Re: How to use stats as a filtered self join?

somesoni2 — Tue, 28 Aug 2018 21:53:30 GMT

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