Splunk Search

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

munisb
Explorer

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

Labels (5)
Tags (2)
0 Karma
1 Solution

tscroggins
Motivator

@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*" ) )

View solution in original post

0 Karma

tscroggins
Motivator

@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*" ) )

0 Karma

munisb
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...