Knowledge Management

Using a field value to reference a lookup column

skelly99
Explorer

Hi

Is there any way I can use a field value to reference a column in a lookup

In my events I have a field called cve which contains a cve value, eg cve-2016-2018. I want to use the value of this field along with os information that is also available in my events to match a column in my lookup file.

The lookup file (vulnerability.csv) looks like this (this is only a small extract from the file - there are 39 columns and 50 or so rows)

platform, major, minor, cve-2016-2108, cve-2016-5195, cve2017-10010, cve-2017-14491
rhel, 5,5, NP , NV, OSP , NP
rhel, 5,6, NP , NV , OSP , DP
rhel, 5,7, NP , NV , OSP , DP
rhel,6, OSP , DP , NV , NP
suse,10 , SP1 , NP , NV , OSP, NP
suse,11, SP1 , NP , NV , OSP , NP

So I can use | lookup vulnerability platform major minor and I will get all 39 lookup columns added to the event for the matching os
However I am only interested in the column that matches the value of the field cve in my event

Using the lookup table example above - if my event has field values platform=rhel major=5 minor=6 cve=cve-2016-2108 I am only interested in the value in the lookup column cve-2016-2108 which matches my os - in the lookup above that is the value NP

Does anyone know of a way to substitute a field value to then use match a lookup column?

I know I can create a field from a value using eval - eg field cve=cve-2016-2108 - if I use eval "{cve}"=cve this creates a new field called cve-2016-2108 with the value cve-2016-2108 - however I can't find a way to then pass the new field into the call to the lookup

Interested in any suggestions please on how to use a field value to reference look-up column. Failing that I have to return all columns in the lookup file whats the best way of keeping the column I need and discarding the rest?

Thanks, S

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Another better option would be to re-format your lookup table content. Instead of having a column for each cve value, have two columns, cve_name and cve_value. This way you can just lookup based on platform major minor and cve column and get the value as OUTPUT.

The format that I am talking about for your lookup can be seen by this query

| inputlookup vulnerability | eval temp=platform."##".major."##".minor | table temp cve*
| untable temp cve_name cve_value

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Another better option would be to re-format your lookup table content. Instead of having a column for each cve value, have two columns, cve_name and cve_value. This way you can just lookup based on platform major minor and cve column and get the value as OUTPUT.

The format that I am talking about for your lookup can be seen by this query

| inputlookup vulnerability | eval temp=platform."##".major."##".minor | table temp cve*
| untable temp cve_name cve_value
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Moved to answer, since it solves the issue perfectly.

0 Karma

skelly99
Explorer

Thanks very much for the pointer - now managed to get this working. I'd been thinking about re-working the lookup but the untable option is very nifty to do this.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

I actually forgot a step in my query to re-extract fields from temp. You just need to add following after untable command

| rex field=temp "(?<platform>.+)##(?<major>.+)##(?<minor>.+)" | fields - temp
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...