Splunk Search

Query with Thousands of "OR"s

ccsfdave
Builder

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

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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

Dmitriy
Explorer

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

KV

 

0 Karma

ccsfdave
Builder

@somesoni2, this worked! Thanks!!!

0 Karma

somesoni2
Revered Legend

Try this
Department=\"*\" [|inputlookup lookup_urls.csv | rename URL as search | fields search]| rename src_ip as "IP" |table Department, IP | dedup IP

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

ccsfdave
Builder

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.

0 Karma

somesoni2
Revered Legend

Try the updated answer.

0 Karma

ccsfdave
Builder

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?

0 Karma

ccsfdave
Builder

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...