Hi all,
Been racking my brain trying to create this search and I can't seem to get it working, so I was hoping you all could help me out. I'm going to try and be as specific and clear as possible, so I apologize in advance for the long question.
We are currently importing DNS logs into Splunk. If I visit www.splunk.com, and then look at the DNS logs in Splunk with the following search:
index="dns" src_ip="{my IP address}"
the query field in my search results looks like this: (3)www(6)splunk(3)com(0)
So I added the following regex to make it look like a normal URL:
Convert the parenthesis and digits to periods
| rex field=query mode=sed "s/\(.*?\)/./g"
Remove the period at the beginning of the string
| rex field=query mode=sed "s/^\.//g"
remove the period at the end of the string
| rex field=query mode=sed "s/\.$//g"
My query field now looks like this: www.splunk.com
Great. Now, what I would like to do is compare it with a lookup table of malicious domains.
index="dns" src_ip="{my IP address}" | rex field=query mode=sed "s/\(.*?\)/./g" | rex field=query mode=sed "s/^\.//g" | rex field=query mode=sed "s/\.$//g" | search [|inputlookup MaliciousDomains.csv | rename domain AS query | fields query]
I know for a fact that there should be results (because I added common domains such as amazon.com to the MaliciousDomains.csv file for testing purposes), and it's not returning any. I have a feeling that it's related to the fact that I seem to have to specify a field in order for the second search to return results.
Anybody have any ideas as to what is going on?
The problem turned out not to be with my search query but with my lookup table. My lookup table (MaliciousDomain) had around 17,000 rows. As a test, I deleted about 7,500 rows in the lookup table and then re-uploaded it to Splunk. The search query worked fine with the newly uploaded lookup table.
This Splunk Answers question has more information regarding lookup table limit sizes:
https://answers.splunk.com/answers/8228/lookup-table-limits.html
The problem turned out not to be with my search query but with my lookup table. My lookup table (MaliciousDomain) had around 17,000 rows. As a test, I deleted about 7,500 rows in the lookup table and then re-uploaded it to Splunk. The search query worked fine with the newly uploaded lookup table.
This Splunk Answers question has more information regarding lookup table limit sizes:
https://answers.splunk.com/answers/8228/lookup-table-limits.html
You rex sed
commands are updating the field value (field=query) but not the raw data. With no field specified in the | search
command, it's searching that string in raw data (_raw field), thus it fails as raw data still has old format. Another issue is that your subsearch is returning field query. The field name 'query' and 'search' are special fields in Splunk and when they are used as return field in subsearch, instead of returning key=value
, they just return the value
. So your subsearch is searching for string in your raw data. Your options would be.
1) Modify your rex-sed commands to work on raw data. That way your current subsearch syntax would work fine.
2) Rename the field query to something else in the base search, before applying your subsearch filter. I would say make it same as the name available in lookup table.
sourcetype="MSAD:NT6:DNS" src_ip="10.2.0.63" | rex field=query mode=sed "s/\(.*?\)/./g" | rex field=query mode=sed "s/^\.//g" | rex field=query mode=sed "s/\.$//g" | rename query as domain | search [|inputlookup MaliciousDomains.csv | table domain]
Hi somesoni2, thanks for your response. I understand your first approach, it makes sense that it is searching again the raw data. Thanks for clarifying that. I tried the second approach (renaming the query field to domain), and it still did not return any results. However, I believe I discovered that my problem was with my lookup table, not with my search query. Please see my answer