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

[melonman]

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

[melonman]

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.

[Ayn · Accepted solution]

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]

Thanks Ayn, great hint you gave me!
Appreciated!