Getting Data In
Highlighted

How to check if a time field is between two hour values?

Engager

Hello,

I have a situation where I need to check if a time field, 'reportdate' in format "%Y-%m-%d %H:%M:%S" happened between 7 AM and 4 PM of that same day. I cant figure out how to do that comparison. I dont know how to get the hour value from my reportdate field.

I'm trying to do that so I can make a filter to see how many reports were made in a specific period of the day so I can tell which shift recieved the report (the recieving time is not the same as the event time in splunk in that particular scenario), and I need to filter by shift.

So far what I did:
index=rawmaximo INCIDENTE=I* GRRESP="OPERACAO"
| eval shift1=strptime(report_date,"%Y-%m-%d %H:%M:%S")
| where shift1 >= "07:00:00" AND shift1 <"16:00:00" (SOMETHING HAS TO BE CHANGED HERE, I'm comparing time with string atm)
|stats count(INCIDENTE) (I dont really remember what goes here, but not relevant, is just a count...)

0 Karma
Highlighted

Re: How to check if a time field is between two hour values?

SplunkTrust
SplunkTrust

Your report-date field is in this format - "%Y-%m-%d %H:%M:%S"

So an example looks like

2020-04-23 12:12:21 

That hour is already in the format you want. strptime is the wrong tool here. All you have to do to get what you want is to drop the first 11 characters and keep the last 8.

Try this:

index=raw_maximo INCIDENTE=I* GR_RESP="OPERACAO"
| eval shift1=substr(report_date,12,8)
| where shift1 >= "07:00:00" AND shift1 <"16:00:00" 

View solution in original post

0 Karma
Highlighted

Re: How to check if a time field is between two hour values?

Engager

YES! I can`t believe it was THAT simple! Thank you so much! It worked!

Highlighted

Re: How to check if a time field is between two hour values?

SplunkTrust
SplunkTrust

@caiomozer - It's always easiest to see someone ELSE's issues. Can't tell you the number of hours we've spent chasing things like that over the decades.

0 Karma
Highlighted

Re: How to check if a time field is between two hour values?

Contributor

Is the time of the event equal to the reportdate field?

If so, you could do something like this:

index=raw_maximo INCIDENTE=I* GR_RESP=OPERACAO" date_hour>=7 date_hour<=16

if not, then you can do something like this:

| makeresults
| eval report_date="2020-04-20 16:20:00|2020-04-20 09:04:20|2020-04-20 17:04:20|2020-04-20 04:20:00|2020-04-20 08:04:20"
| makemv report_date delim="|" | mvexpand report_date | fields - _time
| eval report_date_hour=strftime(strptime(report_date, "%Y-%m-%d %H:%M:%S"), "%H")
| eval is_between_700_and_1600=if(report_date_hour>=7 AND report_date_hour<16, 1, 0)

Where you strptime the string to get epoch and strftime the output to get hour by itself...

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.