How to search the count of an event for the last sixty minutes, and the count of the same event for the same hour yesterday?

[govindsinghrawa]

How to get the count of an event (say logins) in last sixty minutes and the count of same event for same hour yesterday? Result should be as:

Today hh:mm:ss Count
Yesterday hh:mm:ss Count

[gokadroid]

Lets assume that the event you have can be uniquely identified by yourBaseSearch, so your base search should return you unique events for whatever you are counting, then search twice and append them.

1. One search to return today's count for an hour ago

   yourBaseSearch earliest=-60m latest=now()
   |fields anyFieldOfyoursToEnsureCountingOfEvents
   | timechart span=1m count 
   | eval _time=_time-now()%3600 
   | timechart span=1h sum(count) as count 
   | tail 3 | tail 2 
   | eval _time=_time+now()%3600
   |tail 1 
   

2. SubSearch to return yesterday's count by shifting earliest and latest by 25 and 24 hours(in minutes to be accurate till minutes):
   

   
   
   search yourBaseSearchAgain earliest=-1500m latest=-1440m 
   
   | timechart span=1m count 
   
   | eval _time=_time-now()%3600 
   
   | timechart span=1h sum(count) as count 
   
   | tail 3 | tail 2 
   
   | eval _time=_time+now()%3600
   
   |tail 1
   
   

3. Append search 1 and 2

search1Above
|append [search2Above]
|sort +_time

NOTE: _time=_time-now()%3600 is given just to push the time in display to return the time correctly to represent since when the count is being taken.

[govindsinghrawa]

tested it as an answer post . seems to be working .

[inventsekar]

we can use date_hour and solve this specific timeframe issue.
Try this one -

index=main sourcetype=yourSourcetype earliest=-2d latest=now (date_hour > 1 OR date_hour < 2) | chart count(Failure) by host

Instead of chart, you use
|stats count AS Count

The date_hour, earliest /latest, combinations can be fine tuned.

[twinspop]

I don't ever trust date_hour. Lots of past discussion on this. Search the archives.

[govindsinghrawa]

will it not just return for 1st and 2nd hour and not for 60 minutes ago from now. I will try though but seems not to be complete. Thanks a lot for helping out though

[twinspop · Accepted solution]

your search earliest=-60m@m latest=@m| stats min(_time) as _time count as Count | eval Day="Today" | fields Day, _time, Count | append [ search your search earliest=-1d@m-60m latest=-1d@m | stats min(_time) as _time count as Count | eval Day="Yesterday" | fields Day, _time, Count ]

[govindsinghrawa]

seems to work, thanks

[sundareshr]

Try this

base search earliest=-1d@d | eval when=if(_time>relative_time(now(), "@d"), "Today", "Yesterday") | eval t=strftime(_time, "%H") | chart count over t by when

[govindsinghrawa]

getting data for all hours and not just one hour of today and yesterday same hour