Splunk Search

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

cesar_tomas
Explorer

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.

Labels (3)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

You can use wildcards in most stats functions.  Try

| stats values(*) as * by ID

 

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...