Splunk Search

How to combine foreach command with lookup data?

AlexeySh
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

HiroshiSatoh
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

woodcock
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

AlexeySh
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

woodcock
Esteemed Legend

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

0 Karma

HiroshiSatoh
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.

AlexeySh
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
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...