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?
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
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?
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
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
You see, took me 9 minutes to write this answer 🙂
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.
As said; un-tested and usually I don't use any kind of sub searches.
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"
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?
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
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.
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
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?
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
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"
no result 😞
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"
Yeah , but i am still getting "Unable to run query ' search index=perfmon earliest>=1433772000 latest<=(1433772000 + 60*60) "