How to break multiple values into a new row ?

[rsokolova]

Thanks in advance,

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

[harsmarvania57 · Accepted solution]

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

[skoelpin]

Great solution @harsmarvania57 !

[harsmarvania57]

Thanks @skoelpin

[rsokolova]

It worked. Thank you so much.

[harsmarvania57]

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

[rsokolova]

Thanks again for your time!

[harsmarvania57]

Welcome @rsokolova

[cmerriman]

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?

[rsokolova]

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.

[cmerriman]

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#mvzi...

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)."%"

[mayurr98]

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

[rsokolova]

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

[harsmarvania57]

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 ?

[rsokolova]

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

[harsmarvania57]

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

[rsokolova]

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

[harsmarvania57]

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 ?

[rsokolova]

I have uploaded another screenshot.

[harsmarvania57]

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.

[rsokolova]

I have uploaded screenshot of the raw data.