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
SplunkTrust
SplunkTrust

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.

Happy Splunking!

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

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.

Happy Splunking!

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...