topic Comparing logs with inputlookup files in Knowledge Management

Comparing logs with inputlookup files

harrysof — Fri, 17 May 2019 23:30:50 GMT

Hey all,

I have a fairly simple question.

I have a web proxy index that has a url field.

I have a CSV that contains malicious TLD's (.ru, .cn).

I'm trying to create the right query to match the CSV to this field in the web proxy index ?
So if traffic is seen for url=hackingsite.ru, that it gets compared to the CSV and sees that a .ru domain is bad (so it matches).

index=webproxy [|inputlookup MalciousDNSTLD.csv | fields dns] | eval dns=url | table url

But, I know I am missing something from the query, looking for some generous help.

Thanks.

Re: Comparing logs with inputlookup files

skalliger — Sat, 18 May 2019 13:23:36 GMT

Still many people try to use | inputlookup when it's not needed. What you rather want to use is | lookup which maps to your use case perfectly. Be sure to have a lookup definition because usually you don't call lookups by "lookup.csv" but by it's lookup definition name (stanza name).
Will be something like

 index=webproxy
| lookup MalciousDNSTLD field1 AS field2 OUTPUT field2 as field2
| where isnull(field2)

.. or isnotnull() depending what you want (is in the badurls list or isn't.
Look at the docs for a description of which field to use where. It's rather simple. 🙂

Skalli

Re: Comparing logs with inputlookup files

niketn — Sat, 18 May 2019 13:58:17 GMT

@harrysof depends on what is the value of url field in the webproxy index and what is the value of dns in the TLD csv file?

Based on the description seems like TLD csv only has TLD info like .ru or .cn. What are you matching this against in the index?

Re: Comparing logs with inputlookup files

starcher — Mon, 20 May 2019 17:51:39 GMT

Consider also making the lookup values wildcard match type.

Re: Comparing logs with inputlookup files

woodcock — Sat, 06 Jul 2019 18:40:40 GMT

First, install URL toolbox:
https://splunkbase.splunk.com/app/2734/

Then do this:

index=webproxy
| lookup ut_parse_extended_lookup url
| rex field=ut_domain "\.(<tld>[^\.]+)$"
| lookup MalciousDNSTLD.csv dns AS tld OUTPUT dns AS malicious
| where isnotnull(malicious)