How to subtract two timestamps per logEvevtType?

[user33]

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
}

[bowesmana]

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

[user33]

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

[user33]

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?

[bowesmana · Accepted solution]

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.

[user33]

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