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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...