Help with search for average response time based on TotalTime value

[dhirajnangar]

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?

[woodcock]

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]

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

[rmmiller]

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