Getting Data In

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

litmuspaper
Loves-to-Learn Lots

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

woodcock
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

prakash007
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

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...