Getting Data In

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

Path Finder

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

1 Solution

Motivator

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

View solution in original post

Motivator

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

View solution in original post

SplunkTrust
SplunkTrust

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

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

0 Karma

Path Finder

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

0 Karma

Motivator

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.

SplunkTrust
SplunkTrust

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").