Can you split a multi [n] value json attribute in to seperate rows?

[nickhuge]

I have the following json

{"timestamp":"2016-07-14T10:26+01:00","venture":"abc","totalRooms":3,"rooms":[{"key":"ROOM1","totalMembers":37,"totalHosts":4,"totalOccupants":41,"memberIds":[17631040,17783547,14887844,15096846,15002311,15161970,16507123,17952060,15092424,16113407,17493043,16499883,15014801,15756846,16371926,15211661,17340395,17639184,15785536,15122212,15501383,14967370,16832392,15864239,15131275,17973514,15244282,15130091,15245260,17793608,16832392,18198940,14975611,15859257,16196886,15731876,15864582],"hostUserNames":["host_1","host_2","host_3","host_4"]},
{"key":"ROOM2","totalMembers":7,"totalHosts":4,"totalOccupants":11,"memberIds":[15316677,17249975,17804442,16140609,17547109,17283553,17126351],"hostUserNames":["host_1","host_2","host_3","host_4"]},
{"key":"ROOM3","totalMembers":0,"totalHosts":0,"totalOccupants":0,"memberIds":[],"hostUserNames":[]},{"key":"host_1","totalMembers":0,"totalHosts":0,"totalOccupants":0,"memberIds":[],"hostUserNames":[]}]}

What I want to do is have a search result in splunk which has a new row for each member in each room so...

2016-07-14T10:26+01:00 ROOM1 17631040
2016-07-14T10:26+01:00 ROOM1 17783547
2016-07-14T10:26+01:00 ROOM1 14887844
...

Any idea how I can do this please? Cheers!

[javiergn]

See if this helps (you can obviously ignore the up to the first spath which I used to replicate your use case):

| stats count | fields - count
| eval _raw = "
{\"timestamp\":\"2016-07-14T10:26+01:00\",\"venture\":\"abc\",\"totalRooms\":3,\"rooms\":[{\"key\":\"ROOM1\",\"totalMembers\":37,\"totalHosts\":4,\"totalOccupants\":41,\"memberIds\":[17631040,17783547,14887844,15096846,15002311,15161970,16507123,17952060,15092424,16113407,17493043,16499883,15014801,15756846,16371926,15211661,17340395,17639184,15785536,15122212,15501383,14967370,16832392,15864239,15131275,17973514,15244282,15130091,15245260,17793608,16832392,18198940,14975611,15859257,16196886,15731876,15864582],\"hostUserNames\":[\"host_1\",\"host_2\",\"host_3\",\"host_4\"]},
{\"key\":\"ROOM2\",\"totalMembers\":7,\"totalHosts\":4,\"totalOccupants\":11,\"memberIds\":[15316677,17249975,17804442,16140609,17547109,17283553,17126351],\"hostUserNames\":[\"host_1\",\"host_2\",\"host_3\",\"host_4\"]},
{\"key\":\"ROOM3\",\"totalMembers\":0,\"totalHosts\":0,\"totalOccupants\":0,\"memberIds\":[],\"hostUserNames\":[]},{\"key\":\"host_1\",\"totalMembers\":0,\"totalHosts\":0,\"totalOccupants\":0,\"memberIds\":[],\"hostUserNames\":[]}]}
"
| spath input=_raw
| table timestamp, _raw
| spath input=_raw output=rooms path=rooms{}
| rex field=rooms max_match=0 "(?<room>[^\n]+)"
| fields - _raw, rooms
| mvexpand room
| spath input=room
| table timestamp, key, memberIds{}
| mvexpand memberIds{}

Output (see attached picture):

[sundareshr]

See if this gives you any ideas

https://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Spath#Example_3:_Extract_and_expa...

[nickhuge]

I saw this but isn't this about taking multiple attributes out of a json, whereas I want to take out multiple values from a single attribute with the number of values being variable...