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.
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
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 😉
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
Your base search date_hour>08 OR date_hour<18 | your next cammond
Basically you can just add the
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
chart for example.
For the public holidays you would have to use a lookup.
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?
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.
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.
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