Hi everyone: I have a lookup I am using to filter against another lookup and I'm having trouble getting the output to appear like I want it. I've looked at many answers here and just can't figure out what I'm doing wrong.
My search:
| inputlookup lookup_1
| table cveID asset Project product dueDate
| mvcombine delim=",", asset
| nomv asset
| eval numberCVEs=mvcount(split(asset,","))
| appendpipe
[ |inputlookup lookup_2
| fields q1-cveID ]
| fillnull
| table cveID, q1-cveID asset Project product dueDate
I am trying to match the cveID field of lookup_1 to the q1-cveID field of lookup_2. And if there are cve's that exist in lookup 2 but do not exist in lookup 1, I still want them to appear with a "0" value in the asset column (thus the append).
What I'm currently getting:
cveID | q1-cveID | Asset | Project | product | dueDate |
cve-1234 | 0 | 7 | Microsoft | Office | 2022-07-01 |
0 | cve-1234 | 0 | 0 | 0 | 0 |
What I would like to see:
cveID | q1-cveID | Asset | Project | product | dueDate |
cve-1234 | cve-1234 | 7 | Microsoft | Office | 2022-07-01 |
cve-5678 | cve-5678 | 0 | Apple | iPhone | 2022-08-01 |
How do I match up these two lookups and create a "0" value for items that exist only in lookup 2?
Thanks for this, ITWhisperer, this got me a little closer. I would also like the cve's that have 0 assets to show in the report, right now only assets with at least a value of 1 are showing. Is it possible to add this?
Figured it out. I added a new column ("mycount") in lookup2 with a "0" value for each entry. Then when I searched lookup2 against lookup1, for every row where there was no cve match between lookups, the cve was added with a "0" value.
Final search:
| inputlookup lookup_1
| table cveID asset Project product dueDate
| mvcombine delim=",", asset
| nomv asset
| eval numberCVEs=mvcount(split(asset,","))
| append
[ |inputlookup lookup_2
| fields q1-cveID, mycount
| eval cveID=q1-cveID ]
| dedup cveID
| eval numberCVEs=if(mycount=0, "0", numberCVEs)
| table cveID, q1-cveID asset Project product dueDate
| inputlookup lookup_1
| table cveID asset Project product dueDate
| mvcombine delim=",", asset
| nomv asset
| eval numberCVEs=mvcount(split(asset,","))
| append
[ |inputlookup lookup_2
| fields q1-cveID
| eval cveID=q1-cveID ]
| stats values(*) as * by cveID
| fillnull
| table cveID, q1-cveID asset Project product dueDate