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?
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
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.
Thanks. Works fine.
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
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.
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")
Hi Soni, Thanks for response. If I change the duration to 4 Hours i.e 60*60*4, this query wont work as expected.
[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.
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.