Splunk Search

Can I change the operator in the result of format command?

melonman
Motivator

Hi,

Can I change the operator in the result of format command for subsearch? I actually want to pass the subsearch format result with "greater than (>) operator" in a particular field.

Example:

Calculate average score using last 1 hour events, then compare average score with each score in the last 1 minute to get the events with score more than average of the server.

sourcetype=score earliest=-1m@m latest=@m [search sourcetype=score earliest=-1h@h latest=@h | chart avg(score) as score by server | format]

Subsearch creates a result like this:

( ( score="36.019553" AND server="node0" ) OR ( score="34.130435" AND server="node1" ) OR ( score="36.378066" AND server="node2" ) OR ( score="36.503577" AND server="node3" ) OR ( score="35.652893" AND server="node4" ) )

However, what I really want is:

( ( score>"36.019553" AND server="node0" ) OR ( score>"34.130435" AND server="node1" ) OR ( score>"36.378066" AND server="node2" ) OR ( score>"36.503577" AND server="node3" ) OR ( score>"35.652893" AND server="node4" ) )

And pass this to parent search to retrieve events more than averaged score.

How can I do this?
Any comment appreciated.

Thanks

Tags (1)
0 Karma
1 Solution

Ayn
Legend

There are ways to do this, though they may not be very pretty 😉

@nick posted a great answer on how to achieve this. Have a look at it here: http://splunk-base.splunk.com/answers/33375/to-use-subsearch-result-in-outersearch-for-and-compariso...

View solution in original post

melonman
Motivator

That's nice, thank Ayn!!!

I just had to edit some search, also I compared 'search' and query'

sourcetype=score earliest=-1m [search sourcetype=score earliest=-1h@h latest=@h | chart avg(score) as score by server | eval query="server=" + server + " AND score>=" + score | fields query] | stats avg(score) as query by server

DEBUG: Subsearch evaluated to the following search expression: ( ( server=node0 AND score>=35.717718 ) OR ( server=node1 AND score>=35.464630 ) OR ( server=node2 AND score>=36.445820 ) OR ( server=node3 AND score>=35.307061 ) OR ( server=node4 AND score>=35.291473 ) )
DEBUG: [subsearch]: base lispy: [ AND sourcetype::score ]
DEBUG: base lispy: [ AND sourcetype::score [ OR node0 node1 node2 node3 node4 ] ]
DEBUG: search context: user="admin", app="search", bs-pathname="/opt/splunk/etc"
INFO: Your timerange was substituted based on your search string
INFO: [subsearch]: Your timerange was substituted based on your search string

sourcetype=score earliest=-1m [search sourcetype=score earliest=-1h@h latest=@h | chart avg(score) as score by server | eval search="server=" + server + " AND score>=" + score | fields search] | stats count by server

DEBUG: Subsearch evaluated to the following search expression: server=node0 AND score>=35.717718
DEBUG: [subsearch]: base lispy: [ AND sourcetype::score ]
DEBUG: base lispy: [ AND node0 sourcetype::score ]
DEBUG: search context: user="admin", app="search", bs-pathname="/opt/splunk/etc"
INFO: Your timerange was substituted based on your search string
INFO: [subsearch]: Your timerange was substituted based on your search string

So I will use query in my case.

Really appreciated.

0 Karma

Ayn
Legend

There are ways to do this, though they may not be very pretty 😉

@nick posted a great answer on how to achieve this. Have a look at it here: http://splunk-base.splunk.com/answers/33375/to-use-subsearch-result-in-outersearch-for-and-compariso...

melonman
Motivator

Thanks Ayn, great hint you gave me!
Appreciated!

0 Karma
Get Updates on the Splunk Community!

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

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, ...