How to parse JSON data at search-time?

[sdaruna]

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]

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]

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.

[Jeremiah]

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

[sdaruna]

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"
         }

[javiergn]

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" }