Extract names and ids within array

[Tajuddin]

I have the following log from splunk where i want to extract names and their respective ids. Please help with the splunk search to print the room names along with dedup ids.

Log Event

TIME:10/Feb/2025:03:08:17 -0800 TYPE:INFO APP_NAME:ROOM_LOOKUP_JOBS APP_BUILD_VERSION:NOT_DEFINED CLIENT_IP:100.102.16.183 CLIENT_USER_AGENT:Unknown Browser CLIENT_OS_N_DEVICE:Unknown OS Unknow Devices CLIENT_REQUEST_METHOD:GET CLIENT_REQUEST_URI:/supporting-apps/room-lookup-job/index.php CLIENT_REQUEST_TYPE:HttpRequest CLIENT_REQUEST_CONTENT_SIZE:0 SERVER_HOST_NAME:roomlookupjob-prod.us-west-2i.app.apple.com SERVER_CONTAINER_ID:roomlookupjob-prod-5d96c45c64-w4q79 REQUEST_UNIQUE_ID:Z6neG-5vAofNnSWuA5msAQAAAAA MESSAGE="Rooms successfully updated for building - IL01: [{\"name\":\"Chiaroscuro (B277) [AVCN] (3) {R} IL01 2nd\",\"id\":\"6C30AF02-5900-480C-873F-8B0763DE95F8\"},{\"name\":\"2-Pop (N221) [AVCN] (8) {R} IL01 2nd\",\"id\":\"7853CB27-A083-454F-90A6-006854396AD1\"},{\"name\":\"Bonk (B380) [AVCN] (3) {R} IL01 3rd\",\"id\":\"88AF6D48-F930-4A98-9171-BE1FAAF0E36D\"},{\"name\":\"Montage (D203) [AVCN] (7) {R} IL01 2nd\",\"id\":\"29C44E4D-8628-4815-9AB8-CF49682A9EDC\"},{\"name\":\"Cougar - Interview Room Only (B138) (4) {R} IL01 1st\",\"id\":\"D1F40F0F-E40D-46B3-BD62-2C9A054E9E70\"},{\"name\":\"Iceman - Interview Room Only (B140) (3) {R} IL01 1st\",\"id\":\"38348FD5-021A-466E-A860-0A45CA9CD18F\"},{\"name\":\"Merlin - Interview Room Only (B136) (2) {R} IL01 1st\",\"id\":\"51211C55-94EA-4B38-97B6-2EB20369FDAF\"},{\"name\":\"Viper - Interview Room Only (B134) (10) {R} IL01 1st\",\"id\":\"940E9844-49BF-4B4E-B114-A2D734203C37\"},{\"name\":\"Maverick - Interview Room Only (B142) (4) {R} IL01 1st\",\"id\":\"6D29660F-09C3-4634-8DE5-0ECFAA5639DB\"},{\"name\":\"Vignette (R278) [AVCN] (12) {R} IL01 2nd\",\"id\":\"00265678-8775-4E95-A7CA-8454AD35C4A4\"},{\"name\":\"Broom Wagon (A317) [AVCN] (14) {R} IL01 3rd\",\"id\":\"1D1EB626-C5D2-4289-B5DA-A7F6EAA79AE8\"},{\"name\":\"Jump Cut (D211) [AVCN] (22) {R} IL01 2nd\",\"id\":\"66FF42BA-3ED6-48E6-886D-08CE18124110\"},{\"name\":\"{M} The Roundhouse (P404) (6) {R} IL01 4th\",\"id\":\"2477B40A-97BF-E2C7-4908-EF5D172D5DD3\"},{\"name\":\"Corncob (S323) [AVCN] (7) {R} IL01 3rd\",\"id\":\"F01706E7-F19B-3035-CEF4-4D13FC792B0E\"},{\"name\":\"Rouleur (Q311) [AVCN] (14) {R} IL01 3rd\",\"id\":\"D96D16CE-557E-90A0-AF65-9FCAAE406659\"},{\"name\":\"Field Sprint (S341) [AVCN] (13) {R} IL01 3rd\",\"id\":\"DA59EAC2-8491-3EE2-9B78-A54E5A3FE704\"},{\"name\":\"{M} Storyboard (C218) [AVCN] (27) {R} IL01 2nd\",\"id\":\"45C4588D-0CB5-D035-5C2E-517477B1D7CB\"},{\"name\":\"Zoetrope (S241) [AVCN] (8) {R} IL01 2nd\",\"id\":\"58750290-4C79-9AFB-B277-BDE5A219D0E5\"},{\"name\":\"Sizzle Reel (P248) [AVCN] (8) {R} IL01 2nd\",\"id\":\"DF8004E6-25B8-3B18-794D-253D83FE1279\"},{\"name\":\"Rough Cut (N213) [AVCN] (7) {R} IL01 2nd\",\"id\":\"A3792CEC-BF73-F207-DB06-3884D1042C80\"}]"

index=roomlookup_prod | search "Rooms successfully updated for building - IL01"

Expected results:

name	id
Chiaroscuro (B277) [AVCN] (3) {R} IL01 2nd	6C30AF02-5900-480C-873F-8B0763DE95F8
2-Pop (N221) [AVCN] (8) {R} IL01 2nd	7853CB27-A083-454F-90A6-006854396AD1

and so on..

[ITWhisperer · Accepted solution]

To be honest, this log looks like a mess - it appears to be trying to use JSON but is failing - if you have any influence over the app developers, try to get them to fix their log format.

Failing that, you could try something really messy like this

| rex "MESSAGE=\"Rooms successfully updated for building - IL01: (?<msg>.+?)(?<!\\\\)\""
| eval msg="{\"il01\":\"".msg."\"}"
| spath input=msg
| spath input=il01 {} output=array
| mvexpand array
| spath input=array
| table name id

This assumes that "Rooms successfully updated for building - IL01" is a static string, if it isn't, you might need to replace some or all of this with the corresponding regular expression.

[Tajuddin]

Excellent, This worked. Thanks a lot for your efforts.

[gcusello]

Hi @Tajuddin ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

[Tajuddin]

Please note that there is actually 😕 in place of 😕. When i posted, it automatically converted to emoji.

[gcusello]

Hi @Tajuddin ,

at first, to share something like log samples or code you can use the "Insert/Edit code sample" button.

Anyway, this seems to be a json log, did you tried to use INDEXED_EXTRACTION=JSON or spath command?

Otherwise, it's possible to use a regex.

Ciao.

Giuseppe

[Tajuddin]

Hi @gcusello I tried both using spath and rex commands. Both haven’t worked. Using spath I didn’t get any stats. With rex, it shown 1317 stats but in results it is completely empty or you can say it created an empty table. 
please give me a full conmand which you feel is gonna work.

index and search string has been already provided.