How to use mvexpand in a table where there are multiple columns with different data count?

[nkhanna]

1. I need to fetch data based on deviceMac such that row gets corresponding data from each column.
2. It should fill NA or NULL if there is not corresponding data
3. If you see column Id, you are seeing more data. 
     for example: if deviceMac 90:dd:5d:bf:10:54 is connected to SA91804F4A, then id has 2 values : SA91804F4A and f452465ee7ab but if devicemac d4:54:8b:bd:a1:c8 is connected to f452465ee7ab, then id has 1 value : f452465ee7ab.
But I want to have my output like this:
90:dd:5d:bf:10:54 SA91804F4A ( do not include f452465ee7ab)
d4:54:8b:bd:a1:c8 f452465ee7ab


Splunk query used to get output:

| search
| rex field=_raw "(?msi)(?<json>{.+}$$)" | spath input=json
| spath input=json output=deviceMac audit.result.devices{}.mac
| spath input=json output=deviceName audit.result.devices{}.name
| spath input=json output=status audit.result.devices{}.health{}.status
| spath input=json output=connectionState audit.result.devices{}.connectionState
| spath input=json output=id audit.result.devices{}.leafToRoot{}.id
| eval time=strftime(_time,"%m/%d/%Y %H:%M:%S.%N")
| dedup deviceMac, id
| table time, deviceMac, connectionState, id, deviceName, status

[PickleRick]

There is no such thing as "corresponding value" between different multivalued fields.

In case like yours you need to firstly parse out whole array into a multivalued field of json strings, do mvexpand on that field and only then parse out single fields from those results.

[nkhanna]

@PickleRick - Can you please share sample syntax?

[PickleRick]

| spath input=json output=device audit.result.devices{}
| mvexpand device
| spath input=device whatever.whatever

[ITWhisperer]

It is difficult to advise without seeing your events. Please share some anonymised events which demonstrate the issue. Please share the raw event in a code block (using the </> button above) to preserve formatting.

[nkhanna]

"result":{"devices":[{"mac":"d8:43:ae:40:6a:c3","hostName":"DESKTOP-JDE9R7Q","medium":"ethernet","connectionState":"connected","connectionStateChangeAt":"2024-10-29T04:41:00.811Z","networkId":"default","favorite":false,"bandSteering":{"_version":"1.0.0","enable":false,"auto":true},"clientSteering":{"_version":"1.0.0","enable":true,"auto":true},"qos":{"prioritization":{"mode":"auto","realizedState":"ignore"}},"ip":"192.168.1.228","ipv6":["2600:6c55:7800:6c::107b","2600:6c55:7800:6c:403d:7bf4:2205:7e59","2600:6c55:7800:6c:90be:13dd:83dd:5ea3","fe80::eacd:8953:9d4f:94ab"],"steering":{},"name":"DESKTOP-JDE9R7Q","icon":"unknown","iconV2":"laptop-windows","category":"Laptop","osName":"Windows","health":{"status":"excellent","score":5,"details":null},"leafToRoot":[{"id":"f452465ee7ab","nickname":"f452465ee7ab"}],"alerts":[],"freeze":{"frozen":false,"suspended":{"id":"suspend","name":"Suspend","enable":false},"timeTemplates":[{"id":"untilMidnight","name":"Until End of Day","enable":false},{"id":"schoolNights","name":"School Nights","enable":false,"schedules":[{"times":[{"start":"20:00","end":"06:00"}],"daysOfWeek":[7,1,2,3,4]}]},{"id":"bedTime","name":"Bed Time","enable":false,"schedules":[{"times":[{"start":"20:00","end":"06:00"}],"daysOfWeek":[1,2,3,4,5,6,7]}]},{"id":"forever","name":"Indefinitely","enable":false}],"autoExpire":{"id":"autoExpire","enable":false,"expiresAt":null},"schedules":[],"forever":{"id":"forever","name":"Indefinitely","enable":false}},"locallyAdministeredShifty":false,"locallyAdministeredShiftyExpired":false,"locallyAdministeredMac":false,"accessZone":{"id":0,"type":"home","description":"Home","createdAt":"2024-10-30T16:48:50.189Z","_version":"1.0.0"},"firstConnectedAt":"2024-10-03T21:10:06.244Z","capabilities":{"radio24":false,"radio50":false,"radio60":false},"features":{},"isPartnerComponent":false,"kind":{"id":"windows","type":{"id":"windows","category":"Laptop","name":"Computer","icon":"unknown","iconV2":"laptop-windows","osName":"Windows","osVersion":"10.0","source":"rules","confidence":325},"category":"Laptop","name":"DESKTOP-JDE9R7Q","icon":"unknown","iconV2":"laptop-windows","osName":"Windows","osVersion":"10.0","source":"rules","confidence":325,"typeIdentified":false,"ohpCapable":false},"nickname":null,"plumeTypeIdentified":false,"customerTypeIdentified":false,"ohp":{"capable":false},"wpaMode":"","accessZoneType":"home","quarantine":{"enable":false,"anomalyBlacklist":[],"anomalyWhitelist":[]},"groupOfUnassignedDevices":true,"networkAccess":{"mode":"approved"}},{"mac":"90:dd:5d:d5:a1:2e","keyId":1,"hostName":"Living-Room","medium":"wifi","connectionState":"connected","connectionStateChangeAt":"2024-10-29T04:41:49.071Z","vapType":"home","networkId":"default","favorite":false,"bandSteering":{"_version":"1.0.0","enable":true,"auto":true},"clientSteering":{"_version":"1.0.0","enable":true,"auto":true},"qos":{"prioritization":{"mode":"auto","realizedState":"ignore"}},"ip":"192.168.1.206","ipv6":["2600:6c55:7800:6c:28e2:7128:e736:a0d7","2600:6c55:7800:6c:359c:ccb9:5198:5c58","2600:6c55:7800:6c:84a9:7a98:bdc1:29a5","2600:6c55:7800:6c:91cb:1c60:d7c1:4320","fd00:f452:465e:e7ac:8a9:3740:fe01:8930"],"channel":44,"freqBand":"5GL","steering":{},"name":"Living-Room","icon":"unknown","iconV2":"smartdevice-apple","brand":"Apple","health":{"score":5,"status":"excellent","details":null},"leafToRoot":[{"id":"SA91804F4A","nickname":"SA91804F4A","parentId":"f452465ee7ab","radio":"5GU","channel":157,"medium":"wifi"},{"id":"f452465ee7ab","nickname":"f452465ee7ab"}],"alerts":[],"freeze":{"frozen":false,"suspended":{"id":"suspend","name":"Suspend","enable":false},"timeTemplates":[{"id":"untilMidnight","name":"Until End of Day","enable":false},{"id":"schoolNights","name":"School Nights","enable":false,"schedules":[{"times":[{"start":"20:00","end":"06:00"}],"daysOfWeek":[7,1,2,3,4]}]},{"id":"bedTime","name":"Bed Time","enable":false,"schedules":[{"times":[{"start":"20:00","end":"06:00"}],"daysOfWeek":[1,2,3,4,5,6,7]}]},{"id":"forever","name":"Indefinitely","enable":false}],"autoExpire":{"id":"autoExpire","enable":false,"expiresAt":null},"schedules":[],"forever":{"id":"forever","name":"Indefinitely","enable":false}},"locallyAdministeredShifty":false,"locallyAdministeredShiftyExpired":false,"locallyAdministeredMac":false,"accessZone":{"id":0,"type":"home","description":"Home","createdAt":"2024-10-30T16:48:50.189Z","_version":"1.0.0"},"firstConnectedAt":"2024-10-03T22:44:22.704Z","capabilities":{"radio24":true,"radio50":true,"radio60":false},"features":{},"isPartnerComponent":false,"kind":{"id":"apple","type":{"id":"apple","brand":"Apple","icon":"unknown","iconV2":"smartdevice-apple","source":"rules","confidence":100},"brand":"Apple","name":"Living-Room","icon":"unknown","iconV2":"smartdevice-apple","source":"rules","confidence":100,"typeIdentified":false,"ohpCapable":false},"nickname":null,"plumeTypeIdentified":false,"customerTypeIdentified":false,"ohp":{"capable":false},"wpaMode":"psk2","accessZoneType":"home","quarantine":{"enable":false,"anomalyBlacklist":[],"anomalyWhitelist":[]},"groupOfUnassignedDevices":true,"networkAccess":{"mode":"auto"}},{"mac":"48:27:e2:ec:1c:24","keyId":1,"hostName":"espressif","medium":"wifi","connectionState":"connected","connectionStateChangeAt":"2024-10-29T04:41:56.063Z","vapType":"home","networkId":"default","favorite":false,"bandSteering":{"_version":"1.0.0","enable":false,"auto":true},"clientSteering":{"_version":"1.0.0","enable":true,"auto":true},"qos":{"prioritization":{"mode":"auto","realizedState":"ignore"}},"ip":"192.168.1.70","ipv6":[],"channel":1,"freqBand":"2.4G","steering":{},"name":"Espressif","icon":"unknown","iconV2":"iotplatform-espressif","category":"IoT Platform","brand":"Espressif","health":{"score":5,"status":"excellent","details":null},"leafToRoot":[{"id":"f452465ee7ab","nickname":"f452465ee7ab"}],"alerts":[],"freeze":{"frozen":false,"suspended":{"id":"suspend","name":"Suspend","enable":false},"timeTemplates":[{"id":"untilMidnight","name":"Until End of Day","enable":false},{"id":"schoolNights","name":"School Nights","enable":false,"schedules":[{"times":[{"start":"20:00","end":"06:00"}],"daysOfWeek":[7,1,2,3,4]}]},{"id":"bedTime","name":"Bed Time","enable":false,"schedules":[{"times":[{"start":"20:00","end":"06:00"}],"daysOfWeek":[1,2,3,4,5,6,7]}]},{"id":"forever","name":"Indefinitely","enable":false}],"autoExpire":{"id":"autoExpire","enable":false,"expiresAt":null},"schedules":[],"forever":{"id":"forever","name":"Indefinitely","enable":false}},"locallyAdministeredShifty":false,"locallyAdministeredShiftyExpired":false,"locallyAdministeredMac":false,"accessZone":{"id":0,"type":"home","description":"Home","createdAt":"2024-10-30T16:48:50.189Z","_version":"1.0.0"},"firstConnectedAt":"2024-10-03T22:44:07.254Z","capabilities":{"radio24":true,"radio50":false,"radio60":false},"features":{},"isPartnerComponent":false,"kind":{"id":"espressif-iotplatform","type":{"id":"espressif-iotplatform","category":"IoT Platform","brand":"Espressif","icon":"unknown","iconV2":"iotplatform-espressif","source":"rules","confidence":20},"category":"IoT Platform","brand":"Espressif","name":"Espressif","icon":"unknown","iconV2":"iotplatform-espressif","source":"rules","confidence":20,"typeIdentified":false,"ohpCapable":false},"nickname":null,"plumeTypeIdentified":false,"customerTypeIdentified":false,"ohp":{"capable":false},"wpaMode":"psk2","accessZoneType":"home","quarantine":{"enable":false,"anomalyBlacklist":[],"anomalyWhitelist":[]},"groupOfUnassignedDevices":true,"networkAccess":{"mode":"auto"}},{"mac":"c0:48:e6:a5:a5:7b","keyId":1,"hostName":"Samsung-33","medium":"wifi","connectionState":"connected","connectionStateChangeAt":"2024-10-29T04:42:00.362Z","vapType":"home","networkId":"default","favorite":false,"bandSteering":{"_version":"1.0.0","enable":false,"auto":true},"clientSteering":{"_version":"1.0.0","enable":true,"auto":true},"qos":{"prioritization":{"mode":"auto","realizedState":"ignore"}},"ip":"192.168.1.209","ipv6":["2600:6c55:7800:6c::145d","fe80::c248:e6ff:fea5:a57b"],"channel":6,"freqBand":"2.4G","steering":{},"name":"Samsung","icon":"unknown","iconV2":"smartdevice-samsung","brand":"Samsung","health":{"score":5,"status":"excellent","details":null},"leafToRoot":[{"id":"SA91804F4A","nickname":"SA91804F4A","parentId":"f452465ee7ab","radio":"5GU","channel":157,"medium":"wifi"},{"id":"f452465ee7ab","nickname":"f452465ee7ab"}],"alerts":[],"freeze":{"frozen":false,"suspended":{"id":"suspend","name":"Suspend","enable":false},"timeTemplates":[{"id":"untilMidnight","name":"Until End of Day","enable":false},{"id":"schoolNights","name":"School Nights","enable":false,"schedules":[{"times":[{"start":"20:00","end":"06:00"}],"daysOfWeek":[7,1,2,3,4]}]},{"id":"bedTime","name":"Bed Time","enable":false,"schedules":[{"times":[{"start":"20:00","end":"06:00"}],"daysOfWeek":[1,2,3,4,5,6,7]}]},{"id":"forever","name":"Indefinitely","enable":false}],"autoExpire":{"id":"autoExpire","enable":false,"expiresAt":null},"schedules":[],"forever":{"id":"forever","name":"Indefinitely","enable":false}},"locallyAdministeredShifty":false,"locallyAdministeredShiftyExpired":false,"locallyAdministeredMac":false,"accessZone":{"id":0,"type":"home","description":"Home","createdAt":"2024-10-30T16:48:50.189Z","_version":"1.0.0"},"firstConnectedAt":"2024-10-04T05:46:20.274Z","capabilities":{"radio24":true,"radio50":false,"radio60":false},"features":{},"isPartnerComponent":false,"kind":{"id":"samsung","type":{"id":"samsung","brand":"Samsung","icon":"unknown","iconV2":"smartdevice-samsung","source":"rules","confidence":100},"brand":"Samsung","name":"Samsung","icon":"unknown","iconV2":"smartdevice-samsung","source":"rules","confidence":100,"typeIdentified":false,"ohpCapable":false},"nickname":null,"plumeTypeIdentified":false,"customerTypeIdentified":false,"ohp":{"capable":false},"wpaMode":"psk2","accessZoneType":"home","quarantine":{"enable":false,"anomalyBlacklist":[],"anomalyWhitelist":[]},"groupOfUnassignedDevices":true,"networkAccess":{"mode":"auto"}},{"mac":"54:3a:d6:5a:4a:38","keyId":1,"hostName":"Samsung-29","medium":"wifi","connectionState":"connected","connectionStateChangeAt":"2024-10-29T04:46:53.982Z","vapType":"home","networkId":"default","favorite":false,"bandSteering":{"_version":"1.0.0","enable":true,"auto":true},"clientSteering":{"_version":"1.0.0","enable":true,"auto":true},"qos":{"prioritization":{"mode":"auto","realizedState":"ignore"}},"ip":"192.168.1.125","ipv6":["2600:6c55:7800:6c::18c6","fe80::563a:d6ff:fe5a:4a38"],"channel":157,"freqBand":"5G","steering":{},"name":"Samsung","icon":"unknown","iconV2":"smartdevice-samsung","brand":"Samsung","health":{"score":5,"status":"excellent","details":null},"leafToRoot":[{"id":"f452465ee7ab","nickname":"f452465ee7ab"}],"alerts":[],"freeze":{"frozen":false,"suspended":{"id":"suspend","name":"Suspend","enable":false},"timeTemplates":[{"id":"untilMidnight","name":"Until End of Day","enable":false},{"id":"schoolNights","name":"School Nights","enable":false,"schedules":[{"times":[{"start":"20:00","end":"06:00"}],"daysOfWeek":[7,1,2,3,4]}]},{"id":"bedTime","name":"Bed Time","enable":false,"schedules":[{"times":[{"start":"20:00","end":"06:00"}],"daysOfWeek":[1,2,3,4,5,6,7]}]},{"id":"forever","name":"Indefinitely","enable":false}],"autoExpire":{"id":"autoExpire","enable":false,"expiresAt":null},"schedules":[],"forever":{"id":"forever","name":"Indefinitely","enable":false}},"locallyAdministeredShifty":false,"locallyAdministeredShiftyExpired":false,"locallyAdministeredMac":false,"accessZone":{"id":0,"type":"home","description":"Home","createdAt":"2024-10-30T16:48:50.189Z","_version":"1.0.0"},"firstConnectedAt":"2024-10-03T22:44:37.294Z","capabilities":{"radio24":true,"radio50":true,"radio60":false},"features":{},"isPartnerComponent":false,"kind":{"id":"samsung","type":{"id":"samsung","brand":"Samsung","icon":"unknown","iconV2":"smartdevice-samsung","source":"rules","confidence":100},"brand":"Samsung","name":"Samsung","icon":"unknown","iconV2":"smartdevice-samsung","source":"rules","confidence":100,"typeIdentified":false,"ohpCapable":false},"nickname":null,"plumeTypeIdentified":false,"customerTypeIdentified":false,"ohp":{"capable":false},"wpaMode":"psk2","accessZoneType":"home","quarantine":{"enable":false,"anomalyBlacklist":[],"anomalyWhitelist":[]},"groupOfUnassignedDevices":true,"networkAccess":{"mode":"auto"}}

[nkhanna]

@ITWhisperer  I couldn't paste the whole JSON so here is the sample.