topic Re: subtracting two timestamps per logEvevtType in Splunk Search

How to subtract two timestamps per logEvevtType?

user33 — Tue, 24 Jan 2023 00:21:37 GMT

Hello, apologies if this was stated previously. I have multiple calls - each RequestID with a RequestReceive and ResponseTransmit. I am trying to find the difference between the two timestamps below. The difference of ResponseTransmit timestamp and RequestReceive timestamp. Then put that into a stats command ordered by clientPathURI and then the difference between the timestamps.

Any assistance is much appreciated!

{ [-]
   RequestID: b74fab20-9a7b-11ed-bd70-c503548afa99
   clientPathURI: signup
   level: Info
   logEventType: ResponseTransmit
   timestamp: 2023-01-22T12:43:57.547-05:00
}

{ [-]
   RequestID: b74fab20-9a7b-11ed-bd70-c503548afa99
   clientPathURI: signup
   }
   level: Info
   logEventType: RequestReceive
   timestamp: 2023-01-22T12:43:57.496-05:00
}

Re: subtracting two timestamps per logEvevtType

bowesmana — Mon, 23 Jan 2023 03:53:06 GMT

Assuming the "}" in the second block is not really there and the fields from the event are extracted as per their JSON names, then this will aggregate min/max timestamps and calculated duration

| eval ts=strptime(timestamp, "%FT%T.%Q-%:z") | stats min(ts) as mints max(ts) as maxts by clientPathURI RequestID | eval duration=maxts-mints

then if you want to aggregate based on the clientPathURI only, do another stats, e.g.

| stats avg(duration) as avgDuration by clientPathURI

Re: subtracting two timestamps per logEvevtType

user33 — Mon, 23 Jan 2023 13:30:12 GMT

Thank you, that is a huge help. Question, if I had multiple calls, how do I get the SPL to subtract timestamp by RequestID? I don't need the RequestID in the stats, but want the SPL to capture the difference in timestamps per call. And then take the 95th percentile of that call per day?

Re: subtracting two timestamps per logEvevtType

user33 — Mon, 23 Jan 2023 13:33:16 GMT

as a follow up, each "transaction" or "call" has one RequestID. Each RequestID with two timnestamps, one Request and one Response. Something like the below? Any assistance is appreciated.

Date	ClientPathURI	Number of calls	95thpercentile of Duration

Re: subtracting two timestamps per logEvevtType

bowesmana — Mon, 23 Jan 2023 23:16:21 GMT

You can do all that in the last stats command, so do this

| bin _time span=1d | eval ts=strptime(timestamp, "%FT%T.%Q-%:z") | stats min(ts) as mints max(ts) as maxts by _time clientPathURI RequestID | eval duration=maxts-mints | stats count as Calls perc95(duration) as p95Duration by _time clientPathURI

so this is doing

line 1 - creates a time bucket to calculate statistics by day
line 2 - converts timestamp to epoch
line 3 - calculates min/max timestamp by URI and Request
line 4 - calculates duration
line 5 - counts the calls, 95th percentile of duration by day and URI

Some caveats here

If you don't have both request/response for each call, then duration will be 0 for that, as min/max are the same
If a call goes over midnight, you will get 1 call with duration 0 on each day for the same RequestID

If relevant, you may want to consider error/failure status in these if they are significant and if they affect the duration in a meaningful way.

Re: subtracting two timestamps per logEvevtType

user33 — Tue, 24 Jan 2023 17:47:39 GMT

Awesome! Excellent insights!! This solution worked out great. I will take a look at failures as well. Thank you very much for this!!