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

[caiomozer]

Hello,

I have a situation where I need to check if a time field, 'report_date' 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 report_date 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=raw_maximo INCIDENTE=I* GR_RESP="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...)

[darrenfuller]

Is the _time of the event equal to the report_date 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...

[DalJeanis · Accepted solution]

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" 

[caiomozer]

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

[DalJeanis]

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