Splunk Search

How do I combine my two searches, using the result from the first search to run the second search?

Communicator

My 1st search will be like this to get Peak Day and Peak Hour according to hits:

earliest="06/08/2015:00:00" latest="06/14/2015:23:59" index=iis | stats count as hit by date_hour, date_mday
| eventstats max(hit) as maxhit by date_mday
| where hit=maxhit| sort hit desc|top limit=1 hit,date_mday,date_hour|fields date_hour,date_mday,hit

Now after getting peak day and peak hour from the 1st result, using this peak day and peak hour, I want to run the search below:

(earliest="result from above" latest="result of peak hour+1hr") index=perfmon host=web1 (counter="% Processor Time" OR counter="Get Requests/Sec" OR counter="Current Connections") |stats avg(Value) by host, counter

Result should show Peak Hour, Peak Day, avg(Value), counter

I have spent almost 2 days on this.
Is it possible?

0 Karma

Esteemed Legend

Let's regroup and try to do this more efficiently and maybe more clearly, too; try this:

 index=perfmon host=web1 (counter="% Processor Time" OR counter="Get Requests/Sec" OR counter="Current Connections")  [search earliest="06/08/2015:00:00" latest="06/14/2015:23:59" index=iis | bucket _time span=1h | stats count BY _time | sort 1 - count | eval earliest=_time | eval latest=relative_time(earliest, "+1h") | fields earliest latest] | stats first(date_hour) AS PeakHour first(date_mday) AS PeakDay avg(Value) BY host, counter
0 Karma

Communicator

I am getting this error"Error in 'search' command: Unable to parse the search: Invalid search: AND AND." I entered |format "(" "(" "" ")" "AND" ")" in between [search....] but it does not displays any result.. So just to check i fired this query
index=perfmon host=ms10176 (counter="% Processor Time" OR counter="Get Requests/Sec" OR counter="Current Connections") [search earliest="08/16/2015:00:00:00" latest="08/23/2015:23:59:59" index=iis_api | bucket _time span=1h | stats count BY _time | sort 1 - count | eval earliest=_time | eval latest=relative_time(earliest, "+1h") | fields earliest latest count | format "" "" "" "" "" "" ] | stats first(date_hour) AS PeakHour first(date_mday) AS PeakDay avg(Value) BY host, counter| format "" "" "" "" "" ""
i got result in search as NOT()

Any idea?

0 Karma

Esteemed Legend

There was an error in the subsearch; try this:

index=perfmon host=web1 (counter="% Processor Time" OR counter="Get Requests/Sec" OR counter="Current Connections")  [search earliest="06/08/2015:00:00" latest="06/14/2015:23:59" index=iis | bucket _time span=1h | stats count BY _time | sort 1 - count | eval earliest=_time | eval latest=relative_time(earliest, "+1h") | fields earliest latest | format "(" "(" "" ")" "" ")"] | stats first(date_hour) AS PeakHour first(date_mday) AS PeakDay avg(Value) BY host, counter
0 Karma

SplunkTrust
SplunkTrust

Hi shreyasathavale,

Sure, the lazy and not very well performing way would be this:

earliest="06/08/2015:00:00" latest="06/14/2015:23:59" index=iis 
| stats count as hit by date_hour, date_mday
| eventstats max(hit) as maxhit by date_mday
| where hit=maxhit
| sort hit desc
|top limit=1 hit,date_mday,date_hour
|fields date_hour,date_mday,hit, earliest, latest
| map search=" search (earliest=$earliest$ latest=$latest$+3600) index=perfmon host=web1 (counter="% Processor Time" OR counter="Get Requests/Sec" OR counter="Current Connections") 
|stats avg(Value) by host, counter

This is completely untested and keep in mind, for me it's early Monday morning 🙂

I'm pretty sure this can be done with some stats tricks

Hope that helps ...

cheers, MuS

0 Karma

SplunkTrust
SplunkTrust

You see, took me 9 minutes to write this answer 🙂

Communicator

Thanks for the comment MuS.. I can understand Monday morning blues 🙂
I gave it a try what you suggested and got an error which i deduced by running query 1 by 1 and found that earliest and latest does not get filled up in 1st query.

0 Karma

SplunkTrust
SplunkTrust

As said; un-tested and usually I don't use any kind of sub searches.

0 Karma

Esteemed Legend

You need the map command like this:

earliest="06/08/2015:00:00" latest="06/14/2015:23:59" index=iis | bucket _time span=1h | stats count BY _time | sort - 1 count | eval time=_time | map search="search index=perfmon earliest>=$time$ latest<=($time$ + 60*60) host=web1 (counter=\"% Processor Time\" OR counter=\"Get Requests/Sec\" OR counter=\"Current Connections\") | stats avg(Value) by host, counter"
0 Karma

Communicator

Thanks woodcock for the comment ..but when i run this query I am not getting any result 😞 .. i am trying to make few changes running query 1 by 1 and finding out
Usually when I am using map it is not giving any output. Do you have any idea?

0 Karma

Communicator

I made slight change in stats count BY _time | sort - 1 count | eval time=_time | map search="search index=perfmon earliest>=$time$ latest<=($time$ + 60*60) by stats count as hit BY _time | sort hit desc| top limit=1 hit,_time| map search="search index=perfmon host=web1 earliest=$_time$ latest=($_time$ + 60*60)

When ran 1st query got the result but when we map it with other I am not getting any result

0 Karma

Esteemed Legend

You are undoing all of my optimizations which is making your search both more complicated and slower. Using sort 1 - means that you do not need the | top parts. Go back to the first search that I posted in the answer (which I did edit within minutes after posting and which is simpler than the version you tried) and try it. If it doesn't work, post another comment.

0 Karma

Communicator

Hey woodcock i tried the query you had posted and did not get any result... After query executed i could see following message "The search result count (168) exceeds maximum (10), using max. To override it, set maxsearches appropriately.
Unable to run query ' search index=perfmon earliest>=1433772000 latest<=(1433772000 + 60*60) "

I tried adding maxsearches=168 , but still no result. It is unable to run 2nd query

0 Karma

Esteemed Legend

Right; I forgot that map has a default limit of 10; where did you add maxsearches=200? It should go at the very end of the search. Is that what you tried?

0 Karma

Communicator

Hi woodcock, I found the issue is with latest time because if i ran without latest time it is giving the result.. How to figure out latest time based on earliest+1hr in the query

0 Karma

Esteemed Legend

OK, try this:

earliest="06/08/2015:00:00" latest="06/14/2015:23:59" index=iis | bucket _time span=1h | stats count BY _time | sort - 1 count | eval time=_time | map search="search index=perfmon earliest>=$time$ latest<=$time$+3600 host=web1 (counter=\"% Processor Time\" OR counter=\"Get Requests/Sec\" OR counter=\"Current Connections\") | stats avg(Value) by host, counter"
0 Karma

Communicator

no result 😞

0 Karma

Esteemed Legend

Try this (some versions may not like leaving out seconds for times):

earliest="06/08/2015:00:00:00" latest="06/14/2015:23:59:00" index=iis | bucket _time span=1h | stats count BY _time | sort - 1 count | eval time=_time | map search="search index=perfmon earliest>=$time$ latest<=$time$+3600 host=web1 (counter=\"% Processor Time\" OR counter=\"Get Requests/Sec\" OR counter=\"Current Connections\") | stats avg(Value) by host, counter"
0 Karma

Communicator

Yeah , but i am still getting "Unable to run query ' search index=perfmon earliest>=1433772000 latest<=(1433772000 + 60*60) "

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!