How to combine foreach command with lookup data?

[AlexeySh]

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.

[woodcock]

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

[AlexeySh]

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"))'.

[woodcock]

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

[HiroshiSatoh · Accepted solution]

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.

[AlexeySh]

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!