Knowledge Management

Comparing logs with inputlookup files

harrysof
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

woodcock
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

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

niketn
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

skalliger
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

starcher
SplunkTrust
SplunkTrust

Consider also making the lookup values wildcard match type.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...