Splunk Search

How to find the difference between dates only counting business hours?

Path Finder

HI,I have two fields A and B with time format as 1/07/2014 3:41:12 PM.
e.g., if A is 1/07/2014 3:41:12 PM and B is 15/07/2014 2:41:12 PM.
To find difference i know we need to use,
"eval epochA=strptime('Field A',"%d/%m/%Y %I:%M:%S %p") | eval epochB=strptime('Field B',"%d/%m/%Y %I:%M:%S %p") | eval diff=round(('epochB'-'epochA') / 3600)|table diff"

Please let me know how to find difference in hours, taking into account only business hours i.e., from 9AM to 5PM and excluding Saturday,Sunday,Public Holiday.

Tags (3)

SplunkTrust
SplunkTrust

okay, hard to test without any real data ... try this:

 source="file.csv" date_month=august date_hour>08 AND date_hour<18 NOT ( date_wday=Saturday OR date_wday=Sunday )
   | eval epoch_A=strptime('start_TIME',"%d/%m/%Y %I:%M:%S %p") 
   | eval epoch_B=strptime('close_TIME',"%d/%m/%Y %I:%M:%S %p") 
   | eval total_TIME=if((epoch_B-epochA)>57600, (epoch_B-epcoh_A-57600)/3600, (epoch_B-epcoh_A)/3600 ) 
   | table start_TIME, close_TIME, total_TIME

The if statement is based on the fact, that if the difference between start and end time is bigger as 57600 seconds (17 hours or 5pm to 9am) then this job was running over night and you simply minus those 17 hours from the total time.

hope this helps ... and this is un-tested 😉

cheers, MuS

SplunkTrust
SplunkTrust

Hi karthikTIL,

Each event has a field called date_hour which has the hour of the day as value, maybe this is of help in your case.
Something like this just to give an example on how to use date_hour:

Your base search date_hour>08 OR date_hour<18 | your next cammond

UPDATE:

Basically you can just add the date_hour and date_wday to the base search like this :

source="file.csv" date_month=august date_hour>08 AND date_hour<18 NOT ( date_wday=Saturday OR date_wday=Sunday ) 
| bucket _time span=1d 
| eval epoch_A=strptime('start_TIME',"%d/%m/%Y %I:%M:%S %p") 
| eval epoch_B=strptime('close_TIME',"%d/%m/%Y %I:%M:%S %p") 
| eval total_TIME=(('epoch_B'-'epoch_A') / 3600)
| table start_TIME,close_TIME,total_TIME

This way you will only get back results for this time range ( 9am to 5pm ) and only on weekdays Mo - Fr. You can decide if you need the bucket or not, this will group all events into per day slices, which will be used in timechart or chart for example.

For the public holidays you would have to use a lookup.

Cheers, MuS

0 Karma

Path Finder

Sorry, it did not work.
If starttime = 26/08/2014 3:23:29 PM and closetime=27/08/2014 12:15:18 PM , your query gives me totaltime=20.86 hours, whereas i expect closetime = 4.52hours.
Not sure if converting epoch time is culprit here.Or am i doing something wrong here?

0 Karma

Path Finder

HI MuS, is there any update you want to provide please

0 Karma

SplunkTrust
SplunkTrust

see my update

0 Karma

Path Finder

HI MuS, Thank you. i got your point but don't know how exactly to include your statement in my query,Could you please le tme know.

My current query is,
source="file.csv" datemonth=august|eval epochA=strptime('startTIME',"%d/%m/%Y %I:%M:%S %p") | eval epochB=strptime('closeTIME',"%d/%m/%Y %I:%M:%S %p") | eval totalTIME=(('epochB'-'epochA') / 3600)|table startTIME,closeTIME,total_TIME

It converts closetime and starttime into epoch time and find the difference(24 hours day).Please let me know how to include your statement above to get total working hours.

0 Karma

SplunkTrust
SplunkTrust

Not exactly. date_hour starts at 0 (midnight to 1am) and ends with 23 (11pm to midnight). So using datehour>8 starts at 9am and datehour<18 ends at 5pm.

use this run everywhere command:

index=_internal date_hour>08 AND date_hour<18 NOT ( date_wday=Saturday OR date_wday=Sunday ) series | bucket _time span=1d | streamstats first(date_hour) AS first_hour, last(date_hour) AS last_hour by series,_time | eval diff=first_hour-last_hour | chart span=1d values(diff) AS diff over _time by series

this will calculate the diff for first hour to the last hour seen each day per series.

0 Karma

Path Finder

HI MuS, I guess the above will give events which started between 8 and 18 hrs. what i am after is,when i calculate difference between close time and start time, i need to consider only from 9 AM to 5 PM

e.g., start time=8/09/2014 9AM; end time= 9/09/2014 10AM
when i do end time- start time, it should be 9 hrs

0 Karma