Getting Data In

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

nnimbe1
Path Finder

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

0 Karma

elliotproebstel
Champion

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).

0 Karma

damien_chillet
Builder

Can you try

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

adonio
Ultra Champion
0 Karma
Get Updates on the Splunk Community!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...