Archive

Comparing logs with inputlookup files

Explorer

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.

Tags (2)
0 Karma
1 Solution

Esteemed Legend

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)

View solution in original post

Esteemed Legend

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)

View solution in original post

Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

SplunkTrust
SplunkTrust

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

SplunkTrust
SplunkTrust

Consider also making the lookup values wildcard match type.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!