Match url from lookup file to the webgateway access logs and return matched URL as a output result

[etric]

Hello Team,

We have a lookup file containing multiple columns, one column contains a list of URL's having column name as url_list.

Now we are ingesting web gateway logs to splunk, now we would like to match the query in splunk which will return the URL as an output match value when the user will try to browse any sites listed within the CSV lookup file.

I am trying below query,

| inputlookup URL | map search="search index=mcafee sourcetype=mcafee:webgateway url=\"$url_list$\"" | stats count by usrName, time_taken, httpStatus, method, dstIP, urlCategories

But unfortunately, this query is not matching with the value listed within the CSV file.

Any inputs will be appreciated!

Thanks,

[damien_chillet]

Try the following:

index=mcafee:web gateway [|inputlookup URL | rename url_list as url | fields url] 
| stats count by usrName, time_taken, httpStatus, method, dstIP, urlCategories

[etric]

Hello,

Thank you so much for your prompt response. This query is perfect and looks good to use in production.

However, one interesting thing I have observed here is, I have two lookup files,

First Lookup file format

id_number, time, url_list, .............

We are using above query posted by you for above CSV lookup file format which works perfectly.

But when I am trying to use the same query for my second lookup table it won't work

Second Lookup format

county, contact, address, domain, ............

Now, the same thing I would like to execute for the above lookup format, in turn, I am using below query,

index=mcafee sourcetype=mcafee:webgateway [|inputlookup URL_TWO| rename domain as url | fields url] | stats count by usrName, time_taken, httpStatus, method, dstIP, urlCategories

Unfortunately, this query is not matching with the value listed within the CSV file, whereas the same query works perfectly for the first lookup table format.

Can you please suggest where I am missing in this query?

Thanks,

[damien_chillet]

Hi etric,

Are you sure domain field and url field hold the same kind of values?

For example:
http://www.splunk.com/home is a url
splunk.com is a domain

If you have a field in your mcafee:webgateway sourcetype holding domain values, you should use that instead:

index=mcafee:web gateway [|inputlookup URL_TWO | rename domain as <domain_field> | fields <domain_field>] 
 | stats count by usrName, time_taken, httpStatus, method, dstIP, urlCategories

[etric]

Hello,

Apologies for the delayed in response.

Second Lookup format is as follow:

domain, severity, date, category, type, contact, pin, url, .....................................

here the interesting thing is domain holds splunk.com whereas url holds http://www.splunk.com/home is a url

Now we would like to match the domain values in csv with the url field value in splunk which will return the domain as an output match value when the user will try to browse any sites listed within the CSV lookup file.

index=mcafee contains field named url

I am using the below query:

index=mcafee sourcetype=mcafee:webgateway [|inputlookup second_lookup | rename domain as url | fields url] | stats count by usrName, time_taken, httpStatus, method, dstIP, urlCategories, url

But unfortunately it is not returning any result, Can you please suggest where I am missing in this query?

Thanks,
Etric

[etric]

Hello,

Apologies for the delayed in response.

Second Lookup format is as follow:

domain, severity, date, category, type, contact, pin, url, .....................................

here the interesting thing is domain holds splunk.com whereas url holds http://www.splunk.com/home is a url

Now we would like to match the domain values in csv with the url field value in splunk which will return the domain as an output match value when the user will try to browse any sites listed within the CSV lookup file.

index=mcafee contains field named url

I am using the below query:

index=mcafee sourcetype=mcafee:webgateway [|inputlookup second_lookup | rename domain as url | fields url] | stats count by usrName, time_taken, httpStatus, method, dstIP, urlCategories, url

But unfortunately it is not returning any result, Can you please suggest where I am missing in this query?

Thanks,
Etric

[damien_chillet]

Not 100% sure what you're willing to accomplish, but could you try the following eventually?

index=mcafee:web gateway 
| lookup second_lookup url OUTPUT domain
| stats count by usrName, time_taken, httpStatus, method, dstIP, urlCategories, domain