topic Re: How to get the time difference of two timestamp in minutes in Splunk Search

How to get the time difference of two timestamp in minutes?

mikeyty07 — Thu, 20 Jul 2023 16:53:57 GMT

I 've two fields one is _time and another one is received_time. I want to get the time differences between these two timestamp.

Logs look like
2023-07-11 11:19:24.964 ..... received_time= 1688574223791

I converted the epoch to human readable but i couldnt get the time differences between these two timestamp.

my search:

<query> | rex "received_time\"\:(?<recTime>[^\,]+)" | eval recTime = strftime(recTime/1000, "%Y-%m-%d %H:%M:%S.%3N") | eval diff = recTime - _time | table recTime _time diff

but it doesnt show any data on diff. Am I missing something?

Re: How to get the time difference of two timestamp in minutes

gcusello — Thu, 20 Jul 2023 16:45:07 GMT

Hi @mikeyty07

you can calculate diff before tranforming timestamps from epoch to human readable.

<your_search> | rex "received_time\"\:(?<recTime>[^\,]+)" | eval diff = recTime - _time | eval recTime = strftime(recTime/1000, "%Y-%m-%d %H:%M:%S.%3N") | table recTime _time diff

Ciao.

Giuseppe

Re: How to get the time difference of two timestamp in minutes?

PickleRick — Thu, 20 Jul 2023 17:10:02 GMT

If you extract your recTime as a string from the raw event you have to either parse it into a numerical timestamp with strptime() or at least (if it's already in epoch) cast it to integer by using tonumber().

EDIT: OK. I just noticed that in your raw data timestamp is already numerical. So you need to do

| eval diff = tonum(recTime)/1000 - _time

Also - when you're manipulating time, it's often more convenient to use fieldformat instead of eval to produce human-readable time strings. But if you insist on rendering timestamp to string with eval, do it after you calculate the diff part.

Re: How to get the time difference of two timestamp in minutes

mikeyty07 — Thu, 20 Jul 2023 19:35:44 GMT

_time	recTime	diff
2023-07-11 11:12:44.888	2023-07-11 11:15:55.999	0 00:03:11.111

the diff is coming in numerical can it be done like this table?

Re: How to get the time difference of two timestamp in minutes

gcusello — Fri, 21 Jul 2023 05:52:23 GMT

Hi @mikeyty07,

yes, it's possible, please try this:

Ciao.

Giuseppe

Re: How to get the time difference of two timestamp in minutes

PickleRick — Fri, 21 Jul 2023 11:25:17 GMT

Sure. Since _time is being internally stored as a number of seconds, all arithmetics involving this number will yield number of seconds. There are several functions allowing you to convert it to more human-friendly format.

Of course you could calculate it manually, but you can use - as @gcusello already showed - the convert() function. I would however use fieldformat instead of eval to keep the value internally as a number (which makes it easier to do subsequent manipulations if you need it or sorting).

Re: How to get the time difference of two timestamp in minutes

mikeyty07 — Fri, 28 Jul 2023 16:16:15 GMT

this gave me the idea to change time to numeric and from there strftime and do calculate the time diff

Re: How to get the time difference of two timestamp in minutes

gcusello — Sat, 29 Jul 2023 05:05:14 GMT

Hi @mikeyty07 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉