search report query with stats

[kasimbekur]

My below query works fine:

index="jenkins-cicd-*" source="**/test-metrics-summary.json" | rex max_match=0 field=_raw "(?<lineData>[^\n]+)" | mvexpand lineData | spath input=lineData path=env output=singleEnv | spath input=singleEnv | spath input=lineData | eval status=mvindex(status,1)| eval testRunStartTime=mvindex(testRunStartTime,1)| eval testRunEndTime=mvindex(testRunEndTime,1)| eval testFileName=mvindex(testFileName,1)| eval testCaseName=mvindex(testCaseName,1)| eval testCaseId=mvindex(testCaseId,1)| eval TotalTime = strftime(strptime(testRunEndTime , "%Y-%m-%dT%H:%M:%S.%3N") - strptime(testRunStartTime, "%Y-%m-%dT%H:%M:%S.%3N"),  "%Mm %Ss %2Nms")|
   table  status testRunStartTime testRunEndTime testFileName testCaseName testCaseId TotalTime

but if i add stats with avg, i am not getting any values other than avg values in the table

index="jenkins-cicd-*" source="**/ctest-metrics-summary.json" | rex max_match=0 field=_raw "(?<lineData>[^\n]+)" | mvexpand lineData | spath input=lineData path=env output=singleEnv | spath input=singleEnv | spath input=lineData | eval status=mvindex(status,1)| eval testRunStartTime=mvindex(testRunStartTime,1)| eval testRunEndTime=mvindex(testRunEndTime,1)| eval testFileName=mvindex(testFileName,1)| eval testCaseName=mvindex(testCaseName,1)| eval testCaseId=mvindex(testCaseId,1)| eval TotalTime = strftime(strptime(testRunEndTime , "%Y-%m-%dT%H:%M:%S.%3N") - strptime(testRunStartTime, "%Y-%m-%dT%H:%M:%S.%3N"),  "%Mm %Ss %2Nms")| stats avg(TotalTime) by TotalTime |
   table  status testRunStartTime testRunEndTime testFileName testCaseName testCaseId TotalTime

I am getting all fields empty. I want to display average value as AverageTime after filed TotalTime.

[elliotproebstel · Accepted solution]

What you are describing is the expected behavior of the command you provided. Let's look at the stats command:

| stats avg(TotalTime) BY TotalTime

This doesn't really make sense as a command. Roughly translated to English, it says: "For each value of TotalTime, find all other events with the same value for TotalTime, and take the average of TotalTime." So if you have five events that each contain the TotalTime=12, then Splunk will take all five events, sum up 12+12+12+12+12 and divide the total by the number of events (5) and return the average: 12. And so on for every value of TotalTime that Splunk finds. So the command | stats avg(TotalTime) BY TotalTime will always yield two columns: avg(TotalTime) and TotalTime, and they will always have the same value. Follow that up with a table command that includes TotalTime but doesn't include avg(TotalTime), and you'll only have values for TotalTime.

That brings us to the second part of the issue at hand with the stats call: if you don't specify a field in the stats call, it won't pass through that part of the query. So no matter how many fields you had before you called | stats avg(TotalTime) by TotalTime, you will only be left with two fields afterwards: TotalTime and avg(TotalTime). If you remove the table command from the end of your search, you'll see that.

I'm not sure what quite what your intent is with the stats call, but I think you want the average of all TotalTime values as a column. If so, this might get you there:

index="jenkins-cicd-*" source="**/ctest-metrics-summary.json" 
| rex max_match=0 field=_raw "(?<lineData>[^\n]+)" 
| mvexpand lineData 
| spath input=lineData path=env output=singleEnv 
| spath input=singleEnv 
| spath input=lineData 
| eval status=mvindex(status,1)
| eval testRunStartTime=mvindex(testRunStartTime,1)
| eval testRunEndTime=mvindex(testRunEndTime,1)
| eval testFileName=mvindex(testFileName,1)
| eval testCaseName=mvindex(testCaseName,1)
| eval testCaseId=mvindex(testCaseId,1)
| eval TotalTime = strftime(strptime(testRunEndTime , "%Y-%m-%dT%H:%M:%S.%3N") - strptime(testRunStartTime, "%Y-%m-%dT%H:%M:%S.%3N"),  "%Mm %Ss %2Nms")
| eventstats avg(TotalTime) AS AverageTime
| table  status testRunStartTime testRunEndTime testFileName testCaseName testCaseId TotalTime AverageTime

[kasimbekur]

I am new to Splunk reporting.
I am looking for average time taken for a single app( say myapp) to run its test cases.
I run your query, AverageTime gives empty value.

[elliotproebstel]

Is there a field that defines the app? Like, if appName was a field name, it would make sense to do:

| eventstats avg(TotalTime) AS AverageTime BY appName

[kasimbekur]

I have given

| eventstats avg(TotalTime) AS AverageTime BY testFileName

still it gives empty
Is this issue because of TotalTime is in time format?

[elliotproebstel]

Oh, yes. It sure would. Can you copy/paste in the current results - or at least a few rows? I'll help you get that parsed out and fixed.

[kasimbekur]

i have updated above main comment section. I want to find average time to run each application. I hope this can be done by subtracting testRunEndTime - testRunStartTime. Also will be better if I get average of each test cases in an application. i.e. testCaseEndTime - testCaseStartTime

[elliotproebstel]

Ok, I think I'm getting your goal now. How about this:

 index="jenkins-cicd-*" source="**/ctest-metrics-summary.json" 
 | rex max_match=0 field=_raw "(?[^\n]+)" 
 | mvexpand lineData 
 | spath input=lineData path=env output=singleEnv 
 | spath input=singleEnv 
 | spath input=lineData 
 | eval status=mvindex(status,1)
 | eval testRunStartTime=mvindex(testRunStartTime,1)
 | eval testRunEndTime=mvindex(testRunEndTime,1)
 | eval testFileName=mvindex(testFileName,1)
 | eval testCaseName=mvindex(testCaseName,1)
 | eval testCaseId=mvindex(testCaseId,1)
 | eval TotalTime = strptime(testRunEndTime , "%Y-%m-%dT%H:%M:%S.%3N") - strptime(testRunStartTime, "%Y-%m-%dT%H:%M:%S.%3N")
 | eventstats avg(TotalTime) AS AverageTime BY testCaseName
 | fieldformat TotalTime=strftime(TotalTime, "%Mm %Ss %2Nms")
 | table  status testRunStartTime testRunEndTime testFileName testCaseName testCaseId

TotalTime AverageTime
If you'd rather do it by testCaseEndTime instead of testRunEndTime (and likewise for the start times), just substitute the field names in place. The major change I made here is to not use the eval command to change the actual value of the field TotalTime but instead use fieldformat, which changes the way the value is presented but not the way it's used in calculations. I also moved this to after the calculation of the AverageTime, so it actually could be either an eval or a fieldformat - as long as the value of TotalTime is a number at the time when we calculate the average.

[kasimbekur]

fantastic, it worked. +1 for your detailed explanation of each steps and functions used, this really helped to understand the Splunk report concept than just fixing issues.

[elliotproebstel]

Awesome. Glad I could help you understand! That's always my goal. 🙂

[kasimbekur]

**but if i add stats with avg, i am not getting any values other than TotalValues in the table which comes wrong values.