topic Re: Calculate the duration in Splunk Search

How to calculate the duration?

tomapatan — Tue, 18 Oct 2022 14:23:12 GMT

Hi,

I`ve got the following search that I would like to amend as follows:

1. swipe_in and swipe_out times to show on the same row for each "transaction" (in and out being considered a transaction).

2. only show the duration for swipe_in and swipe_out and not for swipe_out-swipe_in.

Essentially my table should display: swipe_in times, swipe out times and duration.

Thank you in advance.

Search details:

| eval location_desc=if(match(location_desc,"OUT"), "swipe_out", "swipe_in")
| sort _time
| streamstats window=2 current=f first(_time) as previous_swipe
| eval duration=round((_time-previous_swipe)/3600, 2)
| table location_desc, _time, duration

Re: Calculate the duration

gcusello — Tue, 18 Oct 2022 12:01:09 GMT

Hi @tomapatan,

you could try something like this:

<your_search> | stats earliest(eval(if(match(location_desc,"OUT"),"",_time))) AS swipe_in latest(eval(if(match(location_desc,"OUT"),_time,""))) AS swipe_out | eval duration=swipe_out-swipe_in

Please, next time, add your search also as text

Ciao.

Giuseppe

Re: Calculate the duration

tomapatan — Tue, 18 Oct 2022 12:09:18 GMT

@gcusello

Thanks for your answer.

This search returns only the very first and very last swipe_in and swipe_out, I need to calculate the duration of every swipe_in and swipe_out during a certain period of time.

Re: Calculate the duration

gcusello — Tue, 18 Oct 2022 12:16:14 GMT

Hi @tomapatan,

this is one of the few cases to use the thansaction command.

if the swipe_ is identiiable with "*IN*" and swipe_out with "*OUT*", please trysomething like this:

<your-search> | transaction startswith=location_desc="*IN*" endswith=location_desc="*OUT*" | table _time duration

otherwise, please tell me how to identify swipe_in and swipe_out.

Ciao.

Giuseppe

Re: Calculate the duration

tomapatan — Tue, 18 Oct 2022 12:28:34 GMT

@gcusello

Thanks.
I`ve also looked at the transaction command, but I`m only getting the swipe_in time stamp and missing the swipe_out data. Duration is calculated OK, but ideally I`d like to be able to display a table that contains:

swipe_in swipe_out duration

2022-10-17 08:02:19	2022-10-17 16:02:19	08:00:00

This is what I`m currently getting:

location_desc _time duration

swipe_in swipe_out	2022-10-17 08:02:19	08:45:18
swipe_in swipe_out	2022-10-11 08:04:37	08:26:48
swipe_in swipe_out	2022-10-10 08:15:24	08:05:37

Re: Calculate the duration

gcusello — Tue, 18 Oct 2022 12:57:31 GMT

Hi @tomapatan,

as you can read in the above link startswith and endswith take as value a string or an eval expressin, so you could try something like this (I never tested!):

<your_search> | transaction maxevents=2 startswith=eval(NOT match(location_desc,"OUT")) endswith=eval(match(location_desc,"OUT")) | table _time duration

Ciao.

Giuseppe

Re: How to calculate the duration?

tomapatan — Wed, 19 Oct 2022 09:18:36 GMT

This is what did the trick for me, although I am convinced there are more elegant ways of writing this search.

{location_desc}=_time - > pairs each Entrance and Exit with it`s corresponding timestamp

Key was to stitch the data together using mvzip then to expand it using mvexpand so I can have Entrance and Exit times displayed on the same row.

Search details:

| fields first_name last_name _time, location_desc
| eval location_desc=if(match(location_desc,"OUT"), "Exit", "Entrance")
| eval
name=first_name." ".last_name,
{location_desc}=_time
| stats values(Entrance) as Entrance values(Exit) as Exit by name
| eval combined_data=mvzip(Entrance,Exit,"|")
| mvexpand combined_data
| eval
fields=split(combined_data, "|"),
Entrance=mvindex(fields,0),
Exit=mvindex(fields,1),
duration=round((Exit-Entrance)/3600, 2),
Entrance=strftime(Entrance, "%d/%m/%Y %H:%M:%S"),
Exit=strftime(Exit, "%d/%m/%Y %H:%M:%S")
| table name, Entrance, Exit duration