Splunk Search

How to parse a path request string in json format?

EricMonkeyKing
Explorer

Hi all,

For this sort of json string, how can I extract KeyA, KeyB, KeyC

{ "KeyA": [ { "path": "/attibuteA", "op": "replace", "value": "hello" }, { "path": "/attibuteB", "op": "replace", "value": "hi" } ], "KeyB": [ { "path": "/attibuteA", "op": "replace", "value": "" }, { "path": "/attibuteC", "op": "replace", "value": "hey" }, { "path": "/attibuteD", "op": "replace", "value": "hello" } ], "KeyC": [ { "path": "/attibuteE", "op": "replace", "value": "" } ] }

 

My ideal output would look like:

Keypathopvalue
KeyAattibuteAreplacehello
KeyAattibuteBreplacehi
KeyBattibuteAreplace 
KeyBattibuteCreplacehey
KeyBattibuteDreplacehello
KeycattibuteEreplace 

 

Many thanks^

Labels (2)
0 Karma
1 Solution

dtburrows3
Builder

Think I was able to get the table in the output you are wanting. 

dtburrows3_0-1702480839496.png


I was able to achieve this using this SPL.

| makeresults
    | eval
        json_object="{ \"KeyA\": [ { \"path\": \"/attibuteA\", \"op\": \"replace\", \"value\": \"hello\" }, { \"path\": \"/attibuteB\", \"op\": \"replace\", \"value\": \"hi\" } ], \"KeyB\": [ { \"path\": \"/attibuteA\", \"op\": \"replace\", \"value\": \"\" }, { \"path\": \"/attibuteC\", \"op\": \"replace\", \"value\": \"hey\" }, { \"path\": \"/attibuteD\", \"op\": \"replace\", \"value\": \"hello\" } ], \"KeyC\": [ { \"path\": \"/attibuteE\", \"op\": \"replace\", \"value\": \"\" } ] }"
    | fields - _time
    | fromjson json_object
    | fields - json_object
    ``` Assuming that your fieldset at this point is only the list of fields needed for your final output ```
    ``` If there are other fields present that are not apart of the json object you are trying to parse then you should set up a naming convention for fields youy want to loop through ```
    | foreach *
        [
            | eval
                all_objects=mvappend(
                    'all_objects',
                    case(
                        isnull('<<FIELD>>'), null(),
                        mvcount('<<FIELD>>')==1, json_set('<<FIELD>>', "Key", "<<FIELD>>"),
                        mvcount('<<FIELD>>')>1, mvmap('<<FIELD>>', json_set('<<FIELD>>', "Key", "<<FIELD>>"))
                        )
                    )
            ]
    | fields + all_objects
    | mvexpand all_objects
    | spath input=all_objects
    | fields - all_objects
    | fields + Key, path, op, value

View solution in original post

dtburrows3
Builder

Think I was able to get the table in the output you are wanting. 

dtburrows3_0-1702480839496.png


I was able to achieve this using this SPL.

| makeresults
    | eval
        json_object="{ \"KeyA\": [ { \"path\": \"/attibuteA\", \"op\": \"replace\", \"value\": \"hello\" }, { \"path\": \"/attibuteB\", \"op\": \"replace\", \"value\": \"hi\" } ], \"KeyB\": [ { \"path\": \"/attibuteA\", \"op\": \"replace\", \"value\": \"\" }, { \"path\": \"/attibuteC\", \"op\": \"replace\", \"value\": \"hey\" }, { \"path\": \"/attibuteD\", \"op\": \"replace\", \"value\": \"hello\" } ], \"KeyC\": [ { \"path\": \"/attibuteE\", \"op\": \"replace\", \"value\": \"\" } ] }"
    | fields - _time
    | fromjson json_object
    | fields - json_object
    ``` Assuming that your fieldset at this point is only the list of fields needed for your final output ```
    ``` If there are other fields present that are not apart of the json object you are trying to parse then you should set up a naming convention for fields youy want to loop through ```
    | foreach *
        [
            | eval
                all_objects=mvappend(
                    'all_objects',
                    case(
                        isnull('<<FIELD>>'), null(),
                        mvcount('<<FIELD>>')==1, json_set('<<FIELD>>', "Key", "<<FIELD>>"),
                        mvcount('<<FIELD>>')>1, mvmap('<<FIELD>>', json_set('<<FIELD>>', "Key", "<<FIELD>>"))
                        )
                    )
            ]
    | fields + all_objects
    | mvexpand all_objects
    | spath input=all_objects
    | fields - all_objects
    | fields + Key, path, op, value

EricMonkeyKing
Explorer
Much appreciated! It works!
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @EricMonkeyKing ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

 

gcusello
SplunkTrust
SplunkTrust

Hi @EricMonkeyKing,

did you configured INDEXED_EXTRACTIONS = json for that sourcetype or used the spath command (https://docs.splunk.com/Documentation/Splunk/9.1.2/SearchReference/Spath) ?

Ciao.

Giuseppe

0 Karma

EricMonkeyKing
Explorer
Thanks. I tried but the scenario is a little bit complex ^^
0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...