Using Data as Fields

[leonheart78]

I’m currently working with some production line data, where each tag value represent a field. Example like below:

Tag | Value
R0001 | 1 -> Batch No
R0002 | 2018 -> Year
R0003 | 1 -> Month
R0004 | 22 -> Day
R0005 | 5040 -> Volume A
R0006 | 446 -> Volume B
R0007 | 189 -> Volume C
R0008 | 1099 -> Total Mixed
R0009 | 0 -> Result (0 = OK, 1 = Not OK)

I need to arrange the data to look as below
Batch No |Year |Month |Day |Volume A |Volume B |Volume C |Total Mixed |Result
1 |2018 |1 |22 |5040 |446 |189 |1099 |OK
2 |2018 |1 |23 |5030 |435 |198 |1078 |OK

I was looking at using the Lookup table to achieve it, but not sure how to go about doing it. Any advise is appreciated. Thank you.

[mayurr98]

hey @leonheart78

I think the better way is to do with lookups.

step 1 : Create a lookup table say mylookup.csv with columns Tag and Description
Tag | Description
R0001 | Batch No
R0002 | Year
R0003 | Month
R0004 | Day
R0005 | Volume A
R0006 | Volume B
R0007 | Volume C
R0008 | Total Mixed
R0009 | Result (0 = OK, 1 = Not OK)

Step 2 : After creating a lookup table, add the lookup table into Splunk.
Follow this doc to add mylookup.csv
https://docs.splunk.com/Documentation/Splunk/7.0.1/PivotTutorial/AddlookupfilestoSplunk

Step 3 : then write this query on the search head

| lookup mylookup.csv Tag OUTPUT Description | stats count by Tag Value | eval Description=case(Value=0 AND Description="Result","OK",Value=1 AND Description="Result","NOT OK",1=1,Description)

I hope this helps you!

[HiroshiSatoh]

An example
Separate key / value pairs If the delimited character is a comma

|NOOP|stats count as _raw|eval _raw="R0001|1
,R0002|2018
,R0003|1R0004|22
,R0005|5040
,R0006|446
,R0007|189
,R0008|1099
,R0009|0"
| extract pairdelim=",", kvdelim="|"
|rename R0001 as "Batch No"
|rename R0002 as "Year"
|rename R0003 as "Month"
|rename R0004 as "Day"
|rename R0005 as "Volume A"
|rename R0006 as "Volume B"
|rename R0007 as "Volume C"
|rename R0008 as "Total Mixed"
|rename R0009 as "Result"
|eval Result=if(Result=0,"OK","Not OK")

[richgalloway]

You'll probably have better results using Field Aliases. A the name implies, field aliases let you define alternative names for some fields. You could, for example, create alias "Batch" for field "R0001", and so on. See http://docs.splunk.com/Documentation/Splunk/7.0.1/Knowledge/Addaliasestofields.

[HiroshiSatoh]

Could you present a sample of _raw data?