Search Contains / Like between index and csv file as well as NOT Contains / Like

[munisb]

Hi,

I am trying to figure this out - I have a data set that I need to compare the DNS values. The index data contains values like:

hostname1
hostname2.domian.com
hostname3.domain

Whereas the csv file may also contain:
hostname2
hostname1.domain.com
hostname3.domain

I can use JOIN to find values that MATCH exactly; however what I am looking for is "*indexdomain* = "*csvdomain*"

After finding the matches, then in another table display the NOT match results

This is what I have so far - but all of the different variation are returning odd number of hits:

| inputlookup linuxhostnames.csv | rename hostname as DNS | [search index=dnsdata| stats count by DNS | table DNS]

index=dnsdata SUMMARY TRACK=AGENT | dedup DNS | search [ | inputlookup linuxhostnames.csv | rename hostname as DNS]| eval result=if(like(hostname,"%".DNS."%"),"Contained","Not Contained") | table DNS, result

Worse case scenario - I can modify the .csv file and exclude the domain.com and just leave the hostname - but still have a Contains / Like search in index is what I can't seem to figure out.

Will appreciate any guidance. Thanks

[tscroggins · Accepted solution]

@munisb 

Without changing the underlying lookup, this should work:

index=dnsdata SUMMARY TRACK=AGENT NOT [ | inputlookup linuxhostnames.csv | eval hostname="*".hostname."*" | rename hostname as DNS ]

Recall that subsearches of this type are converted to search strings:

( ( DNS="*hostname2*" ) OR ( DNS="*hostname1.domain.com*" ) OR ( DNS="*hostname3.domain*" ) )

You can confirm this with the format command:

|  inputlookup linuxhostnames.csv | eval hostname="*".hostname."*" | rename hostname as DNS | format

search

( ( DNS="*hostname2*" ) OR ( DNS="*hostname1.domain.com*" ) OR ( DNS="*hostname3.domain*" ) )

The expanded search becomes:

index=dnsdata SUMMARY TRACK=AGENT NOT ( ( DNS="*hostname2*" ) OR ( DNS="*hostname1.domain.com*" ) OR ( DNS="*hostname3.domain*" ) )

[munisb]

@tscroggins 

Thanks for the feedback. This brought me closer - but still some of the results are not accurate.

Ex: If I run with NOT - it is returning 6 assets that have the agent tag (TRACK=Agent) instead on NOT having the "AGENT"

and if I remove the TAG - it returns 100s of results. Essentially, showing assets are in the index and NOT in the csv.

Whereas, I am trying to see find assets from CSV in the index that does not have the field TRACK=AGENT (field in the index)

Hopefully, this makes more sense.