Getting Data In

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

caiomozer
Engager

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

0 Karma
1 Solution

DalJeanis
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

darrenfuller
Contributor

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

0 Karma

DalJeanis
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

caiomozer
Engager

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

DalJeanis
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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!