https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402233#M5777 <P>Give this try</P> <PRE><CODE>index=Nitro_server=xs_json Model="*v10*" FirmwareVersion = * earliest=-48h | fields hdr.nitro "Mac_Address" FirmwareVersion | dedup "Mac_Address" | eval nitro_loc=tonumber('hdr.nitro') as nitro_loc | search nitro_loc="*" | lookup nitro_loc.csv nitro_loc OUTPUT TimeZone | stats count by FirmwareVersion TimeZone </CODE></PRE> <P>changes done<BR /> 1) Moved filters to base search wherever possible<BR /> 2) Added fields command to only keep the fields that'll be used in search.<BR /> 3) Moved dedup earlier in the search so that subsequent operations are happening on only required events<BR /> 4) Instead of formatting lookup table column nitro_loc (which will require join), formatted search data field to be number and did regular lookup.</P> Tue, 14 Aug 2018 21:04:01 GMT somesoni2 2018-08-14T21:04:01Z https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402229#M5773 <P>I have a query that is being blocked from retrieving all relevant data due to policy to keep queries under 500mb, is there anyway I could optimize this query?</P> <PRE><CODE>index=Nitro_server=xs_json earliest=-48h | rename hdr.nitro as nitro_loc | join type=inner [ inputlookup nitro_loc.csv | search TimeZone="C" OR "CDT" | eval nitro_loc=case(len(STORE)==4,STORE,len(STORE)==3,"0".STORE,len(STORE)==2,"00".STORE,len(STORE)==1,"000".STORE) ] | search Model="*v10*" nitro_loc="*" FirmwareVersion = * | dedup "Mac_Address" | stats count by FirmwareVersion TimeZone </CODE></PRE> <P>Any suggestions would be appreciated! </P> Tue, 14 Aug 2018 20:01:55 GMT https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402229#M5773 JoshuaJohn 2018-08-14T20:01:55Z https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402230#M5774 <P>Generally try to avoid <CODE>join</CODE> whenever possible. Have you explored just using <CODE>nitro_loc.csv</CODE> as a regular lookup using the <CODE>lookup</CODE> command here?</P> Tue, 14 Aug 2018 20:45:00 GMT https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402230#M5774 Ayn 2018-08-14T20:45:00Z https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402231#M5775 <P>What all fields are you getting from lookup nitro_loc.csv? On what field you're doing the join? </P> Tue, 14 Aug 2018 20:45:41 GMT https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402231#M5775 somesoni2 2018-08-14T20:45:41Z https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402232#M5776 <P>I am doing the join on nitro_loc which is a 4 digit number, and I am trying to get timezone out of the csv.</P> Tue, 14 Aug 2018 20:51:09 GMT https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402232#M5776 JoshuaJohn 2018-08-14T20:51:09Z https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402233#M5777 <P>Give this try</P> <PRE><CODE>index=Nitro_server=xs_json Model="*v10*" FirmwareVersion = * earliest=-48h | fields hdr.nitro "Mac_Address" FirmwareVersion | dedup "Mac_Address" | eval nitro_loc=tonumber('hdr.nitro') as nitro_loc | search nitro_loc="*" | lookup nitro_loc.csv nitro_loc OUTPUT TimeZone | stats count by FirmwareVersion TimeZone </CODE></PRE> <P>changes done<BR /> 1) Moved filters to base search wherever possible<BR /> 2) Added fields command to only keep the fields that'll be used in search.<BR /> 3) Moved dedup earlier in the search so that subsequent operations are happening on only required events<BR /> 4) Instead of formatting lookup table column nitro_loc (which will require join), formatted search data field to be number and did regular lookup.</P> Tue, 14 Aug 2018 21:04:01 GMT https://community.splunk.com/t5/Monitoring-Splunk/Lower-Memory-usage/m-p/402233#M5777 somesoni2 2018-08-14T21:04:01Z