topic Re: JSON array contents to lookup table in Splunk Search

How to extract JSON array contents to lookup table?

AlanMoen — Mon, 18 Jul 2022 14:14:03 GMT

I've got a JSON array I ingest that I want to extract certain fields from to save into a lookup table.

Here's an example of the JSON:

{ "Response": { "results": [ { "memberType": 2, "isOnline": false, "lastOnlineStatusChange": "1657176499", "groupId": "1234567", "destinyUserInfo": { "LastSeenDisplayName": "UserName1", "LastSeenDisplayNameType": 1, "iconPath": "/img/theme/bungienet/icons/xboxLiveLogo.png", "crossSaveOverride": 1, "applicableMembershipTypes": [ 2, 1 ], "isPublic": false, "membershipType": 1, "membershipId": "1234567890123456789", "displayName": "UserName1", "bungieGlobalDisplayName": "UserName1", "bungieGlobalDisplayNameCode": 9999 }, "bungieNetUserInfo": { "supplementalDisplayName": "UserName1#9999", "iconPath": "/img/profile/avatars/default_avatar.gif", "crossSaveOverride": 0, "isPublic": false, "membershipType": 254, "membershipId": "12345678", "displayName": "UserName1", "bungieGlobalDisplayName": "UserName1", "bungieGlobalDisplayNameCode": 9999 }, "joinDate": "2021-10-27T20:56:48Z" }, { "memberType": 2, "isOnline": false, "lastOnlineStatusChange": "1657390180", "groupId": "1234567", "destinyUserInfo": { "LastSeenDisplayName": "UserName2", "LastSeenDisplayNameType": 1, "iconPath": "/img/theme/bungienet/icons/xboxLiveLogo.png", "crossSaveOverride": 1, "applicableMembershipTypes": [ 2, 3, 1 ], "isPublic": false, "membershipType": 1, "membershipId": "4611686018431599324", "displayName": "UserName2", "bungieGlobalDisplayName": "UserName2", "bungieGlobalDisplayNameCode": 8888 }, "bungieNetUserInfo": { "supplementalDisplayName": "UserName2#8888", "iconPath": "/img/profile/avatars/HaloRingcopy.gif", "crossSaveOverride": 0, "isPublic": false, "membershipType": 254, "membershipId": "1990219", "displayName": "UserName2", "bungieGlobalDisplayName": "UserName2", "bungieGlobalDisplayNameCode": 8888 }, "joinDate": "2020-04-07T15:07:21Z" } ], "totalResults": 2, "hasMore": true, "query": { "itemsPerPage": 2, "currentPage": 1 }, "useTotalResults": true }, "ErrorCode": 1, "ThrottleSeconds": 0, "ErrorStatus": "Success", "Message": "Ok", "MessageData": {} }

(I truncated the results array to 2, there are normally many more)
I want to write to a lookup table like this:

_time | membershipId | joinDate 2022-07-17 16:20:28 | 1234567890123456789 | 2021-10-27T20:56:48Z 2022-07-17 16:20:28 | 9876543210123456789 | 2020-04-07T15:07:21Z

I can get something close into a table with:

index=myindex | rename Response.results{}.destinyUserInfo.membershipId as membershipId Response.results{}.joinDate as JoinDate | table _time ID JoinDate

but saving that to a lookup table makes the membershipId and joinDate into multivalue fields and stores all of the values accordingly. I need them separate.

Help?

Re: JSON array contents to lookup table

yuanliu — Sun, 17 Jul 2022 23:08:19 GMT

You need to mvexpand on Response.results{} first using

| spath path=Response.results{} | mvexpand Response.results{} | spath input=Response.results{}

After this, the two columns will be listed like

destinyUserInfo.membershipId	joinDate
1234567890123456789	2021-10-27T20:56:48Z
4611686018431599324	2020-04-07T15:07:21Z

Re: JSON array contents to lookup table

AlanMoen — Mon, 18 Jul 2022 00:50:31 GMT

OK -
That does get the ID and joinDate in a table as discrete value pairs, so I'm closer, but trying to add the _time value only associates it with the first value pair.

But it's a start, thanks

Re: JSON array contents to lookup table

yuanliu — Mon, 18 Jul 2022 01:24:53 GMT

When you say "only associate time with first value", do you mean you want to discard the rest of values? If so, no need for mvexpand. This becomes a much cheaper operation with mvindex.

| spath path=Response.results{} | eval FirstResult=mvindex('Response.results{}', 0) | spath input=FirstResult

Re: JSON array contents to lookup table

AlanMoen — Mon, 18 Jul 2022 12:04:11 GMT

No - it looks like this when I add _time:

_time | membershipId | joinDate --------------------------------------------------------------- 2022/07/18 07:25:00 | 012345678912345678 | 02-21-2020T09:25:36Z | 987654321098765432 | 09-15-2019T17:53:25Z | etc

I need all three fields populated in the lookup field. The _time field is important to some later processes. If a member leaves, I need to know the last time they were found to be there.

Re: JSON array contents to lookup table

yuanliu — Mon, 18 Jul 2022 16:15:02 GMT

In that case, using mvexpand is right direction. You only need to include _time in table. Here is my complete simulation.

| makeresults ``` simulate data ``` | eval data="{ \"Response\": { \"results\": [ { \"memberType\": 2, \"isOnline\": false, \"lastOnlineStatusChange\": \"1657176499\", \"groupId\": \"1234567\", \"destinyUserInfo\": { \"LastSeenDisplayName\": \"UserName1\", \"LastSeenDisplayNameType\": 1, \"iconPath\": \"/img/theme/bungienet/icons/xboxLiveLogo.png\", \"crossSaveOverride\": 1, \"applicableMembershipTypes\": [ 2, 1 ], \"isPublic\": false, \"membershipType\": 1, \"membershipId\": \"1234567890123456789\", \"displayName\": \"UserName1\", \"bungieGlobalDisplayName\": \"UserName1\", \"bungieGlobalDisplayNameCode\": 9999 }, \"bungieNetUserInfo\": { \"supplementalDisplayName\": \"UserName1#9999\", \"iconPath\": \"/img/profile/avatars/default_avatar.gif\", \"crossSaveOverride\": 0, \"isPublic\": false, \"membershipType\": 254, \"membershipId\": \"12345678\", \"displayName\": \"UserName1\", \"bungieGlobalDisplayName\": \"UserName1\", \"bungieGlobalDisplayNameCode\": 9999 }, \"joinDate\": \"2021-10-27T20:56:48Z\" }, { \"memberType\": 2, \"isOnline\": false, \"lastOnlineStatusChange\": \"1657390180\", \"groupId\": \"1234567\", \"destinyUserInfo\": { \"LastSeenDisplayName\": \"UserName2\", \"LastSeenDisplayNameType\": 1, \"iconPath\": \"/img/theme/bungienet/icons/xboxLiveLogo.png\", \"crossSaveOverride\": 1, \"applicableMembershipTypes\": [ 2, 3, 1 ], \"isPublic\": false, \"membershipType\": 1, \"membershipId\": \"4611686018431599324\", \"displayName\": \"UserName2\", \"bungieGlobalDisplayName\": \"UserName2\", \"bungieGlobalDisplayNameCode\": 8888 }, \"bungieNetUserInfo\": { \"supplementalDisplayName\": \"UserName2#8888\", \"iconPath\": \"/img/profile/avatars/HaloRingcopy.gif\", \"crossSaveOverride\": 0, \"isPublic\": false, \"membershipType\": 254, \"membershipId\": \"1990219\", \"displayName\": \"UserName2\", \"bungieGlobalDisplayName\": \"UserName2\", \"bungieGlobalDisplayNameCode\": 8888 }, \"joinDate\": \"2020-04-07T15:07:21Z\" } ], \"totalResults\": 2, \"hasMore\": true, \"query\": { \"itemsPerPage\": 2, \"currentPage\": 1 }, \"useTotalResults\": true }, \"ErrorCode\": 1, \"ThrottleSeconds\": 0, \"ErrorStatus\": \"Success\", \"Message\": \"Ok\", \"MessageData\": {} }" ``` data simulation complete ``` | spath input=data path=Response.results{} ``` input= is needed unless _raw is JSON ``` | mvexpand Response.results{} | spath input=Response.results{} | table _time destinyUserInfo.membershipId joinDate

_time	estinyUserInfo.membershipId	joinDate
2022-07-18 09:11:49	1234567890123456789	2021-10-27T20:56:48Z
2022-07-18 09:11:49	4611686018431599324	2020-04-07T15:07:21Z

Is this something you are looking for?

Re: JSON array contents to lookup table

AlanMoen — Tue, 19 Jul 2022 00:32:05 GMT

Perfect!

Thank you very much for your assistance