Splunk Search

How to search what values are missing in my lookup table?

raomu
Explorer

How to write a search to get a list of items which are not matching.

Example :

I have a list of devices :

A
B
C
D

Now I have a lookup table with a list of devices below :

A
B
D
E

How I can find the list of devices which are missing in my lookup table?
In above case, the results should be C and E

1 Solution

niketn
Legend

@raomu, your question mentions that you need to find devices present in your data but missing from your lookup. However, based on your expected results, device E is present in lookup while missing from your actual data. Please confirm whether that is required or not.

Nevertheless @somesoni2 's answer should still work for you with final pipe as | where mvcount(from)=1.

Following is a run anywhere search based on sample data and output provided

| makeresults
| eval deviceFromData="A,B,C,D"
| makemv deviceFromData delim=","
| mvexpand deviceFromData
| rename deviceFromData as device
| append [ | makeresults
| eval deviceFromLookup="A,B,D,E"
| makemv deviceFromLookup delim=","
| mvexpand deviceFromLookup
| rename deviceFromLookup as device]
| stats count by device
| search count=1
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@raomu, your question mentions that you need to find devices present in your data but missing from your lookup. However, based on your expected results, device E is present in lookup while missing from your actual data. Please confirm whether that is required or not.

Nevertheless @somesoni2 's answer should still work for you with final pipe as | where mvcount(from)=1.

Following is a run anywhere search based on sample data and output provided

| makeresults
| eval deviceFromData="A,B,C,D"
| makemv deviceFromData delim=","
| mvexpand deviceFromData
| rename deviceFromData as device
| append [ | makeresults
| eval deviceFromLookup="A,B,D,E"
| makemv deviceFromLookup delim=","
| mvexpand deviceFromLookup
| rename deviceFromLookup as device]
| stats count by device
| search count=1
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

raomu
Explorer

Excellent.. It worked for me.

Thanks for your time.

0 Karma

niketn
Legend

@raomu, I have converted to answer. Please accept to mark as answered!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

kdulle
Explorer

You mention that you want the results to show both C and E, but actually the only one that appears to be missing from the lookup is C. So if you are interested in finding out what is missing from both, you may want to try using the diff command.

Something like:

| set diff [ | inputlookup devices.csv ] [ search index=<your_index> devices=* | table devices]

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

The problem with diff is that it does not tell you which way the search is different - an element missing from the first file, or an element missing from the second file, will both be on the output file with no telling where it came from. That's not particularly useful in most scenarios. (Not your fault, I've just never found it particularly useful.)

0 Karma

mayurr98
Super Champion

Hey try this run anywhere search

| makeresults 
| eval devices="A B C D" 
| makemv devices 
| mvexpand devices 
| eval check="csv1" 
| append 
    [| makeresults 
    | eval devices="A B D E" 
    | makemv devices 
    | mvexpand devices 
    | eval check="csv2" ] 
| fields- _time 
| stats dc(check) as count by devices 
| where count=1 
| fields devices 
| rename devices as "missing device list"

In your environment you should try something like this

index=<your_index> devices=* 
| dedup devices 
| table devices 
| eval check="csv1" 
| append 
    [| inputlookup devices.csv 
    | dedup devices 
    | table devices 
    | eval check="csv2"] 
| stats dc(check) as count by devices 
| where count=1 
| fields devices 
| rename devices as "missing device list"

let me know if this helps!

somesoni2
Revered Legend

Try like this

your current search giving list of of devices from your index with field say device | table deviceName | eval from="data"
| append [| inputlookup append=t yourlookup.csv | table deviceName | eval from="lookup"]
| stats values(from) as from by deviceName  | where mvcount(from)=1 AND from="data"

kdulle
Explorer

This worked great for me to identify which devices were missing from a search compared to my lookup table. I just had to change from="data" to from="lookup" in the where statement to reverse the result. Thanks!

Get Updates on the Splunk Community!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...