Why are multiple Lookups showing no results instead of Zero?

[mistydennis]

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?

[mistydennis]

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?

[mistydennis]

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

[ITWhisperer]

| 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