Splunk Search

search query stats multiple counts filteration

yasit
Explorer

i have a query where i am looking for multiple values with OR and then counting the occurrence with the stats the query is something like this 

index=****  ("value1") OR ("Value3") OR ...  |  stats count(eval(searchmatch("vlaue1"))) as value1,  count(eval(searchmatch("vlaue2"))) as value2 


now I just want to collect only those values which are found which mean there count is greater than 0. How can I achieve this where only stats of the values are displayed which are found in the events

 
also search values are mostly ips, URLs , domains, etc
Note: I'm making this query for dashboard

Labels (7)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

Splunk (and most data query languages) treat columns as solemn.  But for display purposes, you can fool the system by converting columns to rows and take out those you don't want.  Of course we are talking about transpose.

 

| transpose 0
| search "row 1" > 0
| transpose 0 header_field=column
| fields - column

 

To demonstrate, run this search

 

index=_internal sourcetype!=splunkd_ui_access json OR python OR foobar
| stats count(eval(searchmatch("json"))) as json count(eval(searchmatch("python"))) as python count(eval(searchmatch("foobar"))) as foobar
``` data emulation above ```

 

It gives 0 for foobar.  

jsonpythonfoobar
40511350

But this search

 

index=_internal sourcetype!=splunkd_ui_access earliest=-5h json OR python OR foobar
| stats count(eval(searchmatch("json"))) as json count(eval(searchmatch("python"))) as python count(eval(searchmatch("foobar"))) as foobar
``` data simulation above ```
| transpose 0
| search "row 1" > 0
| transpose 0 header_field=column
| fields - column

 

eliminates foobar from table

jsonpython
4421232

(The numbers changed because this is a live splunkd.)

View solution in original post

Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

Splunk (and most data query languages) treat columns as solemn.  But for display purposes, you can fool the system by converting columns to rows and take out those you don't want.  Of course we are talking about transpose.

 

| transpose 0
| search "row 1" > 0
| transpose 0 header_field=column
| fields - column

 

To demonstrate, run this search

 

index=_internal sourcetype!=splunkd_ui_access json OR python OR foobar
| stats count(eval(searchmatch("json"))) as json count(eval(searchmatch("python"))) as python count(eval(searchmatch("foobar"))) as foobar
``` data emulation above ```

 

It gives 0 for foobar.  

jsonpythonfoobar
40511350

But this search

 

index=_internal sourcetype!=splunkd_ui_access earliest=-5h json OR python OR foobar
| stats count(eval(searchmatch("json"))) as json count(eval(searchmatch("python"))) as python count(eval(searchmatch("foobar"))) as foobar
``` data simulation above ```
| transpose 0
| search "row 1" > 0
| transpose 0 header_field=column
| fields - column

 

eliminates foobar from table

jsonpython
4421232

(The numbers changed because this is a live splunkd.)

Tags (1)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

Your question is a bit vague so I'm not sure what you want so please be a little more descriptive. But from what you wrote I assume that you do some comditional aggregation and want to "go back" to raw events fulfilling your conditions. You can't do that this way.

Splunk "loses" all information not being explicitly passed from the command. So when you're doing the stats command only results of the stats command are available for further processing - the original events are no longer known in your pipeline.

So you have to approach it differently. Probably adding some artificial "classifier" field or two but can't really say without knowing what exactly you want to achieve.

0 Karma
Get Updates on the Splunk Community!

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

Splunk Decoded: Business Transactions vs Business IQ

It’s the morning of Black Friday, and your e-commerce site is handling 10x normal traffic. Orders are flowing, ...

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...