All Apps and Add-ons

Splitting JSON Respone into Rows for each event - Akamai Data

dzillner
New Member

I'm using the REST API app and I'm having a bit of trouble figuring out the response handlers and/or Splunk dealing with JSON data.

I have a very large JSON file that gets returned, and I want to create a entry for each row in the data, but the data isn't paired up directly like in most examples I've seen in the help sections.

Here's an example of the data

  {
        "columns": [
            {
                "type": "dimension",
                "name": "Time",
                "description": "Time as per time zone specified in the report",
                "index": 0
            },
            {
                "type": "dimension",
                "name": "Country",
                "description": "Country from which viewer requested media ",
                "index": 1
            },
            {
                "type": "metric",
                "name": "Plays with Rebuffers",
                "description": "Number of Plays with Rebuffers",
                "index": 2,
                "aggregate": "418",
                "peak": "156",
                "unit": null
            }
        ],
        "rows": [
            [
                "1415837040",
                "US"
                "156"
            ],
            [
                "1415837040",
                "CH"
                "136"
            ],
            [
                "1415836980",
                "US"
                "113"
            ],
            [
                "1415836980",
                "CH"
                "103"
            ],
            [
                "1415836920",
                "US"
                "95"
            ],
        ],
        "metaData": {
            "aggregation": 60,
            "limit": 5,
            "startTimeInEpoch": 1415836800,
            "hasMoreData": false,
            "timeZone": "GMT",
            "offset": 0,
            "reportPack": "HDS Qos Test",
            "endTimeInEpoch": 1415837100
        }
    }

At the End of the day I want to end up with the data for each row just pulling the name field out of the columns, and discarding the metadata

Time=1415837040 Country=US "Plays with Rebuffers"=156
Time=1415837040 Country=CH "Plays with Rebuffers"=136
ETC

I also need to make the _time for the entry the same as the time returned in the row. It looks like I need to do something like this in my props file for my source type, but that won't work until I get the parsing by row working.
TIME_PREFIX = Time=
TIME_FORMAT = %s

Thanks in advance for your time for anyone looking at this or responding.

0 Karma
1 Solution

Damien_Dallimor
Ultra Champion

Try this code in responsehandlers.py

class YourJSONArrayHandler:

    def __init__(self,**args):
        pass

    def __call__(self, response_object,raw_response_output,response_type,req_args,endpoint):
        if response_type == "json":
            raw_json = json.loads(raw_response_output)
            column_list = []
            for column in raw_json['columns']:
                column_list.append(column['name'])
            for row in raw_json['rows']:
                i = 0;
                new_event = {}
                for row_item in row:          
                    new_event[column_list[i]] = row_item
                    i = i+1
                print print_xml_stream(json.dumps(new_event))

        else:
            print_xml_stream(raw_response_output) 

And wire it up in your stanza config :

alt text

View solution in original post

Damien_Dallimor
Ultra Champion

Try this code in responsehandlers.py

class YourJSONArrayHandler:

    def __init__(self,**args):
        pass

    def __call__(self, response_object,raw_response_output,response_type,req_args,endpoint):
        if response_type == "json":
            raw_json = json.loads(raw_response_output)
            column_list = []
            for column in raw_json['columns']:
                column_list.append(column['name'])
            for row in raw_json['rows']:
                i = 0;
                new_event = {}
                for row_item in row:          
                    new_event[column_list[i]] = row_item
                    i = i+1
                print print_xml_stream(json.dumps(new_event))

        else:
            print_xml_stream(raw_response_output) 

And wire it up in your stanza config :

alt text

dzillner
New Member

Thank you for the response! I had no idea you would answer so quickly.

I had another version of this going, and I combine what you wrote, with what I had going. So far it's working great other than some issues with the data source.

class AkamaiColumnRowsJSONHandler:     
        def __init__(self,**args):
            pass

        def __call__(self, response_object,raw_response_output,response_type,req_args,endpoint):
            if response_type == "json":
                raw_json = json.loads(raw_response_output)
                columns = [str(row['name']).replace(" ","") for row in raw_json['columns']]
                for row in raw_json['rows']:
                    new_event = {}
                    for a,b in zip(columns, row):
                    if not b: #Skip Null Valued Pairs
                        continue
                    new_event[a] = b
                print_xml_stream(json.dumps(new_event))
            else:
                print_xml_stream(raw_response_output)

I'm really glad I was closer than I thought to a viable solution at the time I ran here for help. You and your tool are fantastic sir.

0 Karma
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 ...