Splunk Enterprise

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

nkhanna
Engager

Screenshot 2024-10-31 at 9.27.05 AM.png

 

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

 



Labels (1)
Tags (3)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

 

0 Karma

nkhanna
Engager

@PickleRick - Can you please share sample syntax?

0 Karma

PickleRick
SplunkTrust
SplunkTrust
| spath input=json output=device audit.result.devices{}
| mvexpand device
| spath input=device whatever.whatever
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

nkhanna
Engager
"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"}}
0 Karma

nkhanna
Engager

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

0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...