topic Re: How to break multiple values into a new row ? in Splunk Search

How to break multiple values into a new row ?

rsokolova — Thu, 18 Jan 2018 14:12:03 GMT

Thanks in advance,

We are having a hard time trying to split free and used space by partition, hope you can help.

Re: How to break multiple values into a new row ?

harsmarvania57 — Thu, 18 Jan 2018 14:19:15 GMT

Hi @rsokolova

Can you please change | stats in your query to something like this ..... | stats list(capacity) AS capacity, list(space) AS space, list(percent) AS percent by cluster.name, vm_name, partition ?

Re: How to break multiple values into a new row ?

rsokolova — Thu, 18 Jan 2018 14:43:42 GMT

It doesnt work , still need to break capacity and space, which we already tried and didnt work either.

Re: How to break multiple values into a new row ?

harsmarvania57 — Thu, 18 Jan 2018 14:47:07 GMT

Can you please provide one sample event (Please mask any sensitive data) ?

Re: How to break multiple values into a new row ?

rsokolova — Thu, 18 Jan 2018 14:52:28 GMT

I have added another screenshot to the first post. Thanks for checking.

Re: How to break multiple values into a new row ?

harsmarvania57 — Tue, 29 Sep 2020 17:44:53 GMT

Looks like you have mutiple different value for single partition for same vm_name and cluster.name. If you try this query ..... | stats values(capacity) AS capacity, values(space) AS space, values(percent) AS percent by cluster.name, vm_name, partition it will give you less result but it will still provide you more than one value on capacity and space column. Can you please provide some sample events ?

Re: How to break multiple values into a new row ?

mayurr98 — Thu, 18 Jan 2018 15:02:54 GMT

Can you try
| stats count by cluster.name vm_name partition capacity space percent | fields - count

Re: How to break multiple values into a new row ?

rsokolova — Thu, 18 Jan 2018 15:04:50 GMT

I have uploaded another screenshot.

Re: How to break multiple values into a new row ?

harsmarvania57 — Thu, 18 Jan 2018 15:07:58 GMT

As I mentioned earlier there are still multiple values but strange thing is that why single partition on same machine have multiple values, that's why I am asking raw sample data.

Re: How to break multiple values into a new row ?

rsokolova — Thu, 18 Jan 2018 15:13:07 GMT

I have uploaded screenshot of the raw data.

Re: How to break multiple values into a new row ?

rsokolova — Tue, 29 Sep 2020 17:40:59 GMT

Didnt quite work, I have uploaded screenshot to the first post of the raw data that might help.

cluster.name vm_name partition capacity space
Development_General MISEDIDEV01 / 10622074880 10588340224
Development_General MISEDIDEV01 / 10622074880 12790001664
Development_General MISEDIDEV01 / 10622074880 12790087680
Development_General MISEDIDEV01 / 10622074880 2040201216
Development_General MISEDIDEV01 / 10622074880 2074800128
Development_General MISEDIDEV01 / 10622074880 31894880256
Development_General MISEDIDEV01 / 10622074880 31895392256
Development_General MISEDIDEV01 / 10622074880 31902117888
Development_General MISEDIDEV01 / 10622074880 31902179328
Development_General MISEDIDEV01 / 10622074880 3368103936
Development_General MISEDIDEV01 / 10622074880 3553128448

Re: How to break multiple values into a new row ?

rsokolova — Thu, 18 Jan 2018 15:42:22 GMT

This is a piece of the raw data without being organized.

disk": [{"capacity": "12874416128", "diskPath": "/", "freeSpace": "12790087680"}, {"capacity": "7505707008", "diskPath": "/usr", "freeSpace": "3691720704"}, {"capacity": "1063256064", "diskPath": "/boot", "freeSpace": "825552896"}, {"capacity": "42392092672", "diskPath": "/logs", "freeSpace": "41320570880"}, {"capacity": "21250441216", "diskPath": "/shBC", "freeSpace": "2040201216"}, {"capacity": "7505707008", "diskPath": "/home", "freeSpace": "7430582272"}, {"capacity": "4284481536", "diskPath": "/tmp", "freeSpace": "4196638720"}, {"capacity": "10622074880", "diskPath": "/logarch", "freeSpace": "10588340224"}, {"capacity": "63319834624", "diskPath": "/apps", "freeSpace": "31894880256"}, {"capacity": "7505707008", "diskPath": "/var", "freeSpace": "5256704000"}, {"capacity": "7505707008", "diskPath": "/var/tmp", "freeSpace": "5256704000"}],

Re: How to break multiple values into a new row ?

cmerriman — Thu, 18 Jan 2018 15:59:59 GMT

looking at what @harsmarvania57 provided, i think something like that might work for you, aside from the percent field. I'm not sure where that is coming from in your original post.

.... | stats max(capacity) AS capacity, max(space) AS space by cluster.name, vm_name, partition
|eval percent=round((space/capacity)*100,2)."%"

if not, can you provide what isn't working or a sample output of what exactly you're looking for?

Re: How to break multiple values into a new row ?

rsokolova — Tue, 29 Sep 2020 17:41:04 GMT

Didnt work, its showing the same capacity and free space for all the partitions.

cluster.name vm_name partitio capacity space percent
Development_General MISEDIDEV01 / 12874416128 41338839040 321.09%
Development_General MISEDIDEV01 /apps 12874416128 41338839040 321.09%
Development_General MISEDIDEV01 /boot 12874416128 41338839040 321.09%

I expected to see the same output but with the right capacity and space to be able to calculate the percent by partition.

Re: How to break multiple values into a new row ?

harsmarvania57 — Thu, 18 Jan 2018 16:13:20 GMT

Try something like this, first 2 lines are used to generate sample data only.

| makeresults
| eval _raw="{\"disk\": [{\"capacity\": \"12874416128\", \"diskPath\": \"/\", \"freeSpace\": \"12790087680\"}, {\"capacity\": \"7505707008\", \"diskPath\": \"/usr\", \"freeSpace\": \"3691720704\"}, {\"capacity\": \"1063256064\", \"diskPath\": \"/boot\", \"freeSpace\": \"825552896\"}, {\"capacity\": \"42392092672\", \"diskPath\": \"/logs\", \"freeSpace\": \"41320570880\"}, {\"capacity\": \"21250441216\", \"diskPath\": \"/shBC\", \"freeSpace\": \"2040201216\"}, {\"capacity\": \"7505707008\", \"diskPath\": \"/home\", \"freeSpace\": \"7430582272\"}, {\"capacity\": \"4284481536\", \"diskPath\": \"/tmp\", \"freeSpace\": \"4196638720\"}, {\"capacity\": \"10622074880\", \"diskPath\": \"/logarch\", \"freeSpace\": \"10588340224\"}, {\"capacity\": \"63319834624\", \"diskPath\": \"/apps\", \"freeSpace\": \"31894880256\"}, {\"capacity\": \"7505707008\", \"diskPath\": \"/var\", \"freeSpace\": \"5256704000\"}, {\"capacity\": \"7505707008\", \"diskPath\": \"/var/tmp\", \"freeSpace\": \"5256704000\"}]}"
| spath
| rename disk{}.capacity AS capacity, disk{}.diskPath AS path, disk{}.freeSpace AS space
| eval cap_path=mvzip(capacity,path)
| eval cap_path_space=mvzip(cap_path,space)
| mvexpand cap_path_space
| makemv delim="," cap_path_space
| eval capacity=mvindex(cap_path_space,0), path=mvindex(cap_path_space,1), space=mvindex(cap_path_space,2)
| fields - cap_path,cap_path_space

Re: How to break multiple values into a new row ?

rsokolova — Thu, 18 Jan 2018 16:29:29 GMT

It worked. Thank you so much.

Re: How to break multiple values into a new row ?

harsmarvania57 — Thu, 18 Jan 2018 16:32:34 GMT

I have converted my comment to answer so you can accept/upvote it.

Re: How to break multiple values into a new row ?

cmerriman — Thu, 18 Jan 2018 16:32:46 GMT

right, that means it's still taking all the values and grabbing the max, and it'll do that if you use values or list, as well.

@harsmarvania57 nailed it with joining the fields together using mvzip.
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/MultivalueEvalFunctions#mvzip.28X.2CY.2C.22Z.22.29

extract your fields with spath, and then add something like

| eval cap_path=mvzip(capacity,path)
 | eval cap_path_space=mvzip(cap_path,space)
 | mvexpand cap_path_space
 | makemv delim="," cap_path_space
 | eval capacity=mvindex(cap_path_space,0), path=mvindex(cap_path_space,1), space=mvindex(cap_path_space,2)| eval percent=round((space/capacity)*100,2)."%"

Re: How to break multiple values into a new row ?

rsokolova — Thu, 18 Jan 2018 16:38:56 GMT

Thanks again for your time!

Re: How to break multiple values into a new row ?

skoelpin — Thu, 18 Jan 2018 16:53:44 GMT

Great solution @harsmarvania57 !