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
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...