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
Super Champion

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
Super Champion

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!

Say goodbye to manually analyzing phishing and malware threats with Splunk Attack ...

In today’s evolving threat landscape, we understand you’re constantly bombarded with phishing and malware ...

AppDynamics is now part of Splunk Ideas

Hello Splunkers, We have exciting news for you! AppDynamics has been added to the Splunk Ideas Portal. Which ...

Advanced Splunk Data Management Strategies

Join us on Wednesday, May 14, 2025, at 11 AM PDT / 2 PM EDT for an exclusive Tech Talk that delves into ...