Splunk Search

Help with search for average response time based on TotalTime value

dhirajnangar
Engager

I have multiple log events like below based on my search criteria-

2020-03-11 08:23:55,141 - [UserId=xyz | UserName=abc |  INFO  
                    INFO  APIName="REPORT SEARCH",Stage="exit",StartTime="2020-03-11 08:23:55.101",EndTime="2020-03-11 08:23:55.141",**TotalTime**="40 Milliseconds",XBAPILatency="0 Milliseconds",XBLatency="40 Milliseconds",XBMessage="REPORT SEARCH API response was 40 Milliseconds.",RequestStatus="Success"

2020-03-11 08:23:55,151 - [UserId=xyz | UserName=abc |  INFO  
                    INFO  APIName="REPORT SEARCH",Stage="exit",StartTime="2020-03-11 08:23:55.101",EndTime="2020-03-11 08:23:55.151",**TotalTime**="50 Milliseconds",XBAPILatency="0 Milliseconds",XBLatency="50 Milliseconds",XBMessage="REPORT SEARCH API response was 50 Milliseconds.",RequestStatus="Success"


2020-03-11 08:23:55,161 - [UserId=xyz | UserName=abc |  INFO  
                    INFO  APIName="REPORT SEARCH",Stage="exit",StartTime="2020-03-11 08:23:55.101",EndTime="2020-03-11 08:23:55.161",**TotalTime**="60 Milliseconds",XBAPILatency="0 Milliseconds",XBLatency="60 Milliseconds",XBMessage="REPORT SEARCH API response was 60 Milliseconds.",RequestStatus="Success"

I want to build a Splunk query which will give me average response time based on TotalTime value.

I tried to do so by | stats avg(TotalTime) but no results are showing as the value contains a string (Milliseconds) as well.
Can someone please help me with this as I am new to Splunk tool?

0 Karma

woodcock
Esteemed Legend

Like this:

| makeresults 
| eval raw="2020-03-11 08:23:55,141 - [UserId=xyz | UserName=abc |  INFO  
                     INFO  APIName=\"REPORT SEARCH\",Stage=\"exit\",StartTime=\"2020-03-11 08:23:55.101\",EndTime=\"2020-03-11 08:23:55.141\",**TotalTime**=\"40 Milliseconds\",XBAPILatency=\"0 Milliseconds\",XBLatency=\"40 Milliseconds\",XBMessage=\"REPORT SEARCH API response was 40 Milliseconds.\",RequestStatus=\"Success\":::2020-03-11 08:23:55,151 - [UserId=xyz | UserName=abc |  INFO  
                     INFO  APIName=\"REPORT SEARCH\",Stage=\"exit\",StartTime=\"2020-03-11 08:23:55.101\",EndTime=\"2020-03-11 08:23:55.151\",**TotalTime**=\"50 Milliseconds\",XBAPILatency=\"0 Milliseconds\",XBLatency=\"50 Milliseconds\",XBMessage=\"REPORT SEARCH API response was 50 Milliseconds.\",RequestStatus=\"Success\":::2020-03-11 08:23:55,161 - [UserId=xyz | UserName=abc |  INFO  
                     INFO  APIName=\"REPORT SEARCH\",Stage=\"exit\",StartTime=\"2020-03-11 08:23:55.101\",EndTime=\"2020-03-11 08:23:55.161\",**TotalTime**=\"60 Milliseconds\",XBAPILatency=\"0 Milliseconds\",XBLatency=\"60 Milliseconds\",XBMessage=\"REPORT SEARCH API response was 60 Milliseconds.\",RequestStatus=\"Success\"" 
| makemv delim=":::" raw 
| mvexpand raw 
| rename raw AS _raw 
| kv

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rex field=TotalTime "^(?<TT_magnitude>\d+)\s*(?<TT_units>.*)$"
| eval TT = TT_magnitude * case(
   TT_units = "Milliseconds", 1/1000,
   TT_units = "Centiseconds", 1/100,
   TT_units = "Seconds", 1,
   true(), 0)
| stats avg(TT) AS avg_TotalTime

rmmiller
Contributor

Solid! My first instinct was to ask about the units and whether they always showed up with the same units. You bulletproofed it! 🙂

0 Karma

rmmiller
Contributor

As a first cut, you can use rex to extract the value of TotalTime from the events.

Something like this should work:

yoursearch
| rex field=_raw \*\*TotalTime\*\*="(?<TotalTime>\d+)\sMilliseconds"
| stats avg(TotalTime)

I recommend looking at the percentile calculations rather than average. Although avg is widely used, average can hide outliers.
In your situation, I would look at the differences between average and the 50th percentile (aka median😞

 yoursearch
 | rex field=_raw \*\*TotalTime\*\*="(?<TotalTime>\d+)\sMilliseconds"
 | stats avg(TotalTime) AS Average, perc50(TotalTime) as Median

If it looks like using Total Time is worthwhile, then move the field extraction of TotalTime to props.conf with the help of your local Splunk admin.

Hope that helps!
rmmiller

Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...