spath - extracting data from groups within JSON

Mick_OBrien — Tue, 11 Jun 2024 16:37:07 GMT

Hi All,

Hopefully someone can help with this. We have logs that contain JSON where one of the fields can have multiple groups/entries - I would like to unwind/expand the groups to have a separate output per line. I think I have to use mvzip command but I'm having issues with syntax. Example data/query below...

| makeresults format=json Data="[ { \"event\": \"AGREEMENT_ACTION_COMPLETED\", \"participantUserEmail\": \"123456789@test.com\", \"agreement\": { \"id\": \"ABCDEFGHIJKLMNOPQRSTUVWXYZ\", \"status\": \"OUT_FOR_SIGNATURE\", \"participantSetsInfo\": { \"participantSets\": [ { \"memberInfos\": [ { \"id\": \"abcdefg\", \"email\": \"abcdefg@test.com\", \"company\": null, \"name\": \"test o'test\", \"privateMessage\": null, \"status\": \"ACTIVE\" } ], \"order\": \"1\", \"role\": \"SIGNER\", \"status\": \"WAITING_FOR_OTHERS\", \"id\": \"abcdefg1234\", \"name\": null, \"privateMessage\": null }, { \"memberInfos\": [ { \"id\": \"hijklmno\", \"email\": \"hijklmno@test.com\", \"company\": null, \"name\": null, \"privateMessage\": null, \"status\": \"ACTIVE\" } ], \"order\": \"1\", \"role\": \"SIGNER\", \"status\": \"WAITING_FOR_MY_SIGNATURE\", \"id\": \"hijklmno1234\", \"name\": null, \"privateMessage\": null } ] }, \"documentsInfo\": null, \"agreementViewRequest\": null } }]" | spath output=eventType path=event | spath output=agreementId path=agreement.id | spath output=agreementStatus path=agreement.status | spath output=participantUserEmail path=participantUserEmail | rename participantSets{}.membersInfos{}.email as memberEmail, participantSets{}.status as memberStatus | table _time, agreementId, eventType, agreementStatus, participantUserEmail, memberEmail, memberStatus

I still see only one line output and the 'memberEmail' and 'memberStatus' fields are showing as blank where as I want to see one line out to match every entry under 'participantSets' field.

Any help appreciated.

Re: spath - extracting data from groups within JSON

deepakc — Wed, 12 Jun 2024 10:51:12 GMT

Replace with this and see if that gives you the results.

| spath output=eventType path=event | spath output=agreementId path=agreement.id | spath output=agreementStatus path=agreement.status | spath output=participantUserEmail path=participantUserEmail | spath output=participantSets path=agreement.participantSetsInfo.participantSets{} | mvexpand participantSets | spath input=participantSets output=memberInfos path=memberInfos{} | mvexpand memberInfos | spath input=memberInfos path=email output=memberEmail | spath input=memberInfos path=status output=memberStatus | table _time, agreementId, eventType, agreementStatus, participantUserEmail, memberEmail, memberStatus

Re: spath - extracting data from groups within JSON

Mick_OBrien — Wed, 12 Jun 2024 11:25:53 GMT

@deepakc - This works - thank you!

topic Re: spath - extracting data from groups within JSON in Splunk Search

spath - extracting data from groups within JSON

Re: spath - extracting data from groups within JSON

Re: spath - extracting data from groups within JSON