topic Re: How to use Lookup table to analyze events? in Getting Data In

How to use Lookup table to analyze events?

waJesu — Wed, 30 Aug 2023 16:57:27 GMT

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.

Re: How to use Lookup table to analyze events?

ITWhisperer — Wed, 30 Aug 2023 17:41:22 GMT

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?

Re: How to use Lookup table to analyze events?

richgalloway — Wed, 30 Aug 2023 17:45:14 GMT

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.

Re: How to use Lookup table to analyze events?

waJesu — Thu, 31 Aug 2023 10:51:34 GMT

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

Re: How to use Lookup table to analyze events?

waJesu — Thu, 31 Aug 2023 10:52:22 GMT

I meant dns_query

Re: How to use Lookup table to analyze events?

waJesu — Thu, 31 Aug 2023 10:56:47 GMT

I tried this and it did not return results.

Re: How to use Lookup table to analyze events?

ITWhisperer — Thu, 31 Aug 2023 11:04:43 GMT

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

Re: How to use Lookup table to analyze events?

waJesu — Fri, 01 Sep 2023 11:06:24 GMT

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.

Re: How to use Lookup table to analyze events?

ITWhisperer — Fri, 01 Sep 2023 11:14:52 GMT

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

Re: How to use Lookup table to analyze events?

waJesu — Tue, 05 Sep 2023 10:50:54 GMT

Thank you. I really appreciate.