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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...