topic Re: Using a field value to reference a lookup column in Knowledge Management

Using a field value to reference a lookup column

skelly99 — Thu, 26 Apr 2018 07:39:21 GMT

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

Re: Using a field value to reference a lookup column

somesoni2 — Tue, 29 Sep 2020 19:18:10 GMT

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

Re: Using a field value to reference a lookup column

skelly99 — Thu, 26 Apr 2018 21:04:23 GMT

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.

Re: Using a field value to reference a lookup column

somesoni2 — Thu, 26 Apr 2018 22:19:33 GMT

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

Re: Using a field value to reference a lookup column

DalJeanis — Fri, 27 Apr 2018 03:20:01 GMT

Moved to answer, since it solves the issue perfectly.