data extraction from Nested JSON data and print only corresponding details

[bala1185]

H Team, 

Am trying to fetch the nicSwitch* details of only corresponding nicName from the below json data, which i could not able to achieve. Help is appreciated!

raw json data to extract the network switch details of each nicName

{
"hostname": "abc",
"inventory": "#####",
"fqdn": "xxxxx.xxxx.xxx.xxx.xxx",
"ip": "#.#.#.#",
"platform": "XXXXX",
"version": "XXXXX",
"environment": "XXXX",
"status": "XXXXX",
"subStatus": "XXXXX",
"contactSupporTeam": "xxxx",
"model": "XXXXX",
"product": "SERVER",
"serial": "dfd34324",
"app": [{
"appName": "XXXXX",
"appAcronym": "XXX",
"appStatus": "xxxxx",
"appOwner": "xxxxxx"
}],
"pkg": [{
"pkgName": "xxxxx",
"pkgVersion": "1.2.3"
}, {
"pkgName": "yyyyy",
"pkgVersion": "2.3.4"
}, {
"pkgName": "zzzzz",
"pkgVersion": "3.4.5"
}],
"nic": [{
"nicName": "eth4",
"nicSwitch": [{
"nicSwitchName": "xxxxxxx",
"nicSwitchSerial": "dfgdg45435fgg",
"nicSwitchManufacturer": "XXXX",
"nicSwitchModel": "XXX22",
"nicSwitchVlan": "Vlan###",
"nicSwitchChannel": "port-channel3",
"nicSwitchPort": "Ethernet107/1/7"
}, {
"nicSwitchName": "xxxxxxxx",
"nicSwitchSerial": "dfsf23432ef",
"nicSwitchManufacturer": "XXXX",
"nicSwitchModel": "XXXX",
"nicSwitchChannel": "port-channel3",
"nicSwitchPort": "Ethernet107/1/8",
"nicSwitchVlan": "Vlan###"
}],
"nicDnsName": "",
"nicType": null,
"nicStatus": "up",
"nicSpeed": "10000",
"nicFirmware": "",
"nicMac": "XX##XXX###XX",
"nicDuplex": "FULL",
"nicIP": "undefined",
"nicNetmask": ""
}, {
"nicName": "eth5",
"nicSwitch": [{
"nicSwitchName": "xxxxxx",
"nicSwitchSerial": "dsfsdf3432sdf",
"nicSwitchManufacturer": "XXXX",
"nicSwitchModel": "XXXXX",
"nicSwitchChannel": "port-channel3",
"nicSwitchVlan": "Vlan###",
"nicSwitchPort": "Ethernet107/1/8"
}, {
"nicSwitchName": "xxxxxx",
"nicSwitchSerial": "fdf345345",
"nicSwitchManufacturer": "XXXXX",
"nicSwitchModel": "XXXXX",
"nicSwitchChannel": "port-channel3",
"nicSwitchPort": "Ethernet107/1/7",
"nicSwitchVlan": "Vlan###"
}],
"nicDnsName": "",
"nicType": null,
"nicStatus": "up",
"nicSpeed": "",
"nicFirmware": "",
"nicMac": "XXX###XXX",
"nicDuplex": "",
"nicIP": "undefined",
"nicNetmask": ""
}, {
"nicName": "eth6",
"nicSwitch": [],
"nicDnsName": "",
"nicType": null,
"nicStatus": "",
"nicSpeed": "",
"nicFirmware": "",
"nicMac": "",
"nicDuplex": "",
"nicIP": "#.#.#.#",
"nicNetmask": "#.#.#.#"
}]
}

[to4kawa]

| makeresults
| eval _raw="{\"hostname\":\"abc\",\"inventory\":\"#####\",\"fqdn\":\"xxxxx.xxxx.xxx.xxx.xxx\",\"ip\":\"#.#.#.#\",\"platform\":\"XXXXX\",\"version\":\"XXXXX\",\"environment\":\"XXXX\",\"status\":\"XXXXX\",\"subStatus\":\"XXXXX\",\"contactSupporTeam\":\"xxxx\",\"model\":\"XXXXX\",\"product\":\"SERVER\",\"serial\":\"dfd34324\",\"app\":[{\"appName\":\"XXXXX\",\"appAcronym\":\"XXX\",\"appStatus\":\"xxxxx\",\"appOwner\":\"xxxxxx\"}],\"pkg\":[{\"pkgName\":\"xxxxx\",\"pkgVersion\":\"1.2.3\"},{\"pkgName\":\"yyyyy\",\"pkgVersion\":\"2.3.4\"},{\"pkgName\":\"zzzzz\",\"pkgVersion\":\"3.4.5\"}],\"nic\":[{\"nicName\":\"eth4\",\"nicSwitch\":[{\"nicSwitchName\":\"xxxxxxx\",\"nicSwitchSerial\":\"dfgdg45435fgg\",\"nicSwitchManufacturer\":\"XXXX\",\"nicSwitchModel\":\"XXX22\",\"nicSwitchVlan\":\"Vlan###\",\"nicSwitchChannel\":\"port-channel3\",\"nicSwitchPort\":\"Ethernet107/1/7\"},{\"nicSwitchName\":\"xxxxxxxx\",\"nicSwitchSerial\":\"dfsf23432ef\",\"nicSwitchManufacturer\":\"XXXX\",\"nicSwitchModel\":\"XXXX\",\"nicSwitchChannel\":\"port-channel3\",\"nicSwitchPort\":\"Ethernet107/1/8\",\"nicSwitchVlan\":\"Vlan###\"}],\"nicDnsName\":\"\",\"nicType\":null,\"nicStatus\":\"up\",\"nicSpeed\":\"10000\",\"nicFirmware\":\"\",\"nicMac\":\"XX##XXX###XX\",\"nicDuplex\":\"FULL\",\"nicIP\":\"undefined\",\"nicNetmask\":\"\"},{\"nicName\":\"eth5\",\"nicSwitch\":[{\"nicSwitchName\":\"xxxxxx\",\"nicSwitchSerial\":\"dsfsdf3432sdf\",\"nicSwitchManufacturer\":\"XXXX\",\"nicSwitchModel\":\"XXXXX\",\"nicSwitchChannel\":\"port-channel3\",\"nicSwitchVlan\":\"Vlan###\",\"nicSwitchPort\":\"Ethernet107/1/8\"},{\"nicSwitchName\":\"xxxxxx\",\"nicSwitchSerial\":\"fdf345345\",\"nicSwitchManufacturer\":\"XXXXX\",\"nicSwitchModel\":\"XXXXX\",\"nicSwitchChannel\":\"port-channel3\",\"nicSwitchPort\":\"Ethernet107/1/7\",\"nicSwitchVlan\":\"Vlan###\"}],\"nicDnsName\":\"\",\"nicType\":null,\"nicStatus\":\"up\",\"nicSpeed\":\"\",\"nicFirmware\":\"\",\"nicMac\":\"XXX###XXX\",\"nicDuplex\":\"\",\"nicIP\":\"undefined\",\"nicNetmask\":\"\"},{\"nicName\":\"eth6\",\"nicSwitch\":[],\"nicDnsName\":\"\",\"nicType\":null,\"nicStatus\":\"\",\"nicSpeed\":\"\",\"nicFirmware\":\"\",\"nicMac\":\"\",\"nicDuplex\":\"\",\"nicIP\":\"#.#.#.#\",\"nicNetmask\":\"#.#.#.#\"}]}"
| spath nic{} output=nic
| stats count by nic
| spath input=nic
| fields - nic count

[bala1185]

thanks for looking into it. it is printing multivalues in each rows, if each nic has multiple switches.

i got help from someone and got it worked below.

i got it worked well with the below:

| makeresults
| eval _raw="{\"hostname\": \"xxxxx\",\"inventory\": \"#####\",\"fqdn\": \"xxxxx.xxxx.xxx.xxx.xxx\",\"ip\": \"#.#.#.#\",\"platform\": \"XXXXX\",\"version\": \"XXXXX\",\"environment\": \"XXXX\",\"status\": \"XXXXX\",\"subStatus\": \"XXXXX\",\"contactSupporTeam\": \"xxxx\",\"model\": \"XXXXX\",\"product\": \"SERVER\",\"serial\": \"dfd34324\",\"app\": [{\"appName\": \"XXXXX\",\"appAcronym\": \"XXX\",\"appStatus\": \"xxxxx\",\"appOwner\": \"xxxxxx\"}],\"pkg\": [{\"pkgName\": \"xxxxx\",\"pkgVersion\": \"1.2.3\"}, {\"pkgName\": \"yyyyy\",\"pkgVersion\": \"2.3.4\"}, {\"pkgName\": \"zzzzz\",\"pkgVersion\": \"3.4.5\"}],\"nic\": [{\"nicName\": \"eth4\",\"nicSwitch\": [{\"nicSwitchName\": \"xxxxxxx\",\"nicSwitchSerial\": \"dfgdg45435fgg\",\"nicSwitchManufacturer\": \"XXXX\",\"nicSwitchModel\": \"XXX22\",\"nicSwitchVlan\": \"Vlan###\",\"nicSwitchChannel\": \"port-channel3\",\"nicSwitchPort\": \"Ethernet107/1/7\"}, {\"nicSwitchName\": \"xxxxxxxx\",\"nicSwitchSerial\": \"dfsf23432ef\",\"nicSwitchManufacturer\": \"XXXX\",\"nicSwitchModel\": \"XXXX\",\"nicSwitchChannel\": \"port-channel3\",\"nicSwitchPort\": \"Ethernet107/1/8\",\"nicSwitchVlan\": \"Vlan###\"}],\"nicDnsName\": \"\",\"nicType\": null,\"nicStatus\": \"up\",\"nicSpeed\": \"10000\",\"nicFirmware\": \"\",\"nicMac\": \"XX##XXX###XX\",\"nicDuplex\": \"FULL\",\"nicIP\": \"undefined\",\"nicNetmask\": \"\"}, {\"nicName\": \"eth5\",\"nicSwitch\": [{\"nicSwitchName\": \"xxxxxx\",\"nicSwitchSerial\": \"dsfsdf3432sdf\",\"nicSwitchManufacturer\": \"XXXX\",\"nicSwitchModel\": \"XXXXX\",\"nicSwitchChannel\": \"port-channel3\",\"nicSwitchVlan\": \"Vlan###\",\"nicSwitchPort\": \"Ethernet107/1/8\"}, {\"nicSwitchName\": \"xxxxxx\",\"nicSwitchSerial\": \"fdf345345\",\"nicSwitchManufacturer\": \"XXXXX\",\"nicSwitchModel\": \"XXXXX\",\"nicSwitchChannel\": \"port-channel3\",\"nicSwitchPort\": \"Ethernet107/1/7\",\"nicSwitchVlan\": \"Vlan###\"}],\"nicDnsName\": \"\",\"nicType\": null,\"nicStatus\": \"up\",\"nicSpeed\": \"\",\"nicFirmware\": \"\",\"nicMac\": \"XXX###XXX\",\"nicDuplex\": \"\",\"nicIP\": \"undefined\",\"nicNetmask\": \"\"}, {\"nicName\": \"eth6\",\"nicSwitch\": [],\"nicDnsName\": \"\",\"nicType\": null,\"nicStatus\": \"\",\"nicSpeed\": \"\",\"nicFirmware\": \"\",\"nicMac\": \"\",\"nicDuplex\": \"\",\"nicIP\": \"#.#.#.#\",\"nicNetmask\": \"#.#.#.#\"}]}"
| spath nic{} output=nic | stats count by nic | rename nic as _raw
| extract | spath nicSwitch{} output=nic | stats count by nicName,nic | rename nic as _raw | extract | fields nicName nic*
| fields - _raw

Now,  i would like to fetch the OS log with below query
index="linux-os" source="tcp:1234" log_source="varlog-messages" "Link is Down"
| rex field=_raw "(?<NICDevice>[\w]{3,7})(: NIC|: Link)" |table hostname, message, NICDeice

the o/p will be like below :

1	abcd	Jul 24 05:46:53 abcd kernel: [ 26.340634] ixgbe 0000:0b:00.0: eth0: NIC Link is Down	eth0
2	efgh	Jul 24 04:20:04 efgh kernel: ixgbe 0000:0b:00.1 ens2f1: NIC Link is Down	ens2f1
3	ijkl	Jul 24 01:02:31 ijkl kernel: vmxnet3 0000:03:00.0 eth0: NIC Link is Down	eth0
4	ijkl	Jul 24 01:02:27 ijkl kernel: vmxnet3 0000:03:00.0 eth0: NIC Link is Down	eth0

i would like to fetch hostname and NICDevice from this output and correlate this hostname and NICDevice with the initial query that you have given and fetch the details of nicSwitch* and also other details like app, location, etc...

seems join is taking too much time and also found that, if a server has the NICDevice reported as down and if that NICDevice is not existing as nicName in the initial index.. the other details like, app, location details also not fetching.

[kamlesh_vaghela · Accepted solution]

| makeresults 
| eval _raw="{\"hostname\":\"abc\",\"inventory\":\"#####\",\"fqdn\":\"xxxxx.xxxx.xxx.xxx.xxx\",\"ip\":\"#.#.#.#\",\"platform\":\"XXXXX\",\"version\":\"XXXXX\",\"environment\":\"XXXX\",\"status\":\"XXXXX\",\"subStatus\":\"XXXXX\",\"contactSupporTeam\":\"xxxx\",\"model\":\"XXXXX\",\"product\":\"SERVER\",\"serial\":\"dfd34324\",\"app\":[{\"appName\":\"XXXXX\",\"appAcronym\":\"XXX\",\"appStatus\":\"xxxxx\",\"appOwner\":\"xxxxxx\"}],\"pkg\":[{\"pkgName\":\"xxxxx\",\"pkgVersion\":\"1.2.3\"},{\"pkgName\":\"yyyyy\",\"pkgVersion\":\"2.3.4\"},{\"pkgName\":\"zzzzz\",\"pkgVersion\":\"3.4.5\"}],\"nic\":[{\"nicName\":\"eth4\",\"nicSwitch\":[{\"nicSwitchName\":\"xxxxxxx\",\"nicSwitchSerial\":\"dfgdg45435fgg\",\"nicSwitchManufacturer\":\"XXXX\",\"nicSwitchModel\":\"XXX22\",\"nicSwitchVlan\":\"Vlan###\",\"nicSwitchChannel\":\"port-channel3\",\"nicSwitchPort\":\"Ethernet107/1/7\"},{\"nicSwitchName\":\"xxxxxxxx\",\"nicSwitchSerial\":\"dfsf23432ef\",\"nicSwitchManufacturer\":\"XXXX\",\"nicSwitchModel\":\"XXXX\",\"nicSwitchChannel\":\"port-channel3\",\"nicSwitchPort\":\"Ethernet107/1/8\",\"nicSwitchVlan\":\"Vlan###\"}],\"nicDnsName\":\"\",\"nicType\":null,\"nicStatus\":\"up\",\"nicSpeed\":\"10000\",\"nicFirmware\":\"\",\"nicMac\":\"XX##XXX###XX\",\"nicDuplex\":\"FULL\",\"nicIP\":\"undefined\",\"nicNetmask\":\"\"},{\"nicName\":\"eth5\",\"nicSwitch\":[{\"nicSwitchName\":\"xxxxxx\",\"nicSwitchSerial\":\"dsfsdf3432sdf\",\"nicSwitchManufacturer\":\"XXXX\",\"nicSwitchModel\":\"XXXXX\",\"nicSwitchChannel\":\"port-channel3\",\"nicSwitchVlan\":\"Vlan###\",\"nicSwitchPort\":\"Ethernet107/1/8\"},{\"nicSwitchName\":\"xxxxxx\",\"nicSwitchSerial\":\"fdf345345\",\"nicSwitchManufacturer\":\"XXXXX\",\"nicSwitchModel\":\"XXXXX\",\"nicSwitchChannel\":\"port-channel3\",\"nicSwitchPort\":\"Ethernet107/1/7\",\"nicSwitchVlan\":\"Vlan###\"}],\"nicDnsName\":\"\",\"nicType\":null,\"nicStatus\":\"up\",\"nicSpeed\":\"\",\"nicFirmware\":\"\",\"nicMac\":\"XXX###XXX\",\"nicDuplex\":\"\",\"nicIP\":\"undefined\",\"nicNetmask\":\"\"},{\"nicName\":\"eth6\",\"nicSwitch\":[],\"nicDnsName\":\"\",\"nicType\":null,\"nicStatus\":\"\",\"nicSpeed\":\"\",\"nicFirmware\":\"\",\"nicMac\":\"\",\"nicDuplex\":\"\",\"nicIP\":\"#.#.#.#\",\"nicNetmask\":\"#.#.#.#\"}]}" 
| spath nic{} output=data 
| stats count by data | rename data as _raw | extract | spath nicSwitch{} output=data | stats count by nicName,data | rename data as _raw | extract | fields nicName nic*