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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...