topic Re: Measuring time difference between 2 entries in Splunk Search

Measuring time difference between 2 entries

Silah — Thu, 13 Jun 2024 13:35:26 GMT

I am getting a log feed for a transactional system. Each log entry has a status either End, Begin or something in between (but for this I don't care about the in between) and a UUID to mark that they belong to the same transaction.

I am struggling to write a search query that essentially subtracts the _time from the BEGIN entry ud UUID123, from the _time from the END entry with the same UUID. Obviously, my goal is to get the time it took the transaction to complete but I am not sure how to compare fields in two entries with the same UUID.

Any ideas ?

Thanks

Re: Measuring time difference between 2 entries

gcusello — Thu, 13 Jun 2024 13:43:45 GMT

Hi @Silah ,

you could try to run something like this:

index=your_index status IN (Begin, End) | stats earliest(eval(status="Begin")) AS Begin_time latest(eval(status="End")) AS End_time BY UUID | eval diff=End_time-Start_time | table UUID diff

then you can manage the incomplete conditions: e.g. there's only one event (Start or End)

Ciao.

Giuseppe

Re: Measuring time difference between 2 entries

Silah — Thu, 13 Jun 2024 13:59:33 GMT

Hi @gcusello

Thank you, this gets me started.

I assume that

| eval diff=End_time-Start_time

should actually be

| eval diff=End_time-Begin_time

as it is called Begin_time in the earliest eval of the Begin event in the Stats part

It does sort of work, My search query is identifying 4000 events and the table lists out 2000 by their UUID, so it has accurately identified that there is a Begin and End pair for each UUID, however the "diff" field of the table is blank for all of them.

When I check the field, the value of diff is "null".

Re: Measuring time difference between 2 entries

Silah — Thu, 13 Jun 2024 14:01:16 GMT

Sorry I should have added that I tried listing the begin_time and end_time in the table also, and both values are simply "True" and not a time stamp

Re: Measuring time difference between 2 entries

gcusello — Thu, 13 Jun 2024 14:07:13 GMT

Hi @Silah ,

yes, it was a mistake!

index=your_index status IN ("Begin", "End") | stats earliest(eval(status="Begin")) AS Begin_time latest(eval(status="End")) AS End_time BY UUID | eval diff=End_time-Begin_time | table UUID diff

anyway, you ha ve to separately check the two conditions (status="Begin" and status="End") to verify that you have in those events the status and UUID fields.

You can also add to the final table command also the Begin_time and End_time fields to see if they are present or not.

Remember to use always quotes in the eval commands.

Ciao.

Giuseppe

Re: Measuring time difference between 2 entries

gcusello — Thu, 13 Jun 2024 14:11:39 GMT

Hi @Silah ,

I saw your second messge only after my answer, plese try this:

Let me understand: what's the value of status in Begin and End events?

You have to check these conditions in the evals:

index=your_index status IN ("Begin", "End") | stats earliest(eval(if(status="Begin",_time,""))) AS Begin_time latest(eval(if(status="End",_time,""))) AS End_time BY UUID | eval diff=End_time-Begin_time | table UUID diff

Ciao.

Giuseppe

Re: Measuring time difference between 2 entries

Silah — Thu, 13 Jun 2024 14:15:15 GMT

This worked perfectly. Thanks @gcusello really appreciate your help.

Re: Measuring time difference between 2 entries

gcusello — Thu, 13 Jun 2024 14:32:09 GMT

Hi @Silah

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉