getting below error
ommand.mvexpand: output will be truncated at 3200 results due to excessive memory usage. Memory threshold of 500MB as configured in limits.conf / [mvexpand] / max_mem_usage_mb has been reached.
this is the query i am running
index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| mvexpand user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls
how i can optimize this search and resolve the mvexpand limit issue?
As you just need name and visuallyCompleteTime, you should only spath out those two fields, combine them, filter them for the ones required, expand them, then filter again and finally do the calcs.
That removes ALL unnecessary fields from the expansion, as you would have been expanding the huge _raw as well as a huge user_actions.
This assumes a 1:1 relationship with name and visuallyCompleteTime fields
index="dynatrace" sourcetype="dynatrace:usersession"
``` SPATH out names and visuallyComplete fields ```
| spath output=names path="userActions{}.name"
| spath output=complete path="userActions{}.visuallyCompleteTime"
``` Filter events that contain this name --SOMEWHERE-- ```
| search names="*newintakeprocess.aspx*"
``` combine the two fields ```
| eval data=mvzip(names, complete, "#!#!#!")
``` and remove ALL others ```
| fields data
| fields - _raw
``` Now EXPAND the data and separate out the fields again ```
| mvexpand data
| eval data=split(data, "#!#!#!")
| eval pp_user_action_name=mvindex(data, 0), pp_user_action_response=mvindex(data, 1)
``` and search again to find the real ones with the filter ```
| search pp_user_action_name="*newintakeprocess.aspx*"
| fields - data
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls
As you just need name and visuallyCompleteTime, you should only spath out those two fields, combine them, filter them for the ones required, expand them, then filter again and finally do the calcs.
That removes ALL unnecessary fields from the expansion, as you would have been expanding the huge _raw as well as a huge user_actions.
This assumes a 1:1 relationship with name and visuallyCompleteTime fields
index="dynatrace" sourcetype="dynatrace:usersession"
``` SPATH out names and visuallyComplete fields ```
| spath output=names path="userActions{}.name"
| spath output=complete path="userActions{}.visuallyCompleteTime"
``` Filter events that contain this name --SOMEWHERE-- ```
| search names="*newintakeprocess.aspx*"
``` combine the two fields ```
| eval data=mvzip(names, complete, "#!#!#!")
``` and remove ALL others ```
| fields data
| fields - _raw
``` Now EXPAND the data and separate out the fields again ```
| mvexpand data
| eval data=split(data, "#!#!#!")
| eval pp_user_action_name=mvindex(data, 0), pp_user_action_response=mvindex(data, 1)
``` and search again to find the real ones with the filter ```
| search pp_user_action_name="*newintakeprocess.aspx*"
| fields - data
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls
@bowesmana
Your suggested solution solved memory issue. Thank you!!
this is working as expected and faster than the other queries. Thanks 🙂
Can you please try this?
index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| stats count by user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls
Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.
its retrieving all the expected results set for yesterday (306098) without any error/exception. It took long time to retrieve the result set. How i can optimize this search?
Can you please share some valid sample events So we can help you further?
meanwhile, you can try this as well.
index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| stats count by user_actions
| spath output=pp_user_action_name input=user_actions path=name
| where pp_user_action_name like "%newintakeprocess.aspx%"
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls
KV
for one day, this search is taking more than 120 seconds.
Here is the sample event
Could you expand the userAction field?
userActions: [ [-]
{ [-]
apdexCategory: SATISFIED
application: *****
cdnBusyTime: 1432
cdnResources: 0
cumulativeLayoutShift: null
customErrorCount: 0
dateProperties: [ [+]
]
documentInteractiveTime: 966
domCompleteTime: 1158
domContentLoadedTime: 1158
domain: ******
doubleProperties: [ [+]
]
duration: 3046
endTime: 1667916591573
firstInputDelay: 45
firstPartyBusyTime: 1923
firstPartyResources: 28
frontendTime: null
internalApplicationId: APPLICATION-4B82F442A55D1200
internalKeyUserActionId: APPLICATION_METHOD-DD251367105EAA43
javascriptErrorCount: 0
keyUserAction: true
largestContentfulPaint: null
loadEventEnd: 1159
loadEventStart: 1159
longProperties: [ [+]
]
matchingConversionGoals: [ [+]
]
name: loadingofpage/customer/
navigationStart: 1667916588527
networkTime: null
requestErrorCount: 0
requestStart: null
responseEnd: 582
responseStart: null
serverTime: null
speedIndex: 1102
startTime: 1667916588527
stringProperties: [ [+]
]
targetUrl: *****
thirdPartyBusyTime: 1432
thirdPartyResources: 31
totalBlockingTime: null
type: Load
userActionPropertyCount: 0
visuallyCompleteTime: 2365
}
{ [+]
}
{ [+]
}
Remove every single possible field you won't need before you mvexpand anything. Looks like you're only using user_actions field, so
| fields user_actions
before you mvexpand
You could probably also filter out the names you are looking for before you expand, so only searching for events that have *newintakeprocess.aspx* (although that's not an efficient search with leading wildcards). You could include this as a search parameter on the first line against _raw or after the spath against user_actions
if i am adding anything in the first search line, the output records got reduced. I am not getting the entire result set
index="dynatrace" sourcetype="dynatrace:usersession" "userActions{}.name"="*newintakeprocess.aspx*"
| spath output=user_actions path="userActions{}"
| mvexpand user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls
-- Retrieved 806 records for today -- no mvexpand error though
index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| mvexpand user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls
-- 8661 events for today with mvexpand error
and with fields statement?
with fields statement also getting same error, but result set got increased little bit
index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| fields user_actions
| mvexpand user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls
-- 9568 events for today -- with mvexpand error
after the spath, add the search
| search user_actions{}.name="*newintakeprocess.aspx*"
However, how many events would you expect to get - is the 800 in the right range or 8000? Your 8000 count is ALL the events without the filter, so will include events where the name is not newinstakeprocess.aspx
If you search in a smaller window, the search filter would restrict the count so you should get a smaller number anyway. Validate that first so you are not finding the memory error
No records retrieved after adding search command
index="dynatrace" sourcetype="dynatrace:usersession"
| spath output=user_actions path="userActions{}"
| search user_actions{}.name="*newintakeprocess.aspx*"
| fields user_actions
| mvexpand user_actions
| spath output=pp_user_action_name input=user_actions path=name
| spath output=pp_user_action_response input=user_actions path=visuallyCompleteTime
| where pp_user_action_name like "%newintakeprocess.aspx%"
| eval pp_user_action_name=substr(pp_user_action_name,0,40)
| stats count(pp_user_action_response) As "Total_Calls" ,avg(pp_user_action_response) AS "User_Action_Response" by pp_user_action_name
| eval User_Action_Response=round(User_Action_Response,0)
| sort -Total_Calls
8000+ records are the correct result set.