Splunk Search

Unpivoting a lookup table

Splunkster45
Communicator

I have an interesting lookup table problem. I essentially want to unpivot a lookup table (in other words I have multiple fields in a lookup table, but only 1 field in the table that will be created. Conditions will determine which lookup field populates that table field).

In my data I have two fields of the following form:
Table.....Status
A............delete
A............insert
B............delete
B............insert

The look up table is of the following form:

Table.....Insert_Status.....Delete_Status
A............yes....................no
B............yes....................yes

If I run this normally as I do a lookup table:

lookup $LookupName$ Table as Table OUTPUTNEW Insert_Status as Insert_Status

I will get 4 columns that look like the following

Table.....Status...........Insert_Status.....Delete_Status
A............delete...........yes......................no
A............insert............yes......................no
B............delete...........yes......................yes
B............insert............yes......................yes

I want instead to have the output be the following:

Table.....Status.....3rd_Column
A............delete......no
A............insert.......yes
B............delete......yes
B............insert.......yes

There are 2 unattractive ways that I can get to this 3 column end result:
1) Have multiple A and B values for the Table field in the lookup table.
2) Use the lookup table to get the 4 column example and then add a transformation line of code that turns creates a 5th column that is in the proper format and then get rid of the other two columns.

I don't like 1, because it will leave a very messy lookup table and I want to keep that as clean as possible as other people will be filling it in. I feel like 2 is a very sloppy way of doing it. I have more than 2 Statuses and this could get really big really quick.

I'm wondering if there is some way that I could populate the third column based on the Status column. e.g. if Status is 'delete' then I use the respective 'Delete Status' lookup field and if the Status is 'insert' then I use the respective 'Insert Status' lookup field.

I know this is a long post, but hopefully it was clear. Thanks for taking the time to look at this.

Splunkster45

Tags (1)
0 Karma

woodcock
Esteemed Legend

I would use a macro like this:

[InsertOrDeleteStatus(3)]
args = LookupName, Table, InsertOrDelete
definition = lookup $LookupName$ $Table$ AS Table OUTPUTNEW $InsertOrDelete$_Status AS Status

Then call it like this (you have to uppercase the first letter of Table; e.g. "delete"->"Delete"):

... | eval myTable= upper(substr(Table,1,1)).substr(Table,2) | `InsertOrDeleteStatus($LookupName$,Table,Status)` | field - myTable
0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!