We have a large (~500 line) report being used to calculate CVE scores and fill a summary index daily, with vulnerabilities from Qualys as the initial input that gets enriched.
One of the fields in the ouput is called ICT_ID, and this is supposed to match on the vulnerabilities from Qualys according to a lookup CSV file. If the vulnerability matches, it gets a corresponding ICT_ID, otherwise this field is NULL.
The issue is that for our lookup file, there are no unique primary keys. QID (Qualys vuln ID) is the closest thing to a PK in the lookup, but there are multiple rows with the same QID and other fields like IP and host which differ.
The requirement for matching a vulnerability to the ICT list is two-fold: 1) the QID must match, but also must match 2) *any* of the following (host, IP, app) *in that order of precedence*
-----------------
The following code was implemented, but it seems to only match on the first matching instance of QID in the lookup, which usually breaks the rest of the logic for the other fields which should match.
``` precedence: (host -> ip -> app -> assetType) && QID ```
| join type=left QID
[
| inputlookup ICT_LOOKUP.csv
| rename "Exp Date" as Exp_Date
| rename * as ICT_LOOKUP-*
| rename ICT_LOOKUP-QID as QID
]
```| rename fields from lookup as VM_ICT_Tracker-<field>```
| eval ICTNumber = case(
like(entity,'ICT_LOOKUP-host'), 'ICT_LOOKUP-ICTNumber',
like(IP,'ICT_LOOKUP-ip'), 'ICT_LOOKUP-ICTNumber',
like(app,'ICT_LOOKUP-app'), 'ICT_LOOKUP-ICTNumber',
like(assetType,'ICT_LOOKUP-assetType'), 'ICT_LOOKUP-ICTNumber',
1=1,"NULL"
)
| rename ICTNumber as ICT_ID
