Splunk Search

Extract names and ids within array

Tajuddin
Explorer

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 2nd6C30AF02-5900-480C-873F-8B0763DE95F8
2-Pop (N221) [AVCN] (8) {R} IL01 2nd 7853CB27-A083-454F-90A6-006854396AD1

and so on..

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

Tajuddin
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Tajuddin ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

0 Karma

Tajuddin
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

Tajuddin
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...