How to find the time difference for the duration of a session?

[bigrichie90]

I am trying to avoid using a join so I use an append. The whole reason behind this is to calculate the duration of a session. For some reason my time difference is not showing up. If anybody could help it would be greatly appreciated.

Here is my query. . . it is a little hard to read so I bolded the first query that grabs when they started a session, then bolded the commands at the end that I "think" will work for both queries. Hope that made it easier to read. (the first 2 queries are essentially the same).

sourcetype=ASK user=bigrichie90 action=added session=12345678-ABCD-EFGH-IJKL-MNOPQRSTUVW | eval timeAdded=strftime(_time, "%Y-%m-%d %H:%M:%S") | dedup action | append [search sourcetype=ASK user=bigrichie90 session=12345678-ABCD-EFGH-IJKL-MNOPQRSTUVW action=removed | eval timeRemoved=strftime(_time, "%Y-%m-%d %H:%M:%S") | dedup action] | eval timeDiff=timeRemoved-timeAdded | table timeDiff

[aweitzman · Accepted solution]

The quick and dirty answer here is to leave the times as is without formatting them, so they stay numeric and you can do math on them:

... | eval timeAdded=_time | ... | eval timeRemoved=_time | ... | eval timeDiff=timeRemoved-timeAdded

[martin_mueller]

For debugging, remove the table at the end and write the tostring() result into a different field... then you can see where in the calculation you're not getting the expected value rather than just seeing "the end result didn't work".

[martin_mueller]

As it was you were overwriting the original value, potentially destroying evidence for debugging. However, apparently the issue was already found 🙂

[bigrichie90]

@martin_mueller, do you mean renaming the tostring() variable then looking at the value in the field on the left pane?

@aweitzman, thanks it worked fantastically!

[bigrichie90]

Thanks for the tip about using | head 1. Also, I changed the query to what @aweitzman suggested and I am still getting no values in the timeDiff field. Here is the query. . . .

. . . | eval timeAdded=_time | head 1 | append . . .action=removed | eval timeRemoved=_time | head 1] | eval timeDiff=timeRemoved-timeAdded | eval timeDiff=tostring(timeDiff, "duration") | table timeDiff

[aweitzman]

I apologize, my answer has a bit of a brain-fart in it. Your timeAdded and timeRemoved are in separate events, so the eval you have is only getting one or the other.

This is one hard way to do it:

your-two-searches | eval ts=coalesce(timeAdded,timeRemoved) | eventstats max(ts) as t1 | eventstats min(ts) as t2 | eval timeDiff=tostring(t1-t2,"duration")

Alternatively, you might be able to solve this with transaction. See if you can define a transaction begin and end for what you're looking for, and then get the earliest and latest times in the transaction and use those values.

[martin_mueller]

Consider using | head 1 instead of | dedup action - should be miles faster, and does the same thing because the search itself makes sure that the action field only contains one value.

For formatting the timeDiff calculated by @aweitzman you can use | eval timeDiff = tostring(timeDiff, "duration").