topic Re: How to check for data that is not present in csv lookup in Getting Data In

How to check for data that is not present in csv lookup

nnimbe1 — Thu, 19 Apr 2018 13:57:15 GMT

I have DHCP logs and a csv which contains hostnames of devices..

I need to check the DHCP logs for the hostnames that are not present in the csv lookup list.

Can you please suggest a query to perform this check without using subsearch? CSV has some 55K rows

Re: How to check for data that is not present in csv lookup

adonio — Thu, 19 Apr 2018 15:14:33 GMT

hello there,
not sure how to achieve without a subsearch as you will need to compare to the lookup with |inputlookup
look at those answers for examples:
https://answers.splunk.com/answers/31578/search-to-determine-what-is-missing-in-lookup-table.html
https://answers.splunk.com/answers/142176/can-splunk-check-for-hosts-sending-data-against-a-lookup-file.html
https://answers.splunk.com/answers/73268/search-for-hosts-in-a-lookup-but-not-in-splunk.html
hope it helps

Re: How to check for data that is not present in csv lookup

damien_chillet — Thu, 19 Apr 2018 15:18:15 GMT

Can you try

index=dhcp 
| lookup dhcp_hosts hostname OUTPUT hostname as filter
| where is null(filter)

Re: How to check for data that is not present in csv lookup

elliotproebstel — Thu, 19 Apr 2018 15:18:48 GMT

Assuming your DHCP logs contain a field called host and the CSV file contains a field called hostname, your query could look like this:

your DHCP log search that contains host field
| lookup device_hostname_lookup.csv hostname AS host OUTPUT hostname AS flag
| where isnull(flag)

This is performing a lookup for the field host in the source log, matching to hostname in the CSV and - if there is a match - adding a field called flag to the source event. The final line filters out any events that contain the field flag (removing all events that had matching hostnames in the CSV file).