topic Re: Average of Seconds and Milliseconds using extracted values in Splunk Search

Average of Seconds and Milliseconds using extracted values

tjsnow — Thu, 04 Feb 2021 19:28:26 GMT

I am trying to put together and average duration (calculated and logged by product) as well as count. however the logs show an "s" or "ms" at the end of the value to reflect how long processing took. I need to convert the results into an average duration but have been unable to figure it out. In this example is shows ms, but could be seconds on the next records and the value end in s

Example Record:

request.status='completed'; status.message=''; request.start='2021-01-29 15:50:25.402471006 +0000 UTC m=+501.139572300'; request.end='2021-01-29 15:50:26.193830852 +0000 UTC m=+501.930932145'; request.duration='791.359845ms'"

Here is my current query (note requestduration is an extracted field resulting in the value of request.duration as 791.359845ms in this case) :

| stats count(requestduration) as count avg(requestduration) as Average by source

I get that I cannot average the values with the alpha characters in there, but I don't know how to convert the seconds into milis second then remove the character and average them.. Any help would be appreciated!

Re: Average of Seconds and Milliseconds using extracted values

scelikok — Thu, 04 Feb 2021 19:53:56 GMT

Hi @tjsnow,

Please try below;

| eval requestduration=if(match(requestduration,"ms"),tonumber(replace(requestduration,"ms",""))*1000,requestduration) | stats count(requestduration) as count avg(requestduration) as Average by source

Re: Average of Seconds and Milliseconds using extracted values

tjsnow — Fri, 05 Feb 2021 16:14:05 GMT

@scelikok Thank you this got me on the right path... There were a couple changes though, so I will post them incase anyone else runs into something similar...

To go from Milliseconds to Seconds we need to divide not multiply, and in this case I still had to strip the S out of the records that were displayed in seconds, because the average wouldn't work if half the records had an alpha character in them. So I added a second if statement with no calculation. Here is the final solution. Thanks @scelikok

| eval requestduration=if(match(requestduration,"ms"),tonumber(replace(requestduration,"ms",""))/1000,requestduration) | eval requestduration=if(match(requestduration,"s"),tonumber(replace(requestduration,"s","")),requestduration) | stats count(requestduration) as count avg(requestduration) as "Average Seconds" by source

Re: Average of Seconds and Milliseconds using extracted values

scelikok — Fri, 05 Feb 2021 18:21:03 GMT

Thank you @tjsnow,

I confused the conversion missed the "s" option. I am glad you found right way..