Extract field value from json string with different spath and group by

[newbie77]

I have splunk events that has a splunk field as json string named "data"

I want to group these events by "id". This id could appear in 2 different path.

Event Type 1

data= {

      "response": {

       "resources":[

            {

               "type": "loginUser",

               "id": "1234"

           }

        ]

 }

}

Event Type 2

data= {

       "response": {

               "actors":

               {

                      "type": "loginUser",

                     "id": "1234"

              }

        }

}

[newbie77]

Many thanks for the help.  I want to expand the requirement as follows:

For  an "id" there could be  upto 12 max possible different events with response.action.type="UserCreated" or response.action.type="TxCreated"  or response.action.type="TxUpdated" and 9 other types.

The goal is to group by "id" where only 2 action types have occured namely:

•            response.action.type="UserCreated" (Event1) and
•            response.action.type="TxCreated"  (Event 2)

Event Type 1

data= {

      "response": {

               "action": {
                    "type": "UserCreated",
               }

       "resources":[

            {

               "type": "loginUser",

               "id": "1234"

           }

        ]

 }

}

Event Type 2

data= {

       "response": {

              "action": {
                   "type": "TxCreated",
               }

               "actors":

               {

                      "type": "loginUser",

                     "id": "1234"

              }

        }

}

Event Type 3

data= {

       "response": {

              "action": {
                   "type": "TxUpdated",
               }

               "actors":

               {

                      "type": "loginUser",

                     "id": "1234"

              }

        }

}

[ITWhisperer · Accepted solution]

Assuming you have already extracted the data field, and that the string in data is valid JSON (which you example is not), you could try this

| spath input=data
| where 'response.action.type'="UserCreated" OR 'response.action.type'="TxCreated"
| eval id = coalesce('response.resources{}.id', 'response.actors.id')

[ITWhisperer]

| spath input=data
| foreach *.id
    [| eval id=if(isnotnull('<<FIELD>>'),'<<FIELD>>',id)]

[yuanliu]

Alternatively, if you already have all possible paths and there are not too many, coalesce can be more succinct.  For the two path illustrated,

| eval id = coalesce('response.resources.id', 'respones.actors.id')