Comparing logs with inputlookup files

[harrysof]

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.

[woodcock · Accepted solution]

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)

[niketn]

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

[skalliger]

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

[starcher]

Consider also making the lookup values wildcard match type.