Getting Data In

How to use Lookup table to analyze events?

waJesu
Path Finder

I created a lookup table for blacklisted DNS queries. I need a query that uses the lookup table to see if domains in the lookup table are present in events in my environment. 

Labels (1)
Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Assuming you have a "domain" field in both the lookup file and an index, this should get you started.

index=foo [ | inputlookup denieddomains.csv | field domain | format ]

The subsearch (inside square brackets) fetches the contents of the lookup table (I made up a name - replace it with your own), extracts only the "domain" field, then formats the results into a search string which is then returned to the main search for execution.

---
If this reply helps you, Karma would be appreciated.

waJesu
Path Finder

I tried this and it did not return results.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This is a bit vague.

What fields and values do you have in your lookup table?

What corresponding fields do you have in your events?

0 Karma

waJesu
Path Finder

The lookup table has a single field "DNS" with all the blocklisted dns requests e.g. bliss.com, sugar.plux.net etc.. The corresponding field in the events could be dns_queries

0 Karma

waJesu
Path Finder

I meant dns_query

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
index=foo [ | inputlookup denieddomains.csv | field DNS | rename DNS as dns_query | format ]

waJesu
Path Finder

It worked. Thank you very much. May you please explain to me what each part of the query does so that next time I can create personal queries of the same kind.

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The subsearch retrieves the DNS names from the lookup and renames the field so that it matches the field name used in the events. The format essentially expands to something like this 

index=foo (( dns_query="value1") OR (dns_query="value2"))

waJesu
Path Finder

Thank you. I really appreciate.

0 Karma
Get Updates on the Splunk Community!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

Stay Connected: Your Guide to June Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...