Please help me!
I have indexed JSON data, but I cannot extract the data as I want.
Below is the raw data.
{
"username": "nsroot",
"resourceName": "",
"ns": [
{
"network_interfaces": [
{
"port_name": "LA/1"
},
{
"port_name": "10/2"
},
{
"port_name": "10/1"
}
],
"ip_address": "172.16.30.131",
"instance_state": "Up"
},
{
"network_interfaces": [
{
"port_name": "10/2"
},
{
"port_name": "LA/1"
},
{
"port_name": "10/1"
}
],
"ip_address": "172.16.30.81",
"instance_state": "Up"
},
{
"network_interfaces": [
{
"port_name": "10/1"
},
{
"port_name": "10/16"
},
{
"port_name": "LA/8"
},
{
"port_name": "LA/1"
},
{
"port_name": "10/2"
},
{
"port_name": "10/15"
}
],
"ip_address": "172.16.30.181",
"instance_state": "Up"
}
],
"errorcode": 0,
"operation": "get",
"resourceType": "ns",
"tenant_name": "Owner",
"message": "Done"
}
I want to make the following result from the original data, but I do not know how to write the query.
Help me, Please!
Try this:
|makeresults | eval _raw="{
\"username\": \"nsroot\",
\"resourceName\": \"\",
\"ns\": [
{
\"network_interfaces\": [
{
\"port_name\": \"LA/1\"
},
{
\"port_name\": \"10/2\"
},
{
\"port_name\": \"10/1\"
}
],
\"ip_address\": \"172.16.30.131\",
\"instance_state\": \"Up\"
},
{
\"network_interfaces\": [
{
\"port_name\": \"10/2\"
},
{
\"port_name\": \"LA/1\"
},
{
\"port_name\": \"10/1\"
}
],
\"ip_address\": \"172.16.30.81\",
\"instance_state\": \"Up\"
},
{
\"network_interfaces\": [
{
\"port_name\": \"10/1\"
},
{
\"port_name\": \"10/16\"
},
{
\"port_name\": \"LA/8\"
},
{
\"port_name\": \"LA/1\"
},
{
\"port_name\": \"10/2\"
},
{
\"port_name\": \"10/15\"
}
],
\"ip_address\": \"172.16.30.181\",
\"instance_state\": \"Up\"
}
],
\"errorcode\": 0,
\"operation\": \"get\",
\"resourceType\": \"ns\",
\"tenant_name\": \"Owner\",
\"message\": \"Done\"
}"
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution."
| rex max_match=0 "(?s)(?<ns>\"network_interfaces\":.*?\"instance_state\":\s*[^\r\n]+)"
| table ns
| mvexpand ns
| rename ns AS _raw
| eval _raw = "{" . _raw . "}"
| spath
| rename network_interfaces{}.port_name AS interfaces
| stats values(interfaces) BY ip_address instance_state
By default splunk is extracting this fields off of your Json data, you can try this search...
index="index_name" sourcetype="sourcetype"
| rename "ns{}.ip_address" as ip_address, "ns{}.network_interfaces{}.port_name" as interfaces, "ns{}.instance_state" as instance_state
| stats count by instance_state,ip_address, interfaces
| stats values(instance_state) as instance_state,values(interfaces) as interfaces by ip_address
OR
index="index_name" sourcetype="sourcetype"
| spath
| rename "ns{}.ip_address" as ip_address, "ns{}.network_interfaces{}.port_name" as interfaces, "ns{}.instance_state" as instance_state
| stats count by instance_state,ip_address, interfaces
| stats values(instance_state) as instance_state,values(interfaces) as interfaces by ip_address
you can also add KV_MODE = json on your search-head props.conf