Splunk Search

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

hudson2000
Engager

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?

Labels (1)
0 Karma
1 Solution

renjith_nair
Legend

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.

---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

renjith_nair
Legend

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.

---
What goes around comes around. If it helps, hit it with Karma 🙂

hudson2000
Engager

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!

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...