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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...