Splunk Search

How to subtract two timestamps per logEvevtType?

user33
Path Finder

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!

 

[-]
   RequestIDb74fab20-9a7b-11ed-bd70-c503548afa99
   clientPathURIsignup
   levelInfo
   logEventTypeResponseTransmit
   timestamp2023-01-22T12:43:57.547-05:00

}

 

[-]
   RequestIDb74fab20-9a7b-11ed-bd70-c503548afa99
   clientPathURIsignup
   }

   levelInfo
   logEventTypeRequestReceive
   timestamp2023-01-22T12:43:57.496-05:00
}

Labels (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

 

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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
0 Karma

user33
Path Finder

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.

DateClientPathURINumber of calls95thpercentile of Duration
    
    
0 Karma

user33
Path Finder

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?

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma

user33
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...