I want to figure out, how long an employee inside office. Once employee enters into office he will do card swipe that we can call it as IN time, and after sometime he can go for tea or coffice out side then he can do OUT. now we need to calculate the time inside office means difference between IN and out. After tea/coffee he can come in and do swipe IN, like that he can do many INs and OUTs in a day. We want to calculate total time he spent in office. Below sample shows the data. Can anyone answer plz
OfficeIn Cardswipe IN 12:21:50 OUT 13:46:41 IN 14:31:19 OUT 17:54:24 IN 18:32:45 OUT 19:16:22 IN 19:25:27 OUT 21:46:49
Hi @srinivasup - apologies, I had not realised you were loading these from a CSV. The issue with the searches above is that they rely on the _time variable to calculate duration. To solve this you need to set _time from the values in the CSV. I have loaded your example into my test system and the following seems to work - (strangely the starts/ends with is not working as I had expected, but works fine without it).
|inputlookup security.csv| rename "Emp No" as Emp_no "Swipe Status" as Status |eval time=Date." ".SwipeTime|eval _time=strptime(time, "%d/%m/%y %H:%M:%S")|transaction Emp_no maxevents=2|stats sum(duration) as totalDuration by Emp_no
I think this query is not giving exact results. If we can see the csv file, for one employee there are multiple Time IN and multiple TIme OUT values are associated. We need to calculate total time spent in office for each employee.
After i executed the above query it is giving duration values. Do we need to convert them into human readable if yes how?
That is what its doing:
Using your example CSV above when i run that search i get:
(Total duration is seconds)
Take user 333 (18:39:43-18:01:05) is 38:38 or 2318 seconds
(19:04:01-18:43:32) is 20:29 or 1229 seconds.
2318 + 1229 = 3547seconds clocked in.
assuming you have a uniqie id for each user/card - cardId
your search|transaction cardId startswith=(OfficeIn="IN") endswith=(OfficeIn="OUT") maxevents=2|stats sum(duration) as totalDuration by cardId
It is csv file and looks like exactly below
Emp No Name Date Status SwipeTime
111 aaa 10/6/16 Time IN 9:11:18
111 aaa 10/6/16 Time OUT 9:50:25
111 aaa 10/6/16 Time IN 9:55:10
111 aaa 10/6/16 Time OUT 12:38:28
111 aaa 10/6/16 Time IN 12:41:18
222 bbb 10/6/16 Time OUT 13:05:05
222 bbb 10/6/16 Time IN 14:06:00
222 bbb 10/6/16 Time OUT 15:50:31
222 bbb 10/6/16 Time IN 15:53:03
222 bbb 10/6/16 Time OUT 17:58:47
333 ccc 10/6/16 Time IN 18:01:05
333 ccc 10/6/16 Time OUT 18:39:43
333 ccc 10/6/16 Time IN 18:43:32
333 ccc 10/6/16 Time OUT 19:04:01
Please post the splunk query which calcualte the total duration of time spent in office
is that file in splunk, with extracted field names?
can you run a search like
SwipeTime=* and get results? -if yes try the following:
sourcetype=yoursourcetypename|transaction Emp_No startswith=(Status="IN") endswith=(Status="OUT") maxevents=2|stats sum(duration) as totalDuration by Emp_No
|inputlookup security.csv | rename "Emp No" as Emp_no "Swipe Status" as Status |transaction Emp_no startswith=(Status="Time IN") endswith=(Status="Time OUT") maxevents=2|stats sum(duration) as totalDuration by Emp_no
Executed above query but no result found.. anything missing
A quick way would to use transaction. Based on your data, it might go something like this:
<search_to_find_data> | transaction startswith="IN" endswith="OUT" max_span=12h | stats sum(duration) as Total_IN_Time
The transaction command will take your events and combine them on IN/OUT as a start/end point, respectively. The max span helps optimize the command by saying only look within a 12 hour window for an IN/OUT combination. Then, sum the durations of the IN/OUT transactions (time spent per IN/OUT) into a total time in Seconds.