topic Re: Secuirty checks with help of splunk in Splunk Search

Secuirty checks with help of splunk

srinivasup — Tue, 21 Feb 2017 12:37:06 GMT

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

Re: Secuirty checks with help of splunk

alacercogitatus — Tue, 21 Feb 2017 13:38:01 GMT

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.

Re: Secuirty checks with help of splunk

nickhills — Tue, 21 Feb 2017 13:45:48 GMT

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

Re: Secuirty checks with help of splunk

srinivasup — Tue, 21 Feb 2017 13:52:02 GMT

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

Re: Secuirty checks with help of splunk

nickhills — Tue, 21 Feb 2017 16:07:26 GMT

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

Re: Secuirty checks with help of splunk

srinivasup — Tue, 29 Sep 2020 13:00:12 GMT

|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

Re: Secuirty checks with help of splunk

nickhills — Tue, 21 Feb 2017 20:34:18 GMT

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

Re: Secuirty checks with help of splunk

srinivasup — Wed, 22 Feb 2017 10:38:33 GMT

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?

Re: Secuirty checks with help of splunk

nickhills — Wed, 22 Feb 2017 11:10:13 GMT

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.