How to filter data from JSON object having one field name and list of values into table?

Splunk_321 — Fri, 11 Nov 2022 14:21:57 GMT

I have data something like below.

msg: {
     application: test-app
     correlationid: 0.59680117.1667864418.7d2b8d5
     httpmethod: GET
     level: INFO
     logMessage: {
       apiName: testApi
       apiStatus: Success
       clientId: testClientId1
       error: NA
       list_items: [
         {
           city: PHOENIX
           countryCode: USA
           locationId: dc5269a4-c043-4381-b757-63950feecac3
           matchRank: 1
           merchantName: testMerchant1
           postalCode: 12345
           state: AZ
           streetAddress: 4000 E SKY HARBOR BLVD
         }
         {
           city: PHOENIX
           countryCode: USA
           locationId: c7b97f03-b21b-4c11-aead-1ca3cd03d415
           matchRank: 2
           merchantName: testMerchant2
           postalCode: 56789
           state: AZ
           streetAddress: 4000 E SKY HARBOR BL
         }

......

]

I have to get a table with clientId and locationId something like below

clientId locationId

testClientId1 dc5269a4-c043-4381-b757-63950feecac3

testClientId1 c7b97f03-b21b-4c11-aead-1ca3cd03d415

What I tried is | base search | | table "msg.logMessage.clientId", "msg.logMessage.matched_locations{}.locationId"

which resulted in grouping the locationIds for clientId hence one row for even multiple locationIds

clientId locationId

testClientId1 dc5269a4-c043-4381-b757-63950feecac3

c7b97f03-b21b-4c11-aead-1ca3cd03d415

Any help is appreciated.

Re: How to filter data from JSON object having one field name and list of values into table

richgalloway — Fri, 11 Nov 2022 13:22:10 GMT

Use the mvexpand command to put the locationId values on different rows.

| base search | | table "msg.logMessage.clientId", "msg.logMessage.matched_locations{}.locationId" | mvexpand "msg.logMessage.matched_locations{}.locationId"

topic Re: How to filter data from JSON object having one field name and list of values into table in Splunk Search

How to filter data from JSON object having one field name and list of values into table?

Re: How to filter data from JSON object having one field name and list of values into table