Streamstats reset at 9.00am every day, even without a 9.00am event

[davidjaniec]

I'm very stuck, how can I have a streamstats function accumulate a total and reset at 9.00am every day? 

It's straightforward if I have an event at 9.00am, but if the last event was at say 8.55am, then the next event is at 9.15am, the reset occurs, however, it will continue to reset for all events which occur between 9.00am and 9.59am as the statement remains true throughout the hour below in my example.

index=main | eval Hour=strftime(_time,"%H")
| streamstats reset_after="("Hour==09")" sum(Result) as Total

I tried to experiment with specifying the minute, but the same situation exists if the 9.00am minute does not exist.

index=main | eval Hour=strftime(_time,"%H%M")
| streamstats reset_after="("Hour==0900")" sum(Result) as Total

I think I need to either make a lookup to create an event every 9 am for each day, but I couldn't figure that out if the time range was greater than one day. I experimented with makeresults to create an event, but this needed an append which messed up all of my other parts of the query.

I think the most elegant way to do this is to have an event created for every 9 am before the query is made, but I can't figure it out, any advice/ideas are welcomed!

Dave

[davidjaniec]

I'll admit I still haven't had success with this, can you explain why this doesn't work?

| makeresults count=5
| streamstats count
| eval age = case(count=1, 25, count=2, 39, count=3, 31, count=4, null())
| eval city = case(count=1 OR count=3, "San Francisco", count=2 OR count=4, "Seattle")
| eval _day=relative_time(_time,"-9h@d")

[ITWhisperer · Accepted solution]

| eval _day=relative_time(_time,"-9h@d")
| streamstats sum(Result) as Total by _day

[davidjaniec]

Thanks for the reply, but isn't that relative to when you run it? Not absolute at 9 am every day?

[ITWhisperer]

The function works on the arguments given - _time is the time from each event, -9 hours takes any _time before 9am into the previous day, and @day snaps to the beginning of that day.

[davidjaniec]

Ahh brilliant thanks so much, I didn't have a derived timestamp in the data so I didn't recognise it. Thanks!