topic How to check if a time field is between two hour values? in Getting Data In

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

caiomozer — Wed, 30 Sep 2020 05:05:29 GMT

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

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

DalJeanis — Thu, 23 Apr 2020 22:13:15 GMT

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"

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

darrenfuller — Wed, 30 Sep 2020 05:08:27 GMT

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

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

caiomozer — Fri, 24 Apr 2020 21:58:17 GMT

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

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

DalJeanis — Thu, 30 Apr 2020 15:35:16 GMT

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