How to calculate the duration?

[tomapatan]

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

[tomapatan · Accepted solution]

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

[gcusello]

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

[tomapatan]

@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.

[gcusello]

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

[tomapatan]

@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

[gcusello]

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