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

Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

Introduction to Splunk AI

How are you using AI in Splunk? Whether you see AI as a threat or opportunity, AI is here to stay. Lucky for ...

Splunk + ThousandEyes: Correlate frontend, app, and network data to troubleshoot ...

Are you tired of troubleshooting delays caused by siloed frontend, application, and network data? We've got a ...

Maximizing the Value of Splunk ES 8.x

Splunk Enterprise Security (ES) continues to be a leader in the Gartner Magic Quadrant, reflecting its pivotal ...