topic Re: mvexpand memory issue in Splunk Search

mvexpand memory issue

mbasharat — Mon, 19 Apr 2021 01:37:10 GMT

Hi,

I have very large dataset that appears as multivalued as below:

When I try to use mvexpand using below technique, it exceeds default memory limit of 500MB. I did increase it to 5000MB but problem remains. I did try to limit _raw and multiple other techniques but to no avail. I can not use stats because cardinality is high as I have more fields, ~ 8 fields to expand. Below is a great solution which I have been using at many places for smaller dataset but not for larger dataset. For example, my saved search produces ~6000 records. When those records are to be expanded, I am expecting them to be around 30,000 rows. I need output to be like below but without mvexpand so we do not have to worry about mvexpand memory limits yet still get complete dataset as needed. Is this possible?

Thanks in-advance!!!!

Re: mvexpand memory issue

bowesmana — Mon, 19 Apr 2021 03:43:06 GMT

Depending on what fields you absolutely need AFTER the mvexpand, try first to remove any fields you will not use after you have expanded the events. If you are working with the raw events but also have fields extracted, then remove _raw if you do not need it any more.

From your question, I understand that you want to add expand the rows, so there is one row for each of the

Windows_7,Unknown,Windows_2012,Windows_7,Windows_8,Windows_10

attributes.

You should also NOT create the multi value fields BEFORE you mvexpand as Splunk then has to expand all those fields too.

Do this AFTER the expansion, e.g.

| makeresults | eval Asset_Category=mvrange(0,mvcount(split("Windows_7,Unknown,Windows_2012,Windows_7,Windows_8,Windows_10", ",")) | mvexpand Asset_Category | eval data1=mvindex(split("Windows_7,Unknown,Windows_2012,Windows_7,Windows_8,Windows_10", ","),Asset_Category) | eval data2=mvindex(split("LAPTOP PC,SERVER,APPLIANCE,DESKTOP,ROUTER,SWITCH", ","),Asset_Category)

There is no point in expanding all the multi-value fields, as all you are trying to do is to get a SINGLE value corresponding to Asset_Category in each of the expanded events

Re: mvexpand memory issue

mbasharat — Mon, 19 Apr 2021 13:43:11 GMT

Hi @bowesmana ,

I have tried all that but to no avail. See my search below:

index=nessus sourcetype="tenable:sc:vuln" severity!=informational ip=* dnsName=* | fields - index, source, sourcetype, splunk_server, splunk_server_group, host, eventtype, field, linecount, punct, tag, tag::eventtype, _raw | fields ip, dnsName, firstSeen, plugin_id, cve, cvss2_base_score, cvssV3BaseScore, severity, vprScore, state | makemv delim="," cve | stats dc(ip) AS Affected_IP_Count, dc(dnsName) AS Affected_DNS_Count, latest(firstSeen) AS First_Seen_Date, latest(state) AS State, latest(vprScore) AS VPR_Score, latest(severity) AS Tenable_Severity, latest(cvss2_base_score) AS cvss2_base_score, latest(cvssV3BaseScore) AS cvssV3BaseScore by plugin_id, cve | lookup vuln_affected_entities_info CVE AS cve OUTPUT Scan_Type, CATEGORY, KISAM_OS_Grouped, Nessus_OS_Grouped, EOPS_Assignment_Group, Responsible_Organization, KISAM_GSS, Project_Type, KISAM_Projects, UCMDB_Environment, Boundary_Name, Boundary_System | eval Asset_Category=mvrange(0,mvcount(CATEGORY)) | mvexpand Asset_Category | eval Scan_Type=mvindex(Scan_Type,Asset_Category) | eval CATEGORY=mvindex(CATEGORY,Asset_Category) | eval KISAM_OS_Grouped=mvindex(KISAM_OS_Grouped,Asset_Category) | eval Nessus_OS_Grouped=mvindex(Nessus_OS_Grouped,Asset_Category) | eval EOPS_Assignment_Group=mvindex(EOPS_Assignment_Group,Asset_Category) | eval Responsible_Organization=mvindex(Responsible_Organization,Asset_Category) | eval KISAM_GSS=mvindex(KISAM_GSS,Asset_Category) | eval Project_Type=mvindex(Project_Type,Asset_Category) | eval KISAM_Projects=mvindex(KISAM_Projects,Asset_Category) | eval UCMDB_Environment=mvindex(UCMDB_Environment,Asset_Category) | eval Boundary_Name=mvindex(Boundary_Name,Asset_Category) | eval Boundary_System=mvindex(Boundary_System,Asset_Category) | rename cvss2_base_score AS CVSS_Score_v2, cvssV3BaseScore AS CVSS_Score_v3, cve AS CVE, plugin_id AS Plugin_ID | lookup cvss_score_v2_nvd_severity CVSS_Score_v2 AS CVSS_Score_v2 OUTPUT NVD_Severity_v2 | lookup cvss_score_v3_nvd_severity CVSS_Score_v3 AS CVSS_Score_v3 OUTPUT NVD_Severity_v3 | eval First_Seen_Date=strftime(First_Seen_Date,"%m/%d/%y %H:%M:%S") | eval Age_Days=ceiling((now()-strptime(First_Seen_Date,"%m/%d/%y"))/86400) | eval CVE_Age=case( Age_Days<30,"A_0 to 29 Days", Age_Days>=365,"G_365 Days+", Age_Days>=180,"F_180 to 364 Days", Age_Days>=120,"E_120 to 179 Days", Age_Days>=90,"D_90 to 119 Days", Age_Days>=60,"C_60 to 89 Days", Age_Days>=30,"B_30 to 59 Days", 0==0,"H_No Age Data") | eval Remediation_Status=case( State="fixed","Remediated", State="open","Not_Remediated", State="reopened","Not_Remediated") | eval Time_to_Remediation_Days=if(Remediation_Status=="Remediated",'Age_Days',"Not_Remediated_Yet") | eval CVE_Age_Remediation=case( Time_to_Remediation_Days<30,"A_0 to 29 Days", Time_to_Remediation_Days>=365,"G_365 Days+", Time_to_Remediation_Days>=180,"F_180 to 364 Days", Time_to_Remediation_Days>=120,"E_120 to 179 Days", Time_to_Remediation_Days>=90,"D_90 to 119 Days", Time_to_Remediation_Days>=60,"C_60 to 89 Days", Time_to_Remediation_Days>=30,"B_30 to 59 Days", 0==0,"H_Not Remediated Yet") | eval Tenable_Severity = upper(Tenable_Severity) | fields Plugin_ID, CVE, CVSS_Score_v2, CVSS_Score_v3, NVD_Severity_v2, NVD_Severity_v3, Tenable_Severity, VPR_Score, First_Seen_Date, Age_Days, CVE_Age, State, Remediation_Status, Time_to_Remediation_Days, CVE_Age_Remediation, Affected_IP_Count, Affected_DNS_Count, Scan_Type, CATEGORY, KISAM_OS_Grouped, Nessus_OS_Grouped, EOPS_Assignment_Group, Responsible_Organization, KISAM_GSS, Project_Type, KISAM_Projects, UCMDB_Environment, Boundary_Name, Boundary_System

Re: mvexpand memory issue

ITWhisperer — Mon, 19 Apr 2021 14:27:09 GMT

Try something like this

I added an extra event to show it working across multiple events. The only gotcha is if the first event has only one multi-value, but this can gotten around with a bit more SPL.

If the order of the subrows is important/needs to be preserved, you just need to do an extra calculation with the mvindex.

Re: mvexpand memory issue

mbasharat — Mon, 19 Apr 2021 15:13:24 GMT

Hi @ITWhisperer,

Thanks to both you and @bowesmana . I have attached my search below, can you please check and assist in tweaking based on your above response?

Thanks in -advance!!!!

Re: mvexpand memory issue

ITWhisperer — Mon, 19 Apr 2021 15:42:39 GMT

Try something like this:

index=nessus sourcetype="tenable:sc:vuln" severity!=informational ip=* dnsName=* | fields - index, source, sourcetype, splunk_server, splunk_server_group, host, eventtype, field, linecount, punct, tag, tag::eventtype, _raw | fields ip, dnsName, firstSeen, plugin_id, cve, cvss2_base_score, cvssV3BaseScore, severity, vprScore, state | makemv delim="," cve | stats dc(ip) AS Affected_IP_Count, dc(dnsName) AS Affected_DNS_Count, latest(firstSeen) AS First_Seen_Date, latest(state) AS State, latest(vprScore) AS VPR_Score, latest(severity) AS Tenable_Severity, latest(cvss2_base_score) AS cvss2_base_score, latest(cvssV3BaseScore) AS cvssV3BaseScore by plugin_id, cve | lookup vuln_affected_entities_info CVE AS cve OUTPUT Scan_Type, CATEGORY, KISAM_OS_Grouped, Nessus_OS_Grouped, EOPS_Assignment_Group, Responsible_Organization, KISAM_GSS, Project_Type, KISAM_Projects, UCMDB_Environment, Boundary_Name, Boundary_System | eval subrows=mvcount(CATEGORY) | streamstats sum(subrows) as subrow | eval topsubrow=subrow | append [| makeresults | eval subrow=1 ] | fields - _time | makecontinuous subrow | sort - subrow | filldown | eval Asset_Category=topsubrow-subrow | eval Scan_Type=mvindex(Scan_Type,Asset_Category) | eval CATEGORY=mvindex(CATEGORY,Asset_Category) | eval KISAM_OS_Grouped=mvindex(KISAM_OS_Grouped,Asset_Category) | eval Nessus_OS_Grouped=mvindex(Nessus_OS_Grouped,Asset_Category) | eval EOPS_Assignment_Group=mvindex(EOPS_Assignment_Group,Asset_Category) | eval Responsible_Organization=mvindex(Responsible_Organization,Asset_Category) | eval KISAM_GSS=mvindex(KISAM_GSS,Asset_Category) | eval Project_Type=mvindex(Project_Type,Asset_Category) | eval KISAM_Projects=mvindex(KISAM_Projects,Asset_Category) | eval UCMDB_Environment=mvindex(UCMDB_Environment,Asset_Category) | eval Boundary_Name=mvindex(Boundary_Name,Asset_Category) | eval Boundary_System=mvindex(Boundary_System,Asset_Category) | rename cvss2_base_score AS CVSS_Score_v2, cvssV3BaseScore AS CVSS_Score_v3, cve AS CVE, plugin_id AS Plugin_ID | lookup cvss_score_v2_nvd_severity CVSS_Score_v2 AS CVSS_Score_v2 OUTPUT NVD_Severity_v2 | lookup cvss_score_v3_nvd_severity CVSS_Score_v3 AS CVSS_Score_v3 OUTPUT NVD_Severity_v3 | eval First_Seen_Date=strftime(First_Seen_Date,"%m/%d/%y %H:%M:%S") | eval Age_Days=ceiling((now()-strptime(First_Seen_Date,"%m/%d/%y"))/86400) | eval CVE_Age=case( Age_Days<30,"A_0 to 29 Days", Age_Days>=365,"G_365 Days+", Age_Days>=180,"F_180 to 364 Days", Age_Days>=120,"E_120 to 179 Days", Age_Days>=90,"D_90 to 119 Days", Age_Days>=60,"C_60 to 89 Days", Age_Days>=30,"B_30 to 59 Days", 0==0,"H_No Age Data") | eval Remediation_Status=case( State="fixed","Remediated", State="open","Not_Remediated", State="reopened","Not_Remediated") | eval Time_to_Remediation_Days=if(Remediation_Status=="Remediated",'Age_Days',"Not_Remediated_Yet") | eval CVE_Age_Remediation=case( Time_to_Remediation_Days<30,"A_0 to 29 Days", Time_to_Remediation_Days>=365,"G_365 Days+", Time_to_Remediation_Days>=180,"F_180 to 364 Days", Time_to_Remediation_Days>=120,"E_120 to 179 Days", Time_to_Remediation_Days>=90,"D_90 to 119 Days", Time_to_Remediation_Days>=60,"C_60 to 89 Days", Time_to_Remediation_Days>=30,"B_30 to 59 Days", 0==0,"H_Not Remediated Yet") | eval Tenable_Severity = upper(Tenable_Severity) | fields Plugin_ID, CVE, CVSS_Score_v2, CVSS_Score_v3, NVD_Severity_v2, NVD_Severity_v3, Tenable_Severity, VPR_Score, First_Seen_Date, Age_Days, CVE_Age, State, Remediation_Status, Time_to_Remediation_Days, CVE_Age_Remediation, Affected_IP_Count, Affected_DNS_Count, Scan_Type, CATEGORY, KISAM_OS_Grouped, Nessus_OS_Grouped, EOPS_Assignment_Group, Responsible_Organization, KISAM_GSS, Project_Type, KISAM_Projects, UCMDB_Environment, Boundary_Name, Boundary_System

Re: mvexpand memory issue

bowesmana — Mon, 19 Apr 2021 23:07:22 GMT

@mbasharat

It looks like you are still expanding all the multivalue fields. Your lookup is BEFORE the mvexpand and from the eval statements following, those are the fields causing the memory overflow.

If you place the lookup AFTER the mvexpand like this

| lookup vuln_affected_entities_info CVE AS cve OUTPUT CATEGORY | eval Asset_Category=mvrange(0,mvcount(CATEGORY)) | mvexpand Asset_Category | lookup vuln_affected_entities_info CVE AS cve OUTPUT Scan_Type, KISAM_OS_Grouped, Nessus_OS_Grouped, EOPS_Assignment_Group, Responsible_Organization, KISAM_GSS, Project_Type, KISAM_Projects, UCMDB_Environment, Boundary_Name, Boundary_System | eval Scan_Type=mvindex(Scan_Type,Asset_Category) | eval CATEGORY=mvindex(CATEGORY,Asset_Category) | eval KISAM_OS_Grouped=mvindex(KISAM_OS_Grouped,Asset_Category) | eval Nessus_OS_Grouped=mvindex(Nessus_OS_Grouped,Asset_Category) | eval EOPS_Assignment_Group=mvindex(EOPS_Assignment_Group,Asset_Category) | eval Responsible_Organization=mvindex(Responsible_Organization,Asset_Category) | eval KISAM_GSS=mvindex(KISAM_GSS,Asset_Category) | eval Project_Type=mvindex(Project_Type,Asset_Category) | eval KISAM_Projects=mvindex(KISAM_Projects,Asset_Category) | eval UCMDB_Environment=mvindex(UCMDB_Environment,Asset_Category) | eval Boundary_Name=mvindex(Boundary_Name,Asset_Category) | eval Boundary_System=mvindex(Boundary_System,Asset_Category)

So, you do a simple lookup to get CATEGORY to get the count to expand, then do the expansion followed by a lookup that will then give you all the MV fields from the lookup, which you then mvindex out.

This is a very simple change that avoids mvexpand having to add all the MV fields. Adding those fields after the expansion achieves the same thing but doesn't hit the memory limits of mvexpand.

Re: mvexpand memory issue

mbasharat — Tue, 20 Apr 2021 13:57:46 GMT

Hi @ITWhisperer ,

Your provided solution works. However, I am getting below error:

Unexpected duplicate values in field 'subrow' have been detected.

Re: mvexpand memory issue

ITWhisperer — Tue, 20 Apr 2021 14:13:13 GMT

This is because the first row has only a single value in the multi-value field which is the gotcha I mentioned earlier. I will try and dig out the solution to that and repost.

Re: mvexpand memory issue

mbasharat — Tue, 20 Apr 2021 14:26:15 GMT

Hi @ITWhisperer

In addition to previous error I mentioned, I am also seeing stripped results and below error:

The specified span would result in too many (>250000) rows.

Re: mvexpand memory issue

ITWhisperer — Tue, 20 Apr 2021 14:31:56 GMT

OK I added a couple of lines before the makecontinuous to effectively remove the additional event if the mvcount for the first original event is 1

index=nessus sourcetype="tenable:sc:vuln" severity!=informational ip=* dnsName=* | fields - index, source, sourcetype, splunk_server, splunk_server_group, host, eventtype, field, linecount, punct, tag, tag::eventtype, _raw | fields ip, dnsName, firstSeen, plugin_id, cve, cvss2_base_score, cvssV3BaseScore, severity, vprScore, state | makemv delim="," cve | stats dc(ip) AS Affected_IP_Count, dc(dnsName) AS Affected_DNS_Count, latest(firstSeen) AS First_Seen_Date, latest(state) AS State, latest(vprScore) AS VPR_Score, latest(severity) AS Tenable_Severity, latest(cvss2_base_score) AS cvss2_base_score, latest(cvssV3BaseScore) AS cvssV3BaseScore by plugin_id, cve | lookup vuln_affected_entities_info CVE AS cve OUTPUT Scan_Type, CATEGORY, KISAM_OS_Grouped, Nessus_OS_Grouped, EOPS_Assignment_Group, Responsible_Organization, KISAM_GSS, Project_Type, KISAM_Projects, UCMDB_Environment, Boundary_Name, Boundary_System | eval subrows=mvcount(CATEGORY) | streamstats sum(subrows) as subrow | eval topsubrow=subrow | append [| makeresults | eval subrow=1 ] | fields - _time | eventstats min(topsubrow) as firsttop | where isnotnull(topsubrow) or topsubrow != firsttop | makecontinuous subrow | sort - subrow | filldown | eval Asset_Category=topsubrow-subrow | eval Scan_Type=mvindex(Scan_Type,Asset_Category) | eval CATEGORY=mvindex(CATEGORY,Asset_Category) | eval KISAM_OS_Grouped=mvindex(KISAM_OS_Grouped,Asset_Category) | eval Nessus_OS_Grouped=mvindex(Nessus_OS_Grouped,Asset_Category) | eval EOPS_Assignment_Group=mvindex(EOPS_Assignment_Group,Asset_Category) | eval Responsible_Organization=mvindex(Responsible_Organization,Asset_Category) | eval KISAM_GSS=mvindex(KISAM_GSS,Asset_Category) | eval Project_Type=mvindex(Project_Type,Asset_Category) | eval KISAM_Projects=mvindex(KISAM_Projects,Asset_Category) | eval UCMDB_Environment=mvindex(UCMDB_Environment,Asset_Category) | eval Boundary_Name=mvindex(Boundary_Name,Asset_Category) | eval Boundary_System=mvindex(Boundary_System,Asset_Category) | rename cvss2_base_score AS CVSS_Score_v2, cvssV3BaseScore AS CVSS_Score_v3, cve AS CVE, plugin_id AS Plugin_ID | lookup cvss_score_v2_nvd_severity CVSS_Score_v2 AS CVSS_Score_v2 OUTPUT NVD_Severity_v2 | lookup cvss_score_v3_nvd_severity CVSS_Score_v3 AS CVSS_Score_v3 OUTPUT NVD_Severity_v3 | eval First_Seen_Date=strftime(First_Seen_Date,"%m/%d/%y %H:%M:%S") | eval Age_Days=ceiling((now()-strptime(First_Seen_Date,"%m/%d/%y"))/86400) | eval CVE_Age=case( Age_Days<30,"A_0 to 29 Days", Age_Days>=365,"G_365 Days+", Age_Days>=180,"F_180 to 364 Days", Age_Days>=120,"E_120 to 179 Days", Age_Days>=90,"D_90 to 119 Days", Age_Days>=60,"C_60 to 89 Days", Age_Days>=30,"B_30 to 59 Days", 0==0,"H_No Age Data") | eval Remediation_Status=case( State="fixed","Remediated", State="open","Not_Remediated", State="reopened","Not_Remediated") | eval Time_to_Remediation_Days=if(Remediation_Status=="Remediated",'Age_Days',"Not_Remediated_Yet") | eval CVE_Age_Remediation=case( Time_to_Remediation_Days<30,"A_0 to 29 Days", Time_to_Remediation_Days>=365,"G_365 Days+", Time_to_Remediation_Days>=180,"F_180 to 364 Days", Time_to_Remediation_Days>=120,"E_120 to 179 Days", Time_to_Remediation_Days>=90,"D_90 to 119 Days", Time_to_Remediation_Days>=60,"C_60 to 89 Days", Time_to_Remediation_Days>=30,"B_30 to 59 Days", 0==0,"H_Not Remediated Yet") | eval Tenable_Severity = upper(Tenable_Severity) | fields Plugin_ID, CVE, CVSS_Score_v2, CVSS_Score_v3, NVD_Severity_v2, NVD_Severity_v3, Tenable_Severity, VPR_Score, First_Seen_Date, Age_Days, CVE_Age, State, Remediation_Status, Time_to_Remediation_Days, CVE_Age_Remediation, Affected_IP_Count, Affected_DNS_Count, Scan_Type, CATEGORY, KISAM_OS_Grouped, Nessus_OS_Grouped, EOPS_Assignment_Group, Responsible_Organization, KISAM_GSS, Project_Type, KISAM_Projects, UCMDB_Environment, Boundary_Name, Boundary_System

Re: mvexpand memory issue

mbasharat — Tue, 20 Apr 2021 14:35:16 GMT

Thank you!!! @ITWhisperer. Awaiting response.

So still need complete dataset and no results truncation pls.

Re: mvexpand memory issue

mbasharat — Tue, 20 Apr 2021 14:38:13 GMT

Hi @bowesmana,

I am also testing your providing solution parallel. Will report back shortly. TY!

Re: mvexpand memory issue

ITWhisperer — Tue, 20 Apr 2021 14:43:45 GMT

Sounds like you need to increase your limits.

Re: mvexpand memory issue

mbasharat — Tue, 20 Apr 2021 15:06:46 GMT

HI @bowesmana

Just tried and same issue! 😞

Re: mvexpand memory issue

mbasharat — Tue, 20 Apr 2021 15:29:19 GMT

Hi @ITWhisperer

Same error + truncated results/rows.

Unexpected duplicate values in field 'subrow' have been detected.

Re: mvexpand memory issue

ITWhisperer — Tue, 20 Apr 2021 15:36:13 GMT

Do you have any events where CATEGORY is null? This would cause a problem. If so, presumably these events can be removed?

| where isnotnull(CATEGORY) | eval subrows=mvcount(CATEGORY)

Re: mvexpand memory issue

mbasharat — Tue, 20 Apr 2021 15:56:35 GMT

I do have NULLs in the data but they have to be included. If it helps, see attached screenshot. Thats how fields from lookup get output.

Re: mvexpand memory issue

ITWhisperer — Tue, 20 Apr 2021 16:15:56 GMT

The issue would be if specifically CATEGORY is null, which it isn't in your screenshot. Can you try this to see if you get any events?

If you do, what does that mean for the "mvexpand"?

Re: mvexpand memory issue

mbasharat — Tue, 20 Apr 2021 16:31:59 GMT

Hi @ITWhisperer

It has some nulls/unknowns. This means that asset does not have a category but there is organization, gss, etc. so yes, there is other data as aforementioned which means I have to find a solution to replace mvexpand but still get all rows expanded.