How to expand grouped table into single row?

[satish]

Hi Splunk Experts,

Im looking for help in splitting a table grouped into single row into multiple rows. I would like identify the filesystems that are above 40% and would like to collect stats and visuals.

The Statistics for table is displayed as single row only. I tried mvexpand but it doesnt accept 2 fields, only accepts one field. If i apply for field but it generates many rows.

Im missing something here. Can you please help me with workaround.

Splunk Query:
----------------

index=lab_env host=labhmc earliest=-4h latest=now
| spath path=hmc_info{} output=LIST
| rename LIST as _raw
| kv
| rex field="hmc_info{}.fs_utilization" mode=sed "s/\%//g"
| table hmc_name hmc_info{}.Filesystem hmc_info{}.fs_utilization

Splunk Event:
---------------

{"category": "hmc", "hmc_name": "labhmc", "hmc_uptime": "73", "hmc_data_ip": "127.0.0.1", "hmc_priv_ip": "127.0.0.1", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/dev", "fs_utilization": "0%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/dev/shm", "fs_utilization": "1%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/run", "fs_utilization": "3%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/sys/fs/cgroup", "fs_utilization": "0%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/", "fs_utilization": "46%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/data", "fs_utilization": "2%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/home", "fs_utilization": "4%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/extra", "fs_utilization": "17%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/dump", "fs_utilization": "1%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/var", "fs_utilization": "14%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/var/hsc/log", "fs_utilization": "25%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/run/user/601", "fs_utilization": "0%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/run/user/604", "fs_utilization": "1%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}
{"category": "hmc_filesystem", "hmc_name": "labhmc", "Filesystem": "/run/user/600", "fs_utilization": "0%", "hmc_version": "V8.65.22", "hmcmodel": "7164-r15", "hmcserial": "673456B", "datacenter": "LAB", "country": "DE"}

[richgalloway]

Please share a mockup of the desired output.

[satish]

@richgalloway 

Below is the current and expected output details.  basically I would like to find the filesystems above 40. 

Current Output from Statistics (They are currently grouped into Single row):

---------------------------------------------------------------------------

hmc_name hmc_info{}.Filesystem hmc_info{}.fs_utilization

labhmc    /dev 1

/dev/shm3

/run0

/sys/fs/cgroup0

/46

/data2

/home4

/extra17

/dump1

/var14

/var/hsc/log25

/run/user/6010

/run/user/6041

/run/user/6000

Expected Output from Statistics (Each item into separate row): 

----------------------------------------------------------------

hmc_name hmc_info{}.Filesystem hmc_info{}.fs_utilization

labhmc        /dev 1

labhmc /dev/shm 3

labhmc /run 0

labhmc /sys/fs/cgroup 0

labhmc / 46

labhmc /data 2

labhmc /home 4

labhmc /extra 17

labhmc /dump 1

labhmc /var 14

labhmc /var/hsc/log 25

labhmc /run/user/601 0

labhmc /run/user/604 1

labhmc /run/user/600 0

[yuanliu · Accepted solution]

As @richgalloway points out, the "Splunk Event" illustrated in OP is not raw event at all, as it contains no hmc_info{}.  My suspicion is that those "events" represents elements in the JSON array hmc_info{}, which you renamed to LIST.  I further speculate that your requirement is to have each element in this LIST to be its own row. (These really should be explained upfront in the question and not make volunteers play mind reading game because the result is often wrong and a waste of everyone's time.)

Taking these to speculations together, you should run mvexpand on LIST aka hmc_info{}, not its subnodes.

index=lab_env host=labhmc earliest=-4h latest=now
| spath path=hmc_info{} output=LIST
``` kv is not applicable; stick with JSON is the best strategy ```
| mvexpand LIST ``` run mvexpand on the array itself, not its subnodes ```
| spath input=LIST ``` after spath, hms_filesystem, fs_utilization, etc., are top nodes, no longer subnodes of hmc_info{} ```
| where category == "hmc_filesystem" AND tonumber(replace(fs_utilization, "%", "")) > 40
| table hmc_name Filesystem fs_utilization

The sample array would give

hmc_name	Filesystem	fs_utilization
labhmc	/	46%

If I take away the > 46% restriction, you get

hmc_name	Filesystem	fs_utilization
labhmc	/dev	0%
labhmc	/dev/shm	1%
labhmc	/run	3%
labhmc	/sys/fs/cgroup	0%
labhmc	/	46%
labhmc	/data	2%
labhmc	/home	4%
labhmc	/extra	17%
labhmc	/dump	1%
labhmc	/var	14%
labhmc	/var/hsc/log	25%
labhmc	/run/user/601	0%
labhmc	/run/user/604	1%
labhmc	/run/user/600	0%

Is this about right?

[satish]

@yuanliu 

Thank you for the solution as per my expectation.

[richgalloway]

Thanks for the sample output.  I tried using your original query with the example data, but it failed because there is no hmc_info field in the data.  Please edit the OP to make the data usable with the query.

Also, is the data a single event or multiple events?