Can you help me create a graph for average calculation of execution time field?

[htomi]

Hi all,

I would like to create a dashboard displaying average transaction time / day / test type.

Tests are running three times a day and each run contains the three different types of tests: declined, remote and remote_auth.

Each test is repeated 100 times, so you end up with 900 rows of execution times every day.

My issue is that Splunk doesn't seems to be able to calculate the average of execution time (field14) field.
Avg(field14) in timechart returns zero values only. Converting the time to seconds didn't work, but if I run the search with latest(field14) then it returns values:

| fields + Testtype, field14, Date
| timechart latest(field14)  by Testtype

The data source is a simple text file with the execution times displayed line by line and formatted as HH:MM:SS.ssssss

Execution time on the 3/6/2019 12:03:16 PM for the declined API test is: 00:00:00.6265450
Execution time on the 3/6/2019 12:03:16 PM for the declined API test is: 00:00:00.5981781
Execution time on the 3/6/2019 12:03:16 PM for the declined API test is: 00:00:00.6123268
Execution time on the 3/6/2019 12:03:16 PM for the declined API test is: 00:00:00.5854582

Splunk can locate all three fields I'm interested in.
Execution time on the 3/6/2019 12:03:16 PM for the declined API test is: 00:00:00.6265450
Date = 3/6/2019 Testtype = declined field14 = 00:00:00.6265450
Execution time on the 3/6/2019 12:03:16 PM for the declined API test is: 00:00:00.5981781
Date = 3/6/2019 Testtype = declined field14 = 00:00:00.5981781
Execution time on the 3/6/2019 12:03:16 PM for the declined API test is: 00:00:00.6123268
Date = 3/6/2019 Testtype = declined field14 = 00:00:00.6123268

Can someone suggest a way of generating a graph showing the average execution time (field14) by test type and by day?

Thanks

[renjith_nair · Accepted solution]

@htomi,

avg works on numeric values. So here you might need to convert your field to a numeric (for e.g. sec) and calculate average on that.

For eg: below should give you an avg of response time in seconds over testtypes

| rex field=field14  "(?<hr>\d{2}):(?<mn>\d{2}):(?<ss>\d{2}).(?<ms>\d+)"
| eval responseTime=(hr*3600+mn*60+ss+ms/1000000)
| stats avg(responseTime)  by Testtype,Date

OR

    | rex field=field14  "(?<hr>\d{2}):(?<mn>\d{2}):(?<ss>\d{2}).(?<ms>\d+)"
    | eval responseTime=(hr*3600+mn*60+ss+ms/1000000)
    | chart avg(responseTime)  over Testtype by Date

Note : assuming that you have microsecond precision. If it's not , you need to adjust the calculation at the last bit accordingly.

[htomi]

The second option didn't but the first one did work: I adjusted the decimal point and removed the hours and minutes which aren't used. Wish I would've known that I should be using regex before all that attempt to convert it to seconds.
This is what it looks like and works as I wanted to

|rex field=field14  "(\d{2}):(\d{2}):(?<ss>\d{2}).(?<ms>\d+)"  
| eval responseTime=(ss+ms/10000000)  
| timechart  span=1d avg(responseTime) by Testtype

Thanks you @renjith.nair !

[renjith_nair]

If you dont need the hour , you could use convert mstime(time) AS ms_time also .

See if this does work for you : https://docs.splunk.com/Documentation/Splunk/7.2.4/SearchReference/Convert#2._Convert_a_time_in_MM:S...