topic Re: InputLookup search query in Splunk Search

InputLookup search query

dyrm1 — Fri, 29 Nov 2019 17:34:49 GMT

Hello everyone!

My initial search give me events with the URLs that users clicked using the outlook client. After a bit of REGEX magic, I have extracted the URL from the event which looks something like "www.Jon.com". I have a CSV file called "URLDatabase" that has very similar content as the text-box below. What I want is to check whether the identified URL "www.Jon.com" is in among the CSV under the url column and if it is to extract/return the full url from the CSV file.

My problem is that the search command after the inputlookup "search= url=web" doesnt treat web as variable but just as word. If I do "search url="www.Jon.com*" it works but I want it to accept a field such as web so it can be dynamic.

CSV file structure (Not using all columns)

pid,url,verdict,process
5654,www.Jon.com/name/dsd.html,Pass,first
5745,www.Michael.com/name/dsdf.html,Fail,first

SPLUNK command

source="WinEventLog:Microsoft-Windows-Sysmon/Operational"  ParentImage:("DATA") Image:("DATA2" OR "DATA3") | rex field=ParentImage "^.*\d(?<website>.*)\.*" |  eval web="\"*".(website)."*\"" | eval foo=[| inputlookup URLDatabase.csv | search url=web | return url | format ] | fields User App Product foo | bucket_time span=20m | stats values(User) as Users values(App) as Application values(Product) as Browser values(foo) as URLs by _time

I have tried multiple ways to make url=web to work but all failed as it is not accepting is a dynamic field/variable"

Any help would be greatly appreciated.

Re: InputLookup search query

to4kawa — Sun, 01 Dec 2019 00:01:09 GMT

You cannot search between fields by searching.

in this case
It will be search url = "web"

therefore,
search url=web
⇨ where url=web

But your SPL is too problematic.

It's better to show the log.

Re: InputLookup search query

dyrm1 — Sun, 01 Dec 2019 00:13:50 GMT

Hello to4kawa

I have tried "where" already but didnt work.

I think the problem is that the query "url=web" happens within "[....]" and therefore is not aware that the web field/variable exists.

any ideas on how to overcome this?

You also mentioned that my SPL is too problematic, can you please elaborate?

Re: InputLookup search query

to4kawa — Sun, 01 Dec 2019 00:23:47 GMT

 source="WinEventLog:Microsoft-Windows-Sysmon/Operational"  ParentImage:("DATA") Image:("DATA2" OR "DATA3") 
| rex field=ParentImage "^.*\d(?<website>.*)\.*"

At this point,
Please tell me some values of website and url .

| makeresults
| eval url="www.Jon.com/name/dsd.html#www.John.com/name/dsd.html#www.Michael.com/name/dsdf.html#*Jon*#*Michael*"
| makemv delim="#" url
| mvexpand url
`comment("check table status")`
| join url [| makeresults
| eval _raw="pid,url,verdict,process
5654,www.Jon.com/name/dsd.html,Pass,first
5745,www.Michael.com/name/dsdf.html,Fail,first"
| multikv forceheader=1
    | table pid,url,verdict,process]

From this result, you can see that the current query does not work properly.

Re: InputLookup search query

dyrm1 — Sun, 01 Dec 2019 00:37:33 GMT

The values would be the following:

url = www.Jon.com/name/dsd.html

website = www.Jon.com

the "url" exists within the CSV file and the "website" is extracted from a sysmon event. So basically I want to check if the extracted value (website) exists in the CSV file under the url column and if it does to capture it.

Re: InputLookup search query

to4kawa — Sun, 01 Dec 2019 00:57:38 GMT

 source="WinEventLog:Microsoft-Windows-Sysmon/Operational"  ParentImage:("DATA") Image:("DATA2" OR "DATA3") 
| rex field=ParentImage "^.*\d(?<website>.*)\.*" 
| join website [|inputlookup URLDatabase.csv 
| rex field=url "(?<website>[^/]+)"
| table website, url]
| fields User App Product url 
| bucket_time span=20m 
| stats values(*) as * by _time

How about it?

Re: InputLookup search query

dyrm1 — Sun, 01 Dec 2019 21:02:16 GMT

Hello to4kawa,

Unfortunately it didn't work 😕

Re: InputLookup search query

woodcock — Sun, 01 Dec 2019 22:24:22 GMT

That is the difference between search and where. Try this:

... | where url==web

Re: InputLookup search query

to4kawa — Mon, 02 Dec 2019 11:35:18 GMT

Check the results line by line.
I can't see your situation.