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, yo...
See more...
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