How to compare a look up field with multivalued indexed data in splunk?

[Richy_s]

I am trying to write a splunk query. I have asset inventory data with hostname and IP address(multivalued), one hostname will have multiple IP address. And I have indexed data in Splunk with a field called Hostname(this is mix of hostname and IP addresses of some random assets).  Now I need to compare the asset inventory data with the indexed data,  and the output should be hostname & IP address that is not present in the indexed data.

Sample data - 

index=asset_inventory | table hostname IPaddress

output

hostname IPaddress

abc 0.0.0.0

abc 2.2.2.2

abc 3.3.3.3

def 1.1.1.1

xyz 4.5.6.7

Indexed data - 

index=indexed_data | stats count by Reporting_Host

Reporting_Host

3.3.3.3

def

Expected output -

Host_not_present

xyz

Can someone help with with a Splunk query to get desired output.

[Richy_s]

Hi @yuanliu 

It seems that the current query is only retrieving results from the 'myinventory' lookup without performing the intended comparison with the 'asset_inventory' data. It appears that you need to modify the query such that it compares both datasets ('myinventory' and 'asset_inventory') and returns only the discrepancies between the two.

[yuanliu]

The problem is actually deeper because appendcols works only if the lookup and index search has the same number of rows (and sort order).  In this use case, that's opposite to the premise.  I will have to look deeper - but there should be something - it could be even more cumbersome.

[yuanliu · Accepted solution]

Like @richgalloway said, Splunk is not great at searching for for missing things.  Meanwhile, if you already have the inventory, there is something you can do.

Assuming lookup myinventory is in the form of

hostname	IPaddress
abc	0.0.0.0
abc	2.2.2.2
xyz	4.5.6.7
zab	7.8.9.10
zab	6.7.8.9

and the requirement is to capture the following entries from the lookup where

1. hostname in this lookup has no matching entry with hostname in index search and
2. IPaddress in this lookup has no matching entry with IPaddress or hostname in index search.

To make our task simpler, further assume that if an index search event matches anything in lookup, that hostname and/or IPaddress is/are no longer a candidate.  This is what you can try:

index=asset_inventory
| stats values(hostname) as hostname values(IPaddress) as IPaddress
| appendcols
    [inputlookup myinventory
    | stats values(hostname) as lookupname values(IPaddress) as lookupaddress]
| eval missingname = mvmap(lookupname, if(lookupname != hostname, lookupname, null()))
| eval missingaddress = mvmap(lookupaddress, if(lookupaddress != IPaddress AND lookupaddress != hostname, missingaddress, null()))
| lookup myinventory IPaddress as missingaddress output hostname as addressmissingname
| eval missingname = mvappend(missingname, mvmap(addressmissingname, if(addressmissingname != hostname, addressmissingname, null())))
| table missingname

Note:

1. the search takes avdantage of Splunk's equality evaluation with multivalue.
2. this search becomes complicated because your index search may return IP address in hostname and apparently you care about those entries.  If we ignore those entries and only compare hostname hostnames with inventory, the search can be as simple as

index=asset_inventory
| stats values(hostname) as hostname
| appendcols
    [inputlookup myinventory
    | stats values(hostname) as lookupname]
| eval missingname = mvmap(lookupname, if(lookupname != hostname, lookupname, null()))
| fields - hostname

[Richy_s]

Hello @yuanliu , your suggestion was exactly what I needed. Thanks to your initial query, I was able to achieve the desired outcome with some adjustments. Your detailed explanation was greatly appreciated.

[richgalloway]

Finding something that is not there is not Splunk's strong suit.  See this blog entry for a good write-up on it.

https://www.duanewaddle.com/proving-a-negative/

[Richy_s]

Thank you @richgalloway     for your insightful article that provided me with a good starting point.