topic Re: How to parse JSON data at search-time? in Getting Data In

How to parse JSON data at search-time?

sdaruna — Wed, 10 Feb 2016 20:22:54 GMT

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.?

Re: How to parse JSON data at search-time?

javiergn — Wed, 10 Feb 2016 20:36:33 GMT

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

Re: How to parse JSON data at search-time?

sdaruna — Wed, 10 Feb 2016 20:55:32 GMT

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.

Re: How to parse JSON data at search-time?

Jeremiah — Wed, 10 Feb 2016 21:44:41 GMT

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

Re: How to parse JSON data at search-time?

sdaruna — Wed, 10 Feb 2016 21:53:27 GMT

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

Re: How to parse JSON data at search-time?

javiergn — Wed, 10 Feb 2016 22:05:03 GMT

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