Getting Data In

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

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

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

Can you try

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

SplunkTrust
SplunkTrust
0 Karma