Getting Data In
Highlighted

Can you help me extract the information I want from the following JSON data (mvzip, mvindex, split)?

New Member

Please help me!

I have indexed JSON data, but I cannot extract the data as I want.
Below is the raw data.

####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!

alt text

0 Karma
Highlighted

Re: Can you help me extract the information I want from the following JSON data (mvzip, mvindex, split)?

Builder

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

Highlighted

Re: Can you help me extract the information I want from the following JSON data (mvzip, mvindex, split)?

Esteemed Legend

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
0 Karma