Splunk Search

How to should I perform a lookup between a url field and a url column inside a kv store file?

New Member

Hi guys,

I'm figuring out which steps should I follow in order to perform a lookup between a url field and a url column inside a kv store file.

For example, my events bring this url field like this

url=graph.facebook.com/ (here, the last character for all url fields finish with "/")

But, let's say that my lookup table will have URL like this

url=graph.facebook.com

For some reason, if I perform the search "| lookup threat_source field_threat as url" , I cannot get any results because of the last "/" character in the original even, which is not included in my lookup file.

We were thinking about performing a wildcard in order to search for any string.

Please any help would be appreciated.

Regards,

Tags (4)
0 Karma

Esteemed Legend

Like this:

mysearch
| eval url = case(service_field==HTTP,"http://", service_field==HTTPS,"https://") . url
| rex field=url mode=sed "s/\s*\/\s*$//"
| lookup threat_source url_threat AS url
| rename COMMENT AS "Do NOT use '|table url' because this THROWS AWAY the fields that are output by your lookup call!"
0 Karma

New Member

thanks for your comment my friend.

I'm just a little confused about the last "rename" statement, which field should I rename ? url ?

I really sorry since I'm new using splunk and I've just started to understand the logic of the application.

0 Karma

Communicator

Go ahead and add a eval to replace the forward slash before the lookup.

yoursearch | eval url=replace (url,"/"," ") | lookup yoursearch
0 Karma

New Member

Sorry to bother again, but I think I have to restate my first post....

Original url field coming from events will use this format
url=graph.facebook.com/ (here, the last character for all url fields finish with "/")

The lookup table shows this
url=http://graph.facebook.com

I tried this search

mysearch | eval url=case(service_field==HTTP,"http://".url,service_field==HTTPS,"https://".url) | eval url=replace(url,"/"," ") | lookup threat_source url_threat as url | table url

Since my original log brings http and https services, I was thinking about adding the "http(s)://" and deleting the "/", but I do not get results neither.

0 Karma

SplunkTrust
SplunkTrust

Hi you need couple of corrections, forward slash needs to be escaped with back slashes. Replace command should remove final forward slash in original url needs to be replaced with empty string not space. Try the following instead.

mysearch 
| eval url=case(service_field=="HTTP","http:\/\/".url,service_field=="HTTPS","https:\/\/".url) 
| eval url=replace(url,"/","")
| lookup threat_source url_threat as url 
| table url
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

New Member

Hi my friend, thanks again for your help.

I tried your suggestion, but back slashes appears as new url value if I print the results, something like this "https:\example[.]com".

In order to be sure that there is a match between both url values (event url and url in lookup db) I performed and eval
my search | eval url=case(service=="HTTP","http:\/\/".url,service=="HTTPS","https:\/\/".url)
| eval url=replace(url,"/","") | lookup threat_source url_threat as url | eval url_state = if(url_threat==url,"MATCH","NO MATCH") | table _time,src,dest,url,url_state

Unfortunately, I get "no match" for the final table.

0 Karma

New Member

Thanks for your response, I will try the replace

0 Karma