How to fix being unable to print few fields from Dynatrace usersession resultset?

[sabari80]

Following query is printing 'pp_user_action_name','Total_Calls','Avg_User_Action_Response' not getting 'pp_user_action_user' values as its outside of useractions{} array. Not able club values from inner array and outer array. How to fix this?

index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=pp_user_action_user path=userId
| search pp_user_action_user ="xxxx,xxxx"
| spath output=user_actions path="userActions{}"
| stats count by user_actions 
| spath output=pp_user_action_application input=user_actions path=application
| where pp_user_action_application="xxxxx"
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_targetUrl input=user_actions path=targetUrl
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| eval pp_user_action_name=substr(pp_user_action_name,0,150)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "Avg_User_Action_Response" by pp_user_action_name
| eval Avg_User_Action_Response=round(Avg_User_Action_Response,0)
| table pp_user_action_user,pp_user_action_name,Total_Calls,Avg_User_Action_Response
| sort -Total_Calls

PFB sample event. 

[-]
applicationType: WEB_APPLICATION
bounce: false
browserFamily: MicrosoftEdge
browserMajorVersion: MicrosoftEdge108
browserType: DesktopBrowser
clientType: DesktopBrowser
connectionType: UNKNOWN
dateProperties: [ [+]
]
displayResolution: FHD
doubleProperties: [ [+]
]
duration: 279730
endReason: TIMEOUT
endTime: 1676486021319
errors: [ [+]
]
events: [ [+]
]
hasError: true
hasSessionReplay: false
internalUserId: xxxxx
ip: xxxxx
longProperties: [ [+]
]
matchingConversionGoals: [ [+]
]
matchingConversionGoalsCount: 0
newUser: true
numberOfRageClicks: 0
numberOfRageTaps: 0
osFamily: Windows
osVersion: Windows10
partNumber: 0
screenHeight: 1080
screenOrientation: LANDSCAPE
screenWidth: 1920
startTime: 1676485741589
stringProperties: [ [+]
]
syntheticEvents: [ [+]
]
tenantId: xxxx
totalErrorCount: 3
totalLicenseCreditCount: 1
userActionCount: 12
userActions: [ [-]
{ [-]
apdexCategory: FRUSTRATED
application: xxxx
cdnBusyTime: null
cdnResources: 0
cumulativeLayoutShift: null
customErrorCount: 0
dateProperties: [ [+]
]
documentInteractiveTime: null
domCompleteTime: null
domContentLoadedTime: null
domain: xxxxx
doubleProperties: [ [+]
]
duration: 16292
endTime: 1676485757881
firstInputDelay: null
firstPartyBusyTime: 15012
firstPartyResources: 2
frontendTime: 1289
internalApplicationId: xxxxx
javascriptErrorCount: 0
keyUserAction: false
largestContentfulPaint: null
loadEventEnd: null
loadEventStart: null
longProperties: [ [+]
]
matchingConversionGoals: [ [+]
]
name: clickontasknamexxxxx
navigationStart: 1676485742474
networkTime: 1881
requestErrorCount: 0
requestStart: 1175
responseEnd: 15003
responseStart: 14297
serverTime: 13122
speedIndex: 16292
startTime: 1676485741589
stringProperties: [ [+]
]
targetUrl: xxxx
thirdPartyBusyTime: null
thirdPartyResources: 0
totalBlockingTime: null
type: Xhr
userActionPropertyCount: 0
visuallyCompleteTime: 16292
}
{ [+]
}
{ [+]
}
{ [+]
}
{ [+]
}
{ [+]
}
{ [+]
}
{ [+]
}
{ [+]
}
{ [+]
}
{ [+]
}
{ [+]
}
]
userExperienceScore: TOLERATED
userId: xxxxx,xxxx
userSessionId: xxxxx
userType: REAL_USER
}

[ITWhisperer]

Please provide an unformatted version of the event preferably in a code block, e.g. 

{
  "applicationType": "WEB_APPLICATION",
...

[sabari80]

{"applicationType":"WEB_APPLICATION","screenWidth":1920,"numberOfRageTaps":0,"userExperienceScore":"TOLERATED","bounce":false,"browserFamily":"MicrosoftEdge","hasSessionReplay":false,"totalErrorCount":3,"stringProperties":[],"browserMajorVersion":"MicrosoftEdge108","doubleProperties":[],"userSessionId":"xxxxxx","totalLicenseCreditCount":1,"connectionType":"UNKNOWN","browserType":"DesktopBrowser","duration":279730,"matchingConversionGoals":[],"clientType":"DesktopBrowser","osVersion":"Windows10","screenOrientation":"LANDSCAPE","longProperties":[],"userActionCount":12,"syntheticEvents":[],"startTime":1676485741589,"matchingConversionGoalsCount":0,"events":[{"application":"xxxx","name":"xxxx,xxxx","internalApplicationId":"APPLICATION-xxxxx","startTime":1676485757881,"type":"UserTagFromMetaData"},{"application":"xxxx","name":"xxxxx","internalApplicationId":"APPLICATION-xxxx","startTime":1676485845708,"type":"UserTagFromMetaData"},{"application":"xxxx","name":"xxxxx","internalApplicationId":"APPLICATION-xxxx","startTime":1676485922111,"type":"UserTagFromMetaData"},{"application":"xxxx","name":"xxxxx","internalApplicationId":"APPLICATION-xxxx","startTime":1676485969606,"type":"UserTagFromMetaData"},{"pageReferrerGroup":"xxxx","application":"xxxx","name":"Pagechange","internalApplicationId":"APPLICATION-xxxxx","startTime":1676485991510,"page":"xxxxx","pageGroup":"xxxxx","pageReferrer":"xxxxx","type":"PageChange"},{"application":"xxxx","name":"Pagechange","internalApplicationId":"APPLICATION-xxxx","startTime":1676485993848,"page":"xxxx","pageGroup":"xxxx","type":"PageChange"},{"pageReferrerGroup":"xxxx","application":"xxxx","name":"Pagechange","internalApplicationId":"APPLICATION-xxx","startTime":1676486002835,"page":"xxxx","pageGroup":"xxxx","pageReferrer":"xxxx","type":"PageChange"},{"pageReferrerGroup":"xxxx","application":"xxxx","name":"Pagechange","internalApplicationId":"APPLICATION-xxxx","startTime":1676486012374,"page":"xxxx","pageGroup":"xxxx","pageReferrer":"xxxxx","type":"PageChange"},{"application":"xxxx","name":"Pagechange","internalApplicationId":"APPLICATION-xxxx","startTime":1676486014249,"page":"xxxx","pageGroup":"xxxx","type":"PageChange"}],"dateProperties":[],"osFamily":"Windows","endReason":"TIMEOUT","ip":"10.160.8.138","screenHeight":1080,"displayResolution":"FHD","numberOfRageClicks":0,"userId":"xxx,xxx","userActions":[{"javascriptErrorCount":0,"cumulativeLayoutShift":null,"firstPartyBusyTime":15012,"responseEnd":15003,"visuallyCompleteTime":16292,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":null,"doubleProperties":[],"type":"Xhr","domCompleteTime":null,"networkTime":1881,"duration":16292,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":1175,"longProperties":[],"firstPartyResources":2,"totalBlockingTime":null,"startTime":1676485741589,"serverTime":13122,"customErrorCount":0,"documentInteractiveTime":null,"dateProperties":[],"keyUserAction":false,"frontendTime":1289,"internalApplicationId":"APPLICATION-xxxx","responseStart":14297,"largestContentfulPaint":null,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":null,"navigationStart":1676485742474,"domain":"xxxx,"name":"clickontasknamexxxx","apdexCategory":"FRUSTRATED","userActionPropertyCount":0,"endTime":1676485757881,"speedIndex":16292,"domContentLoadedTime":null,"targetUrl":"xxxx","loadEventEnd":null},{"javascriptErrorCount":0,"cumulativeLayoutShift":null,"firstPartyBusyTime":2795,"responseEnd":2790,"visuallyCompleteTime":10531,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":null,"doubleProperties":[],"type":"Xhr","domCompleteTime":null,"networkTime":737,"duration":10531,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":8,"longProperties":[],"firstPartyResources":2,"totalBlockingTime":null,"startTime":1676485765543,"serverTime":2053,"customErrorCount":0,"documentInteractiveTime":null,"dateProperties":[],"keyUserAction":false,"frontendTime":7741,"internalApplicationId":"APPLICATION-xxxx","responseStart":2061,"largestContentfulPaint":null,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":null,"navigationStart":1676485765566,"domain":"xxxx","name":"clickonsaveonpagexxxx","apdexCategory":"TOLERATING","userActionPropertyCount":0,"endTime":1676485776074,"speedIndex":10531,"domContentLoadedTime":null,"targetUrl":"xxxx","loadEventEnd":null},{"javascriptErrorCount":0,"cumulativeLayoutShift":null,"firstPartyBusyTime":2120,"responseEnd":2117,"visuallyCompleteTime":19685,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":null,"doubleProperties":[],"type":"Xhr","domCompleteTime":null,"networkTime":517,"duration":19685,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":6,"longProperties":[],"firstPartyResources":2,"totalBlockingTime":null,"startTime":1676485784191,"serverTime":1600,"customErrorCount":0,"documentInteractiveTime":null,"dateProperties":[],"keyUserAction":false,"frontendTime":17568,"internalApplicationId":"APPLICATION-xxxx","responseStart":1606,"largestContentfulPaint":null,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":null,"navigationStart":1676485784204,"domain":"xxxx","name":"clickonsaveonpagexxxx","apdexCategory":"FRUSTRATED","userActionPropertyCount":0,"endTime":1676485803876,"speedIndex":19685,"domContentLoadedTime":null,"targetUrl":"xxxx","loadEventEnd":null},{"javascriptErrorCount":0,"cumulativeLayoutShift":null,"firstPartyBusyTime":2914,"responseEnd":2911,"visuallyCompleteTime":10436,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":null,"doubleProperties":[],"type":"Xhr","domCompleteTime":null,"networkTime":798,"duration":10436,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":177,"longProperties":[],"firstPartyResources":2,"totalBlockingTime":null,"startTime":1676485814095,"serverTime":2113,"customErrorCount":0,"documentInteractiveTime":null,"dateProperties":[],"keyUserAction":false,"frontendTime":7525,"internalApplicationId":"APPLICATION-xxxx","responseStart":2290,"largestContentfulPaint":null,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":null,"navigationStart":1676485814120,"domain":"xxxxx","name":"clickonsaveonpagexxxx","apdexCategory":"TOLERATING","userActionPropertyCount":0,"endTime":1676485824531,"speedIndex":10436,"domContentLoadedTime":null,"targetUrl":"xxxxx","loadEventEnd":null},{"javascriptErrorCount":0,"cumulativeLayoutShift":null,"firstPartyBusyTime":2785,"responseEnd":2783,"visuallyCompleteTime":2843,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":null,"doubleProperties":[],"type":"Xhr","domCompleteTime":null,"networkTime":717,"duration":2843,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":281,"longProperties":[],"firstPartyResources":2,"totalBlockingTime":null,"startTime":1676485842865,"serverTime":2066,"customErrorCount":0,"documentInteractiveTime":null,"dateProperties":[],"keyUserAction":false,"frontendTime":60,"internalApplicationId":"APPLICATION-xxxx","responseStart":2347,"largestContentfulPaint":null,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":null,"navigationStart":1676485842882,"domain":"xxxx","name":"clickonsaveonpagexxxx","apdexCategory":"SATISFIED","userActionPropertyCount":0,"endTime":1676485845708,"speedIndex":2843,"domContentLoadedTime":null,"targetUrl":"xxxx","loadEventEnd":null},{"javascriptErrorCount":0,"cumulativeLayoutShift":null,"firstPartyBusyTime":66020,"responseEnd":66017,"visuallyCompleteTime":68092,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":null,"doubleProperties":[],"type":"Xhr","domCompleteTime":null,"networkTime":20295,"duration":68092,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":3,"longProperties":[],"firstPartyResources":2,"totalBlockingTime":null,"startTime":1676485854019,"serverTime":45722,"customErrorCount":0,"documentInteractiveTime":null,"dateProperties":[],"keyUserAction":false,"frontendTime":2075,"internalApplicationId":"APPLICATION-xxxx","responseStart":45725,"largestContentfulPaint":null,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":null,"navigationStart":1676485854027,"domain":"xxxx","name":"clickonclearfilteronpagexxxx","apdexCategory":"FRUSTRATED","userActionPropertyCount":0,"endTime":1676485922111,"speedIndex":68092,"domContentLoadedTime":null,"targetUrl":"xxxx","loadEventEnd":null},{"javascriptErrorCount":0,"cumulativeLayoutShift":null,"firstPartyBusyTime":8443,"responseEnd":8437,"visuallyCompleteTime":9517,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":null,"doubleProperties":[],"type":"Xhr","domCompleteTime":null,"networkTime":586,"duration":9517,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":293,"longProperties":[],"firstPartyResources":2,"totalBlockingTime":null,"startTime":1676485960089,"serverTime":7851,"customErrorCount":0,"documentInteractiveTime":null,"dateProperties":[],"keyUserAction":false,"frontendTime":1080,"internalApplicationId":"APPLICATION-xxxx","responseStart":8144,"largestContentfulPaint":null,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":null,"navigationStart":1676485960458,"domain":"xxxx","name":"clickonfiltertasksonpagexxxx","apdexCategory":"SATISFIED","userActionPropertyCount":0,"endTime":1676485969606,"speedIndex":9517,"domContentLoadedTime":null,"targetUrl":"xxxx","loadEventEnd":null},{"javascriptErrorCount":0,"cumulativeLayoutShift":null,"firstPartyBusyTime":2013,"responseEnd":2010,"visuallyCompleteTime":2060,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":null,"doubleProperties":[],"type":"Xhr","domCompleteTime":null,"networkTime":441,"duration":2060,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":152,"longProperties":[],"firstPartyResources":2,"totalBlockingTime":null,"startTime":1676485983357,"serverTime":1569,"customErrorCount":0,"documentInteractiveTime":null,"dateProperties":[],"keyUserAction":false,"frontendTime":50,"internalApplicationId":"APPLICATION-xxxx","responseStart":1721,"largestContentfulPaint":null,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":null,"navigationStart":1676485983370,"domain":"xxxx","name":"clickonsaveonpagexxxx","apdexCategory":"SATISFIED","userActionPropertyCount":0,"endTime":1676485985417,"speedIndex":2060,"domContentLoadedTime":null,"targetUrl":"xxxx","loadEventEnd":null},{"javascriptErrorCount":0,"cumulativeLayoutShift":0.0185,"firstPartyBusyTime":2675,"responseEnd":2211,"visuallyCompleteTime":6702,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":1,"doubleProperties":[],"type":"Load","domCompleteTime":4841,"networkTime":253,"duration":6764,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":96,"longProperties":[],"firstPartyResources":58,"totalBlockingTime":null,"startTime":1676485989587,"serverTime":1958,"customErrorCount":0,"documentInteractiveTime":2369,"dateProperties":[],"keyUserAction":false,"frontendTime":4553,"internalApplicationId":"APPLICATION-xxxx","responseStart":2054,"largestContentfulPaint":2280,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":4844,"navigationStart":1676485991507,"domain":"xxxx","name":"clickon36310650onpagexxxx","apdexCategory":"SATISFIED","userActionPropertyCount":0,"endTime":1676485996351,"speedIndex":6630,"domContentLoadedTime":2462,"targetUrl":"xxxx","loadEventEnd":4844},{"javascriptErrorCount":1,"cumulativeLayoutShift":0.0578,"firstPartyBusyTime":1246,"responseEnd":3058,"visuallyCompleteTime":3910,"requestErrorCount":1,"stringProperties":[],"firstInputDelay":1,"doubleProperties":[],"type":"Load","domCompleteTime":4025,"networkTime":448,"duration":4035,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":17,"longProperties":[],"firstPartyResources":53,"totalBlockingTime":null,"startTime":1676486002830,"serverTime":2610,"customErrorCount":0,"documentInteractiveTime":3941,"dateProperties":[],"keyUserAction":false,"frontendTime":977,"internalApplicationId":"APPLICATION-xxxx","responseStart":2627,"largestContentfulPaint":3925,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":4029,"navigationStart":1676486002835,"domain":"xxxx","name":"clickon36310650onpagexxxx","apdexCategory":"SATISFIED","userActionPropertyCount":0,"endTime":1676486006865,"speedIndex":3556,"domContentLoadedTime":3993,"targetUrl":"xxxx","loadEventEnd":4030},{"javascriptErrorCount":1,"cumulativeLayoutShift":0.0208,"firstPartyBusyTime":104,"responseEnd":1840,"visuallyCompleteTime":2715,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":6,"doubleProperties":[],"type":"Load","domCompleteTime":2801,"networkTime":401,"duration":2811,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":14,"longProperties":[],"firstPartyResources":64,"totalBlockingTime":null,"startTime":1676486012367,"serverTime":1439,"customErrorCount":0,"documentInteractiveTime":1886,"dateProperties":[],"keyUserAction":true,"frontendTime":971,"internalApplicationId":"APPLICATION-xxxx","responseStart":1453,"largestContentfulPaint":1714,"cdnResources":0,"cdnBusyTime":null,"internalKeyUserActionId":"APPLICATION_METHOD-588922C3DDCEED8A","application":"xxxx","loadEventStart":2804,"navigationStart":1676486012374,"domain":"xxxx","name":"clickonxxxx","apdexCategory":"SATISFIED","userActionPropertyCount":0,"endTime":1676486015178,"speedIndex":2562,"domContentLoadedTime":1934,"targetUrl":"xxxx","loadEventEnd":2804},{"javascriptErrorCount":0,"cumulativeLayoutShift":null,"firstPartyBusyTime":2028,"responseEnd":984,"visuallyCompleteTime":null,"requestErrorCount":0,"stringProperties":[],"firstInputDelay":null,"doubleProperties":[],"type":"Xhr","domCompleteTime":null,"networkTime":247,"duration":1001,"matchingConversionGoals":[],"thirdPartyResources":0,"thirdPartyBusyTime":null,"requestStart":4,"longProperties":[],"firstPartyResources":2,"totalBlockingTime":null,"startTime":1676486020318,"serverTime":737,"customErrorCount":0,"documentInteractiveTime":null,"dateProperties":[],"keyUserAction":false,"frontendTime":17,"internalApplicationId":"APPLICATION-xxxx","responseStart":741,"largestContentfulPaint":null,"cdnResources":0,"cdnBusyTime":null,"application":"xxxx","loadEventStart":null,"navigationStart":1676486020332,"domain":"xxxx","name":"clickonsaveonpagexxxx&priority=0&categoryid=0&parentpageurl=xxxxx","apdexCategory":"SATISFIED","userActionPropertyCount":0,"endTime":1676486021319,"speedIndex":null,"domContentLoadedTime":null,"targetUrl":"xxxx&Priority=0&CategoryID=0&ParentPageURL=xxxx","loadEventEnd":null}],"newUser":true,"tenantId":"3905aa6f-4130-439f-b336-dd2af9fa40d4","partNumber":0,"endTime":1676486021319,"userType":"REAL_USER","hasError":true,"internalUserId":"xxxx","errors":[]}

[ITWhisperer]

Apart from the missing double quote which makes your pasted (and edited) JSON invalid, and the searches for name which your substituted for slightly different strings, the main problems are you using

| stats count by user_actions

to expand user_actions - either use

| mvexpand user_actions 

or

| stats count by pp_user_action_user user_actions 

to preserve pp_user_action_user 

Similarly, the other stats command is forgetting pp_user_action_user - try this instead

| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "Avg_User_Action_Response" by pp_user_action_user pp_user_action_name