How to display multiple values of different field names in one column table?

[cesar_tomas]

Hi everyone,

I am new to Splunk and  I have been trying to do a complex report that I haven't been able to solve so please any help would appreciate a lot.

I need to create a table like this:

ID	Name	Function	Device	Number	Unit
1	AAA23	Allocate	A1	12	U1
			A2	15	U2
			A3	13	U1
			A4	12	U4
2	AAA23	Allocate	A1	12	U1
3	AAA23	Deallocate	A1	12	U1
			A2	15	U2

Here are the three events in JSON format:

1{"ID":"1","NAME":"AAA23","FUNCTION":"1", "DEVICE_001":”A1”,”NUMBER_001”:12,”UNIT_001”:”U1”, "DEVICE_002":”A2”,”NUMBER_002”:15,”UNIT_002”:”U2”, "DEVICE_003":”A3”,”NUMBER_003”:13,”UNIT_003”:”U1”, "DEVICE_004":”A4”,”NUMBER_004”:12,”UNIT_004”:”U4”}

2 {"ID":"2","NAME":"AAA23","FUNCTION":"1", "DEVICE_001":”A1”,”NUMBER_001”:12,”UNIT_001”:”U1” }

3{"ID":"3","NAME":"AAA23","FUNCTION":"2", "DEVICE_001":”A1”,”NUMBER_001”:12,”UNIT_001”:”U1”, "DEVICE_002":”A2”,”NUMBER_002”:15,”UNIT_002”:”U2”)

As you can see the name of the fields DEVICE, NUMBER and UNIT depends on the number of entries in the NAME & ID fields so, sometimes for the same NAME & ID   field values I have 50 different name fields with a consecutive number, so as an example the previous fields are:

DEVICE_001 ,DEVICE_002,…,DEVICE_050

NUMBER_001, NUBMER _002…., NUMBER _050,

UNIT_001, UNIT_002,…, UNIT_050

And sometimes only 1 entry . this is variable and don´t depend on a specific field name.

With this in mind my question is how I can set this search on a Table Splunk:

I have been trying the next:

index=dataexample 

|spath

|rex "DEVICE_\d+":"(?P<DEVICE_1>[a-zA-Z0-9]+)" max_match=0

|rex "NUMBER_\d+":(?P<NUMBER_1>\d+)" max_match=0

|rex "UNIT_\d+":"(?P<UNIT_1>[a-zA-Z0-9]+)" max_match=0

|eval TIPO=case(FUNCTION ==01,"ALLOCATE", FUNCTION ==02,"DEALLOCATE", FUNCTION ==03, "OTHER")

| stats values(NAME),values(TIPO),values(DEVICE_1), values(NUMBER_1), values(UNIT_1) by ID

But I don´t know how to set all the variable( 1 or 50 or 60 ) field values in just one column per each DEVICE, NUMBER, UNIT per each event.

[richgalloway]

You can use wildcards in most stats functions.  Try

| stats values(*) as * by ID