Getting Data In

How to parse JSON data at search-time?

sdaruna
Explorer

I am getting different types of data from source. It can be XML or JSON.

For XML, I am just indexing whole file and later at search-time, I am using xmlkv + xpath to parse and get the data that I want.

For JSON, I need to index whole file, but is there a way that I can parse at search time similar to the above.?

javiergn
SplunkTrust
SplunkTrust

Hi,

Spath is your friend if you want automatic field extraction during search time for both XML and JSON type of data:

http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Spath

sdaruna
Explorer

Hi Javier,

I cannot specify any format while indexing data. for xml, if i specify xmlkv and use spath, it works fine. but, i am not sure about the json.

0 Karma

Jeremiah
Motivator

What do you mean you can't specify the format? Is each file a valid json file? Or does it contain individual json events?

0 Karma

sdaruna
Explorer

As said earlier, i can get xml file or json file. While indexing the data, i just need to load whole file. Because, end users need to see whole file.

But, our processing framework needs splitted data.

I have json as below.

{
    "Document": {
        "-xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
        "-xsi:noNamespaceSchemaLocation": "EPA_GEODATA_v1.0.xsd"
    },
    "FacilitySite": [{
        "LatitudeMeasure": "31.59",
        "LongitudeMeasure": "-85.278333",
        "Program": {
            "ProgramCommonName": "TRIS",
            "ProgramAcronymName": "TRIS"
        }
    }, {
        "LocalityName": "ABBEVILLE",
        "LocationAddressStateCode": "AL",
        "Program": {
            "ProgramCommonName": "TRIS",
            "ProgramAcronymName": "TRIS"
        }
    }]
}

can i get child json document as below.

"Program": {
             "ProgramCommonName": "TRIS",
             "ProgramAcronymName": "TRIS"
         }
-----------------------------
"Program": {
             "ProgramCommonName": "TRIS",
             "ProgramAcronymName": "TRIS"
         }
0 Karma

javiergn
SplunkTrust
SplunkTrust

Hi,

Run the following query from Splunk and let me know if this is what you are looking for.
If so, then simply apply the same logic to your events.

| stats count
| eval jsonIn = "
{
     \"Document\": {
         \"-xmlns:xsi\": \"http://www.w3.org/2001/XMLSchema-instance\",
         \"-xsi:noNamespaceSchemaLocation\": \"EPA_GEODATA_v1.0.xsd\"
     },
     \"FacilitySite\": [{
         \"LatitudeMeasure\": \"31.59\",
         \"LongitudeMeasure\": \"-85.278333\",
         \"Program\": {
             \"ProgramCommonName\": \"TRIS\",
             \"ProgramAcronymName\": \"TRIS\"
         }
     }, {
         \"LocalityName\": \"ABBEVILLE\",
         \"LocationAddressStateCode\": \"AL\",
         \"Program\": {
             \"ProgramCommonName\": \"TRIS\",
             \"ProgramAcronymName\": \"TRIS\"
         }
     }]
 }
"
| spath input=jsonIn path=FacilitySite{}.Program output=Program
| fields Program

Output:

Program
{ "ProgramCommonName": "TRIS", "ProgramAcronymName": "TRIS" }
{ "ProgramCommonName": "TRIS", "ProgramAcronymName": "TRIS" } 
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...