topic Re: Create new rows from multi-value fields in Splunk Search

Create new rows from multi-value fields

robjackson — Thu, 18 Apr 2019 20:56:07 GMT

So basically what we need is a new row with the new data. A new row for each value pair of Software and Version.

If we have.

We want this.

Re: Create new rows from multi-value fields

vnravikumar — Thu, 18 Apr 2019 21:55:13 GMT

Give a try

| makeresults 
| eval CI_Name="A",Software_installed="ms,info,block,top",Software_Version="2013,4.0,5.1,6" 
| eval Software_installed_temp=Software_installed, Software_Version_temp=Software_Version 
| makemv delim="," Software_installed 
| makemv delim="," Software_Version 
| eval temp = mvzip(Software_installed,Software_Version,",") 
| mvexpand temp 
| eval software_version= split(temp,",") 
| eval Software= mvindex(software_version,0) 
| eval SoftwareV= mvindex(software_version,1) 
| rename Software_installed_temp as Software_installed,Software_Version_temp as Software_Version 
| table CI_Name,Software_installed,Software_Version,Software,SoftwareV

Re: Create new rows from multi-value fields

VatsalJagani — Fri, 19 Apr 2019 05:45:38 GMT

Hi @robjackson,
Please try below query.

| makeresults 
| eval Cl_Name="A", Software_Installed="ms, info, block, top", Software_Version="2013, 4.0, 5.1, 6" 
| append 
    [| makeresults 
    | eval Cl_Name="B", Software_Installed="ms, info, block, top", Software_Version="2013, 4.0, 5.1, 6"] 
| append 
    [| makeresults 
    | eval Cl_Name="A", Software_Installed="ms, info, block, top", Software_Version="2013, 4.0, 5.1, 6"]
| table Cl_Name, Software_Installed, Software_Version
| eval soft_=Software_Installed
| eval ver_=Software_Version
| makemv soft_ delim=", "
| makemv ver_ delim=", "
| eval soft_zip = mvzip(soft_, ver_)
| mvexpand soft_zip
| rex field=soft_zip "(?<soft>.*),(?<ver>.*)"
| table Cl_Name Software_Installed, Software_Version, soft, ver

Re: Create new rows from multi-value fields

robjackson — Wed, 30 Sep 2020 00:07:48 GMT

That works as a stand alone search.

But returns no data when adding our search.

index=something 
 | table Cl_Name, Software_Installed, Software_Version
 | eval soft_=Software_Installed
 | eval ver_=Software_Version
 | makemv soft_ delim=", "
 | makemv ver_ delim=", "
 | eval soft_zip = mvzip(soft_, ver_)
 | mvexpand soft_zip
 | rex field=soft_zip "(?<soft>.*),(?<ver>.*)"
 | table Cl_Name Software_Installed, Software_Version, soft, ver

or since | makeresults must be the first command. the index=something returns over 15,000 CI_Name, but this search returns 0.

If it would work I would expect 2 million rows. So CI_Name X (Software_Installed X Softeware_Version).

 | makeresults 
 [| search index=something]
 | table Cl_Name, Software_Installed, Software_Version
 | eval soft_=Software_Installed
 | eval ver_=Software_Version
 | makemv soft_ delim=", "
 | makemv ver_ delim=", "
 | eval soft_zip = mvzip(soft_, ver_)
 | mvexpand soft_zip
 | rex field=soft_zip "(?<soft>.*),(?<ver>.*)"
 | table Cl_Name Software_Installed, Software_Version, soft, ver

Re: Create new rows from multi-value fields

robjackson — Wed, 30 Sep 2020 00:07:51 GMT

Parts of it work. But it only returns 15,000 CI_Name.

soft = the Softeware_Installed and Software_Version
ver= just the first value of the multi-value Software_Version

Index=something

| table CI_Name Software_Installed, Software_Version

Re: Create new rows from multi-value fields

robjackson — Wed, 30 Sep 2020 00:07:56 GMT

I got it to kinda of work.

The delimiter in the data was " ," instead of ", ". However, when i run index=something and filter only items with something in Software_Installed. I get over 9,000 unique CI_Name items.

the same search with the added extracts creates 52600 rows. But, only has 169 unique CI_Name items.

index=something

Re: Create new rows from multi-value fields

woodcock — Sun, 21 Apr 2019 14:44:55 GMT

You need makemv to make the fields multi-valued, and then mvexpand to split them into individual rows:
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Makemv
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Mvexpand

Re: Create new rows from multi-value fields

robjackson — Wed, 30 Sep 2020 00:08:09 GMT

The SPL works. But is only returning 51400 rows. Root cause the max_mem_usage_mb in the limits.conf is 500 MB and the search exceeds this.

May have to change both settings below.

max_mem_usage_mb = 500 #default memory usage per search is 200MB probably need to increase to support that many rows
[searchresults]
maxresultrows = 86400 #default return for rows is 50000

Re: Create new rows from multi-value fields

niketn — Mon, 22 Apr 2019 18:15:06 GMT

@robjackson try the following approach with stats instead of mvexpand.
PS: I did not hit memory limit of 500 MB with mvexpand with 52K rows (most likely because the dummy data generation query using makeresults for demo purpose is way less expensive than your existing main search). So I bumped up makeresults rows to 520K.

While stats worked fine for me, I had issue with mvexpand results being truncated (query 2 below threw following message: command.mvexpand: output will be truncated at 1497700 results due to excessive memory usage. Memory threshold of 500MB as configured in limits.conf / [mvexpand] / max_mem_usage_mb has been reached.)

Please confirm that truncation is avoided with first stats query below or not:

Query with stats (Search returned all 520000*4=2080000 results)

| makeresults count=520000 
| fields - _time 
| eval CI_Name="A",Software_installed="ms,info,block,top",Software_Version="2013,4.0,5.1,6" 
| streamstats count as sno 
| eval sno=printf("%05d",sno), CI_Name=CI_Name.sno 
| fields - sno
| makemv delim="," Software_installed 
| makemv delim="," Software_Version 
| eval temp = mvzip(Software_installed,Software_Version,",") 
| stats count by CI_Name temp 
| fields - count 
| eval temp=split(temp,",") 
| eval Software_installed=mvindex(temp,0),Software_Version=mvindex(temp,1) 
| fields - temp

*Query with mvexpand * (Results got truncated due to mvexpand memory usage limit of 500 MB on my machine. Only 1497700 results returned)

| makeresults count=520000 
| fields - _time 
| eval CI_Name="A",Software_installed="ms,info,block,top",Software_Version="2013,4.0,5.1,6" 
| streamstats count as sno 
| eval sno=printf("%05d",sno), CI_Name=CI_Name.sno 
| fields - sno
| makemv delim="," Software_installed 
| makemv delim="," Software_Version 
| eval temp = mvzip(Software_installed,Software_Version,",") 
| stats count by CI_Name temp 
| fields - count 
| eval temp=split(temp,",") 
| eval Software_installed=mvindex(temp,0),Software_Version=mvindex(temp,1) 
| fields - temp

Re: Create new rows from multi-value fields

robjackson — Wed, 30 Sep 2020 00:08:12 GMT

I changed the following on the search head cluster.

[mvexpand]
max_mem_usage_mb = 2500

the systems have plenty of ram. How high can we go for this setting? the report would run every 12 hours and output(overwrite) to a lookup.

The error stopped but it still is not showing all of the data.

there are over 4000 devices. Either still ram or the row returned max.

before the change it showed 51400 rows which was only 19 device ( used Excel to remove duplicates).

After the change it shows over 246k rows for 23 devices. But no memory error.

Re: Create new rows from multi-value fields

robjackson — Mon, 22 Apr 2019 18:52:35 GMT

Looks like it is working now. Thanks for your help.

Re: Create new rows from multi-value fields

niketn — Mon, 22 Apr 2019 19:13:30 GMT

@robjackson, first off, request you to post a comment to any of the existing Answers that community members have provided. Posting comment as an Answer is misleading and due to your karma points requires moderators to publish the same (causing delay).

Did you get to test and compare the two options provided in my answer above? Did the stats one work for you?

The answer to question you have posted here the systems have plenty of ram. How high can we go for this setting?, is that it depends on how much data you can test this with (in your test environment which simulates production configuration).

Use my query above and keep extending the numbers until you get max_mem_usage_mb. You would also need to continuously monitor your test machine memory usage with respect to splunkd process whether it reaches 100% or not. You may get to OS terminate splunkd process if it lead to full memory consumption, thereby terminating job.

Also depending on the maximum number of software installed per CI, you can calculate the total number of events to be cooked up and total number of results to be displayed in the final results (maximum expected volume). However for us to assist you better you would need to provide more detail of your raw event for example, do you actually run the following command or do you perform stats aggregate before using table command? Are you interested only in the latest data per CI or not? Comma separated values exist for each CI or you perform some calculation to derive this in Splunk. Only after you can provide some more insight to your data we would be able to assist you better.

 index=something
 | table CI_Name Software_Installed, Software_Version

Finally, I would suggest you to reach out to Splunk Support with your entitlement or else take Splunk Professional Services help as this issue seems more on your data/infrastructure and query performance tuning and scale kind of issue. Community assistance without access to your infrastructure/data would be a tough ask!

Re: Create new rows from multi-value fields

niketn — Mon, 22 Apr 2019 19:14:37 GMT

@robjackson glad you got it to work! Do accept my answer above and up vote the comment/s that helped!
As stated earlier, instead of posting a New Answer, use an existing answer or thread and use Add comment to post your comment.

Re: Create new rows from multi-value fields

niketn — Mon, 22 Apr 2019 19:39:00 GMT

@robjackson did you get your query working by mvexpand with higher memory or by using stats command? What is your final query?
While posting code use the code button on Splunk Answer i.e. 101010 or shortcut Ctrl+K, so that special characters in your search query does not escape.

Re: Create new rows from multi-value fields

robjackson — Wed, 30 Sep 2020 00:08:14 GMT

Had to change the max_mem_usage_mb in the local\limits.conf file on each search head. Default was 500.

[mvexpand]
max_mem_usage_mb = 2500

index=something

Re: Create new rows from multi-value fields

niketn — Mon, 22 Apr 2019 20:02:28 GMT

Ok refer to my previous answer with stats and you might not need to increase the memory limit as mvexpand need memory. ( up to you to try as your issue is already resolved) 😉