Further filtering out events in a stats group by based on fields

[hudson2000]

Hi,

I have a relatively simple search, grouping events based on a extracted correlation id like this:

| eval id=coalesce(cid, cid2) |

| stats values(*) by id

However what I need to do now is further filter down events included in each final row - specifically, I have extacted path1 field on some events and path2 field on others, having the same id. If path1 includes path2, I don't want either event in the aggregated rows. 

Tried (prior to stats) something like:

|  eventstats values(path1) as AllPath1 by id | where NOT like(AllPath1, "%".path2."%") 

..but for some reason path2 disappears... Any pointers?

[renjith_nair · Accepted solution]

Try

"your current search"
|eventstats values(path1) as tmpPath1List by id
|eval tmpPresent=mvindex(tmpPath1List,mvfind(tmpPath1List,if(path2=="" OR isnull(path2),NULL,path2)))
|eventstats first(tmpPresent) as tmpPresentList by id
|where NOT (tmpPresent==tmpPresentList OR path1==tmpPresentList)|fields - tmp*
|stats values(*) as * by id

Run anywhere example

|makeresults |eval id="1 2 2 3 1 2 4"|makemv id|mvexpand id
|appendcols [|makeresults|eval path1="/abc,/dupabc,/def/aaa/pqr,/ghi,NULL,NULL,NULL"|makemv path1 delim="," |mvexpand path1 ]
|appendcols [|makeresults|eval path2="NULL,NULL,NULL,bla,/xyz,/pqr,/mno"|makemv path2 delim="," |mvexpand path2 ]
|replace NULL with "" in path1,path2
|rename COMMENT as "DUMMY DATA GENERATION ENDS HERE"
|eventstats values(path1) as tmpPath1List by id
|eval tmpPresent=mvindex(tmpPath1List,mvfind(tmpPath1List,if(path2=="" OR isnull(path2),NULL,path2)))
|eventstats first(tmpPresent) as tmpPresentList by id
|where NOT (tmpPresent==tmpPresentList OR path1==tmpPresentList)|fields - tmp*
|stats values(*) as * by id

You may execute until line4 before the "COMMENT" to see  the dummy data used. Add rest of the search line by line in each execution and see if it matches your requirement.

[hudson2000]

Not only this worked out of the box (removing both events by using tmpPresent and tmpPresentList is a really neat idea), you clued me in on makeresults which is going to make figuring out queries far, far faster. Thank you!