Archive

Secuirty checks with help of splunk

Explorer

Hi,

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 
Tags (1)
0 Karma

Ultra Champion

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
0 Karma

Explorer

Hi,

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?

0 Karma

Ultra Champion

That is what its doing:
Using your example CSV above when i run that search i get:
"Emp_no",totalDuration
111,12145
222,3807
333,3547
(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.

0 Karma

Ultra Champion

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
0 Karma

Explorer

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

0 Karma

Ultra Champion

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
0 Karma

Explorer

|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

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!