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.
