How to use stats as a filtered self join?

[pm771]

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]

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  

[nrohbock]

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.

[pm771]

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.

[nrohbock]

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