Splunk Search

calculate last 1 hr events for last one week

jayannah
Builder

Hi

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?

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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 

View solution in original post

lguinn2
Legend

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.

jayannah
Builder

Thanks. Works fine.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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 

jayannah
Builder

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.

0 Karma

jayannah
Builder

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")

0 Karma

jayannah
Builder

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

0 Karma

lguinn2
Legend

[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.

jayannah
Builder

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.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...