topic Re: Query with Thousands of "OR"s in Splunk Search

Query with Thousands of "OR"s

ccsfdave — Mon, 16 Dec 2013 17:55:54 GMT

Greetings,

I want to know the least resource intensive way of searching thousands of URLs in one search. So what I am doing is taking the Infragard warnings and then building them into queries enterprise-wide. The latest warning had about 2500 URLs that have been used for DDoS and Botnets. Right now I have a scheduled search with URL OR URL OR URL....etc.

Is there a better way to do this? When I want to adjust the search, I have to pull it into a text editor and then put it back because Splunk web will crawl while I mess with it.

Thanks and let me know if I need to be more specific.

Dave

Re: Query with Thousands of "OR"s

richgalloway — Mon, 16 Dec 2013 18:36:01 GMT

Is there some other common field you could use in your search?

Re: Query with Thousands of "OR"s

somesoni2 — Mon, 16 Dec 2013 18:46:14 GMT

If the list of URL are static, then you can create a lookup table with them.[create a csv file with all URL entry,one per row. Manager->Lookups->lookup table files]. The you can use this lookup file in your search query to filter out events.

your search | inputlookup lookup_urls.csv

Updated search

Try this.,Assuming your lookup table have just one field named URL

yoursearch [|inputlookup lookup_urls.csv | rename URL as search | fields search]

This search will put all the URLs from the lookup table as values and append it to your base search. E.g. if you have URL values like "abc.com", "xyz.com", "pqr.com" in the lookup file, it will become

your search  (("abc.com") OR ("xyz.com") OR ("pqr.com"))

Re: Query with Thousands of "OR"s

ccsfdave — Mon, 16 Dec 2013 19:49:30 GMT

Hmm, let me give that a try. I'll report back on how it performs. Thanks

Re: Query with Thousands of "OR"s

ccsfdave — Mon, 16 Dec 2013 20:49:47 GMT

This method doesn't seem to work. First, it won't let me put anything before the |inputlookup. Second, if I run the lookup on its own, it gives me a list of the URLs. If I follow the lookup and file with the rest of my search it comes up with no results because it is not searching the index for the lookup file and then formatting a table from there.

Any thoughts?

Re: Query with Thousands of "OR"s

somesoni2 — Mon, 16 Dec 2013 21:10:44 GMT

Try the updated answer.

Re: Query with Thousands of "OR"s

ccsfdave — Mon, 16 Dec 2013 21:37:48 GMT

My original search was:

http://abc.com OR http://def.com OR ... |search Department="*"| rename src_ip as "IP" |table Department, IP | dedup IP

I am trying to format the revised answer above into it but am having no luck.

Re: Query with Thousands of "OR"s

somesoni2 — Mon, 28 Sep 2020 15:29:47 GMT

Ensure that lookup file name (lookup_urls.csv) and field name (URL) are correct.

Re: Query with Thousands of "OR"s

ccsfdave — Tue, 17 Dec 2013 19:16:32 GMT

@somesoni2, this worked! Thanks!!!

Re: Query with Thousands of "OR"s

Dmitriy — Thu, 05 Aug 2021 04:00:48 GMT

Hi, please help, i have 6500 IIN (like id) and put this id to lookup then tried search: index=alfa [|inputlookup IIN_oleg.csv |rename IIN as search | fields search]

They give result only for one firs IIN in lookup.

If i search whit out lookup just 10 IIN use OR the give me 10 result

Re: Query with Thousands of "OR"s

kamlesh_vaghela — Thu, 05 Aug 2021 04:58:48 GMT

@Dmitriy

index=alfa [|inputlookup IIN_oleg.csv | dedup IIN |table IIN | format "(" "(" "AND" ")" "OR" ")"

The assumption is IIN field is also in alfa index events.

If you need further help, I will suggest to post new question with your requirement.