Splunk Search

Why are multiple Lookups showing no results instead of Zero?

mistydennis
Communicator

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:

cveIDq1-cveIDAssetProjectproductdueDate
cve-123407MicrosoftOffice2022-07-01
0cve-12340000

 

What I would like to see:

cveIDq1-cveIDAssetProjectproductdueDate
cve-1234cve-12347MicrosoftOffice2022-07-01
cve-5678cve-56780AppleiPhone2022-08-01

 

How do I match up these two lookups and create a "0" value for items that exist only in lookup 2?

Labels (4)
Tags (1)
0 Karma

mistydennis
Communicator

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?

0 Karma

mistydennis
Communicator

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
0 Karma

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