Splunk Search

How to combine foreach command with lookup data?

Communicator

Hello,

In order to clean our filtering rules we'd like to check if some of our old URL's are still in use (an if yes - how many times in last 90 days). Basically we'd like to perform the query below:

index=nginx sourcetype="nginx:plus:access"
| search uri_path=<uri_path_we_are_searching_for> 
| stats count

The problem is that there are almost 600 URL's we need to check.

We'd like to know if there is a way to put all the URL's in a lookup and then perform a kind of foreach search.

Thanks for the help.
Alex.

0 Karma
1 Solution

Champion

Try this!

index=nginx sourcetype="nginx:plus:access"  [|inputlookup your_filename|table uri_path]  
| stats count
↓
index=nginx sourcetype="nginx:plus:access" (uri_path="XXX" OR uri_path="YYY" OR uri_path="XXX")

Or it can be linked using the LOOKUP command.

View solution in original post

Esteemed Legend

Pur your 600 URLs in a lookup file called uri_path.csv with a single field named uri_path and then do this:

index=nginx sourcetype="nginx:plus:access"
|inputlookup append=true uri_path.csv
| stats count(eval(sourcetype="nginx:plus:access")) AS count BY uri_path
0 Karma

Communicator

Hi @woodcock ,

Thanks for the help, but unfortunately I was not able to execute the query because of an error:
'Error in 'stats' command: You must specify a rename for the aggregation specifier on the dynamically evaluated field 'count(eval(sourcetype="nginx:plus:access"))'.

0 Karma

Esteemed Legend

I edited .my answer and fixed it. Try it now.

0 Karma

Champion

Try this!

index=nginx sourcetype="nginx:plus:access"  [|inputlookup your_filename|table uri_path]  
| stats count
↓
index=nginx sourcetype="nginx:plus:access" (uri_path="XXX" OR uri_path="YYY" OR uri_path="XXX")

Or it can be linked using the LOOKUP command.

View solution in original post

Communicator

Hi @HiroshiSatoh

Almost what I wanted to find. I just modified the second row in order to have a stats by each uri_path:

| stats count by uri_path

Thanks for the help!

0 Karma