Splunk Search

Getting Error - [mvexpand] / max_mem_usage_mb has been reached

sabari80
Explorer

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?

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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

 

LearningGuy
Builder

@bowesmana 
Your suggested solution solved memory issue. Thank you!! 

0 Karma

sabari80
Explorer

this is working as expected and faster than the other queries. Thanks 🙂

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@sabari80 

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.

0 Karma

sabari80
Explorer

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?

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@sabari80 

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

0 Karma

sabari80
Explorer

for one day, this search is taking more than 120 seconds.

Here is the sample event 

{ [-]
   applicationTypeWEB_APPLICATION
   bouncefalse
   browserFamilyMicrosoftEdge
   browserMajorVersionMicrosoftEdge105
   browserTypeDesktopBrowser
   clientTypeDesktopBrowser
   connectionTypeUNKNOWN
   dateProperties: [ [+]
   ]

   displayResolutionHD
   doubleProperties: [ [+]
   ]

   duration2262208
   endReasonUSER_ACTION_LIMIT
   endTime1667916283243
   errors: [ [+]
   ]

   events: [ [+]
   ]

   hasErrortrue
   hasSessionReplayfalse
   internalUserId1667914023413LF1NFVLVTMIJ7EG3T3JKOTBVQAD45EU4
   ip10.147.53.109
   longProperties: [ [+]
   ]

   matchingConversionGoals: [ [+]
   ]

   matchingConversionGoalsCount0
   newUsertrue
   numberOfRageClicks0
   numberOfRageTaps0
   osFamilyWindows
   osVersionWindows10
   partNumber0
   screenHeight720
   screenOrientationLANDSCAPE
   screenWidth1280
   startTime1667914021035
   stringProperties: [ [+]
   ]

   syntheticEvents: [ [+]
   ]

   tenantId3905aa6f-4130-439f-b336-dd2af9fa40d4
   totalErrorCount55
   totalLicenseCreditCount1
   userActionCount113
   userActions: [ [+]
   ]

   userExperienceScoreTOLERATED
   userIdMCCANTS,MICHAEL
   userSessionIdRKTHQWBCUQHKDTTVLPOPQURUKUFAPCCB-0
   userTypeREAL_USER

}
 

Show as raw text

 

Tags (1)
0 Karma

johnhuang
Motivator

Could you expand the userAction field?

0 Karma

sabari80
Explorer

userActions[ [-]
     { [-]
       apdexCategorySATISFIED
       application*****
       cdnBusyTime1432
       cdnResources0
       cumulativeLayoutShiftnull
       customErrorCount0
       dateProperties: [ [+]
       ]

       documentInteractiveTime966
       domCompleteTime1158
       domContentLoadedTime1158
       domain******
       doubleProperties: [ [+]
       ]

       duration3046
       endTime1667916591573
       firstInputDelay45
       firstPartyBusyTime1923
       firstPartyResources28
       frontendTimenull
       internalApplicationIdAPPLICATION-4B82F442A55D1200
       internalKeyUserActionIdAPPLICATION_METHOD-DD251367105EAA43
       javascriptErrorCount0
       keyUserActiontrue
       largestContentfulPaintnull
       loadEventEnd1159
       loadEventStart1159
       longProperties: [ [+]
       ]

       matchingConversionGoals: [ [+]
       ]

       nameloadingofpage/customer/
       navigationStart1667916588527
       networkTimenull
       requestErrorCount0
       requestStartnull
       responseEnd582
       responseStartnull
       serverTimenull
       speedIndex1102
       startTime1667916588527
       stringProperties: [ [+]
       ]

       targetUrl*****
       thirdPartyBusyTime1432
       thirdPartyResources31
       totalBlockingTimenull
       typeLoad
       userActionPropertyCount0
       visuallyCompleteTime2365
     }
     { [+]
     }
     { [+]
     }

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

 

0 Karma

sabari80
Explorer

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 

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

and with fields statement?

0 Karma

sabari80
Explorer

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 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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 

0 Karma

sabari80
Explorer

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. 

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...