Splunk Dynamic query

[vidya321]

Hi,

I am new to splunk.We have recently integrated New relic(monitoring tool) to splunk.we are currently creating splunk dashboard for new relic to monitor the application performance.As a part of this,we have the fields like custom.step1,custom.step1status,custom.step1duration,custom.step2,custom.step2status,custom.step2duration ans so on for n number of steps in a row format.below is the data we see in the events -

events: [ [-]
{ [+]
}
{ [+]
}
{ [-]
custom.JAZZ_INSTANCE: xx-xxx-06-p
custom.Step1: APP_LandingPage
custom.Step1Duration: 205
custom.Step1Status: Pass
custom.Step2: APP_Login
custom.Step2Duration: 1430
custom.Step2Status: Pass
custom.Step3: APP_Dashboard
custom.Step3Duration: 6704
custom.Step3Status: Pass
custom.Step4: APP_WorkitemQuery
custom.Step4Duration: 3349
custom.Step4Status: Pass
custom.errMsg: null
custom.errorStep: null
custom.testCase: null
custom.testCaseStatus: null
duration: 15888.00099992752
error:
host: null
locationLabel: china_bcn
monitorName: APP_CHINA
result: SUCCESS
timestamp: 1623073512664
}

So,to calculate the average of duration of all the steps1,2,3,4.we have written the below query.

index="new_relic_test_index"
| stats avg(results{}.events{}.custom.Step1Duration) as step1duration avg(results{}.events{}.custom.Step2Duration) as step2duration avg(results{}.events{}.custom.Step3Duration) as step3duration avg(results{}.events{}.custom.Step4Duration) as step4duration by results{}.events{}.custom.Step1 results{}.events{}.custom.Step2 results{}.events{}.custom.Step3 results{}.events{}.custom.Step4
| eval splithere='results{}.events{}.custom.Step1' ."|". step1duration .";" . 'results{}.events{}.custom.Step2' . "|" . step2duration .";" . 'results{}.events{}.custom.Step3' . "|" . step3duration .";" . 'results{}.events{}.custom.Step4' . "|" . step4duration
| makemv splithere delim=";"
| mvexpand splithere
| eval step=mvindex(split(splithere,"|"),0), Average(duration)=mvindex(split(splithere,"|"),1)
| table step,Average(duration) | dedup step

Output -
step                                              Average(duration)
APP_LandingPage                205.83333333333334
APP_Login                               1033.986111111111
APP_Dashboard                   4040.1388888888887
APP_WorkitemQuery         1724.7916666666667

But the issue we are facing here is ...we need a dynamic query which will be the same even in case if we have more number of steps and need not edit the query all the time.Also this query is taking long to load.

Please let us know if the dynamic query is possible in this case.

Thanks in Advance!

[ITWhisperer]

The first part creates some dummy data.

Expand out the events from the JSON,  delimit the step number part of the names, and extract the fields, then join the name and duration, put these in a mv field and expand, finally, split the name and duration and perform stats

| makeresults
| eval _raw="{\"events\": [ 
{
\"custom.JAZZ_INSTANCE\": \"xx-xxx-06-p\",
\"custom.Step1\": \"APP_LandingPage\",
\"custom.Step1Duration\": 205,
\"custom.Step1Status\": \"Pass\",
\"custom.Step2\": \"APP_Login\",
\"custom.Step2Duration\": 1430,
\"custom.Step2Status\": \"Pass\",
\"custom.Step3\": \"APP_Dashboard\",
\"custom.Step3Duration\": 6704,
\"custom.Step3Status\": \"Pass\",
\"custom.Step4\": \"APP_WorkitemQuery\",
\"custom.Step4Duration\": 3349,
\"custom.Step4Status\": \"Pass\",
\"custom.errMsg\": null,
\"custom.errorStep\": null,
\"custom.testCase\": null,
\"custom.testCaseStatus\": null,
\"duration\": 15888.00099992752,
\"error\": \"\",
\"host\": null,
\"locationLabel\": \"china_bcn\",
\"monitorName\": \"APP_CHINA\",
\"result\": \"SUCCESS\",
\"timestamp\": 1623073512664
},{
\"custom.JAZZ_INSTANCE\": \"xx-xxx-06-p\",
\"custom.Step1\": \"APP_LandingPage\",
\"custom.Step1Duration\": 305,
\"custom.Step1Status\": \"Pass\",
\"custom.Step2\": \"APP_Login\",
\"custom.Step2Duration\": 2430,
\"custom.Step2Status\": \"Pass\",
\"custom.Step3\": \"APP_Dashboard\",
\"custom.Step3Duration\": 7704,
\"custom.Step3Status\": \"Pass\",
\"custom.Step4\": \"APP_WorkitemQuery\",
\"custom.Step4Duration\": 4349,
\"custom.Step4Status\": \"Pass\",
\"custom.errMsg\": null,
\"custom.errorStep\": null,
\"custom.testCase\": null,
\"custom.testCaseStatus\": null,
\"duration\": 25888.00099992752,
\"error\": \"\",
\"host\": null,
\"locationLabel\": \"china_bcn\",
\"monitorName\": \"APP_CHINA\",
\"result\": \"SUCCESS\",
\"timestamp\": 1623073512664
}
]}"


| spath path=events{} output=events
| mvexpand events
| rex field=events mode=sed max_match=0 "s/custom\.Step(?<step>\d+)/custom.Step\1./g"
| spath input=events
| foreach custom.Step*.*
    [eval custom.Step<<MATCHSEG1>>.=if("<<MATCHSEG2>>"="Duration",'custom.Step<<MATCHSEG1>>.'.":".'<<FIELD>>','custom.Step<<MATCHSEG1>>.')]
| foreach custom.Step*.
    [eval steps=if(isnull(steps),'<<FIELD>>',mvappend(steps,'<<FIELD>>'))]
| table steps
| mvexpand steps
| eval steps=split(steps,":")
| eval step=mvindex(steps,0), duration=mvindex(steps,1)
| stats avg(duration) as avg_duration by step