topic calculate last 1 hr events for last one week in Splunk Search

calculate last 1 hr events for last one week

jayannah — Tue, 10 Jun 2014 14:27:55 GMT

I want to calculate the last 1 hour events for the past week at any given point of time. Support now the time is 11.30a.m. I want to events from 10.am to 11.30am for past 1 week.

I'm trying the below search query, but it is returning zero. Since the below query runs on _internal index, you can try on ur system too.

index=_internal source=*metrics.log splunk_server="*" group="per_index_thruput" earliest=-7d@d  
| eval MB=kb/1024 | eval duration=60*60 
| eval current_hour=strftime(now(),"%H")
| eval last_hour=strftime((now()-duration),"%H") 
| eval current_Min=strftime(now(),"%M") 
| where (date_hour=last_hour AND date_minute>current_Min) AND 
        (date_hour<=current_hour AND date_minute<=current_Min)

Whats wrong here and what is the right query?

Re: calculate last 1 hr events for last one week

lguinn2 — Tue, 10 Jun 2014 15:45:00 GMT

[Note: fixed search conditions] First, the date_* fields do not exist in all events.

Second, I suggest that you take a look at the Timewrap app - while it may not solve this problem exactly, this is the kind of problem that it is designed to solve. It's free.

And here is my suggestion

index=_internal source=*metrics.log group="per_index_thruput" earliest=-7d@d 
| eval MB=kb/1024 
| eval startTimeOfDay=strftime(relative_time(now(),"-1h@h"),"%H:%M")
| eval endTimeOfDay=strftime(now(),"%H:%M")
| eval eventDay=strftime(_time,"%Y-%m-%d ")
| eval eventEarliest=strptime(eventDay . startTimeOfDay,"%Y-%m-%d %H:%M")
| eval eventLatest=strptime(eventDay . endTimeOfDay,"%Y-%m-%d %H:%M")
| where _time >= eventEarliest AND _time <= eventLatest

This solution does not depend on the presence of the date_* fields, which sometimes don't exist and can have difficulties due to timezone.

Re: calculate last 1 hr events for last one week

somesoni2 — Tue, 10 Jun 2014 15:50:03 GMT

Try this

index=_internal source=*metrics.log splunk_server="*" group="per_index_thruput" earliest=-7d@d  
| eval MB=kb/1024 | eval duration=60*60 
| eval current_hour=strftime(now(),"%H")
| eval last_hour=strftime((now()-duration),"%H") 
| eval current_Min=strftime(now(),"%M") 
| where (date_hour=last_hour AND date_minute>current_Min) OR 
        (date_hour=current_hour AND date_minute<=current_Min)

Try this one
Slightly modified @Iguinn's answer

index=_internal source=*metrics.log group="per_index_thruput" earliest=-7d@d 
| eval MB=kb/1024 
| eval startTimeOfDay=strftime(now()-60*60*4,"%H:%M")
| eval endTimeOfDay=strftime(now(),"%H:%M")
| eval eventDay=strftime(_time,"%Y-%m-%d")
| eval eventEarliest=strptime(eventDay." ".startTimeOfDay,"%Y-%m-%d %H:%M")
| eval eventLatest=strptime(eventDay." ".endTimeOfDay,"%Y-%m-%d %H:%M")  | where _time>=eventEarliest AND _time<=eventLatest

Re: calculate last 1 hr events for last one week

jayannah — Tue, 10 Jun 2014 16:07:04 GMT

Thank you the response.

The above query doesn't return events. I just copy pasted your query in my search and no events seen. But, there are events in the _internal for the selected time in the system.

Is this query worked for you in your system?

Note that, I tried by removing space around "." in strptime functional aswell. Still no events seen.

Re: calculate last 1 hr events for last one week

jayannah — Mon, 28 Sep 2020 16:49:38 GMT

Hi Soni, Thanks for response. If I change the duration to 4 Hours i.e 60*60*4, this query wont work as expected.

Re: calculate last 1 hr events for last one week

jayannah — Tue, 10 Jun 2014 20:08:25 GMT

Thanks. Works fine.

Re: calculate last 1 hr events for last one week

jayannah — Mon, 28 Sep 2020 16:49:53 GMT

I copy pasted the same your query on my laptop and tested on _internal index. It works well.

But, on the production system where I logged in as NON-admin user with access to only SEARCH APP and Index name 'abc'.

When I issue the same command
index=_abc earliest=-7d@d
| eval period=60*60*4
| eval startTimeOfDay=strftime(now()-period,"%H:%M")
| eval endTimeOfDay=strftime(now(),"%H:%M")
| eval eventDay=strftime(_time,"%Y-%m-%d")
| eval eventEarliest=strptime(eventDay." ".startTimeOfDay,"%Y-%m-%d %H:%M")
| eval eventLatest=strptime(eventDay." ".endTimeOfDay,"%Y-%m-%d %H:%M")

Re: calculate last 1 hr events for last one week

jayannah — Tue, 10 Jun 2014 20:44:54 GMT

The fields eventEarliest and eventLatest are NOT getting created !!! is there any permission required?

When I logged in ADMIN user on my laptop these 2 fields get created, but NOT for non-admin user on production system.

Please do the needful.

Re: calculate last 1 hr events for last one week

lguinn2 — Wed, 11 Jun 2014 07:14:23 GMT

Revised answer:

index=_internal source=*metrics.log group="per_index_thruput" earliest=-7d@d 
| eval MB=kb/1024 
| eval endTimeOfDay=strftime(now(),"%H:%M")
| eval eventDay=strftime(_time,"%Y-%m-%d ")
| eval eventLatest=strptime(eventDay . endTimeOfDay,"%Y-%m-%d %H:%M")
| eval eventEarliest = relative_time(eventLatest,"-1h@h")
| where _time >= eventEarliest AND _time <= eventLatest

In the final line, I had reversed the test! Also I have updated the computations to work properly even across date boundaries.