Splunk Search

Keyword search using a lookup table and other search value

Armyeric
Path Finder

I am trying to take a saved search, with a large ammount of keywords and make the saved search smaller and more versitile by using a lookup table. Here is the current search (it is a sensitive word search based on what search engine is used):

index="bcproxy" sourcetype="bcoat_proxysg" dest_host="www.google.com" 
(nude OR nudity OR naked OR milf OR bondage OR porn OR nsfw OR bikini OR erotic OR eros OR xxx OR ecstacy OR softcore OR hardcore OR sex OR sexy OR flashing OR amatuer OR topless) 
| top Google_Searches,user limit=20 showcount=0 showperc=0 
| rename user as Google_User 
| appendcols [search dest_host="search.yahoo.com" (nude OR nudity OR naked OR milf OR bondage OR porn OR nsfw OR bikini OR erotic OR eros OR xxx OR ecstacy OR softcore OR hardcore OR sex OR sexy OR flashing OR amatuer OR topless)
    | top Yahoo_Searches,user limit=20 showcount=0 showperc=0 
    | rename user as Yahoo_User] 
| appendcols [search dest_host="www.bing.com" (nude OR nudity OR naked OR milf OR bondage OR porn OR nsfw OR bikini OR erotic OR eros OR xxx OR ecstacy OR softcore OR hardcore OR sex OR sexy OR flashing OR amatuer OR topless) 
    | top Bing_Searches,user limit=20 showcount=0 showperc=0 
    | rename user as Bing_User]

As you can see, this is a rather large search (I took out other keywords). This is what I am trying to do but it is not giving me any results:

index="bcproxy" sourcetype="bcoat_proxysg" (dest_host="www.google.com" ([|inputlookup keyword])  ) 
| top Google_Searches,user limit=20 showcount=0 showperc=0 
| rename user as Google_User 

I am just trying to make the first part work and then I figured it would be easy to add the other parts. I do have the csv loaded into Splunk and the lookup definition created.

Any suggestions on getting this to run properly would be greatly appreciated.

Here is the debug for the shortend query:

did not return any data. Possible solutions are to:
•relax the primary search criteria
•widen the time range of the search
•check that the default search indexes for your account include the desired indexes

The following messages were returned by the search subsystem:
DEBUG: Subsearch evaluated to the following search expression: ( ( keyword="nude" ) OR ( keyword="nudity" ) OR ( keyword="porn" ) OR ( keyword="nsfw" ) OR ( keyword="bikini" ) OR ( keyword="erotic" ) OR ( keyword="eros" ) OR ( keyword="xxx" ) OR ( keyword="ecstacy" ) OR ( keyword="softcore" ) OR ( keyword="hardcore" ) OR ( keyword="sex" ) OR ( keyword="sexy" ) OR ( keyword="flashing" ) OR ( keyword="amatuer" ) OR ( keyword="topless" ) )
DEBUG: [SRV-SI001] search context: user="joeuser", app="searchdev", bs-pathname="H:\Splunk\var\run\searchpeers\SRV-SS001-1372108101"
DEBUG: [SRV-SI002] search context: user="joeuser", app="searchdev", bs-pathname="H:\Splunk\var\run\searchpeers\SRV-SS001-1372108101"
DEBUG: [SRV-SI001] search context: user="joeuser", app="searchdev", bs-pathname="H:\Splunk\var\run\searchpeers\SRV-SS001-1372108101"
DEBUG: [SRV-SI002] search context: user="joeuser", app="searchdev", bs-pathname="H:\Splunk\var\run\searchpeers\SRV-SS001-1372108101"
DEBUG: [subsearch]: Successfully read lookup file 'H:\Splunk\etc\apps\searchdev\lookups\porn_keywords.csv'.
DEBUG: base lispy: [ AND com google index::bcproxy sourcetype::bcoat_proxysg www [ OR amatuer bikini ecstacy eros erotic flashing hardcore nsfw nude nudity porn sex sexy softcore topless xxx ] ]
DEBUG: search context: user="joeuser", app="searchdev", bs-pathname="H:\Splunk\etc"

Tags (2)

Armyeric
Path Finder

I have added the debug info to my original post

0 Karma

Ayn
Legend

Edit your question with the info. It's hard to help without seeing what the lookup looks like.

0 Karma

Armyeric
Path Finder

I created the csv in vi to avoid any extra characters. I don't have access to the config files, so I am not sure what the admin would have set it up as. I did create both using Lookup Table Files and Lookup Deffinitions. I am seeing the search words listed in the debug message. I would put the message in here but the character limitation wont let me get the bulk of it listed.

0 Karma

hartfoml
Motivator

Try this

index="bcproxy" sourcetype="bcoat_proxysg" dest_host="www.google.com" [|inputlookup keyword | rename keyword_header AS query | fields query] | top 20 Google_Searches user showcount=0 showperc=0 | rename user as Google_User

Let me know if this works for you.

Armyeric
Path Finder

I got this to work using hartfoml first solution and reversing the two queries: index="bcproxy" sourcetype="bcoat_proxysg" ([|inputlookup porn_keywords.csv|rename keyword as query | fields query] AND dest_host="www.google.com")

I also found a comment made by Ayn to be helpful as well to explain a few things: http://splunk-base.splunk.com/answers/38520/how-to-generate-a-report-for-searching-the-request-from-...

Armyeric
Path Finder

keyword,
nude,
nudity,
...

I don't see a place were I can add an image, but yes, it returns with keyword at the top and the list words below.

0 Karma

hartfoml
Motivator

I was assuming you have a header on your keyword lookup file. if you do this |inputlookup keyword do you get a column header in your keyword file? you need to rename that keyword_header as query.

0 Karma

Ayn
Legend

See my q - can you paste the start of your lookup so we can see what it looks like?

0 Karma

Armyeric
Path Finder

It did not. Here is the job inspector results:

DEBUG: Subsearch evaluated to the following search expression: NOT ()
DEBUG: [subsearch]: Successfully read lookup file 'H:\Splunk\etc\apps\searchdev\lookups\porn_keywords.csv'.
DEBUG: search context: user="joeuser", app="searchdev", bs-pathname="H:\Splunk\etc"
INFO: The specified search will not match any events

0 Karma

Ayn
Legend

What does your lookup file look like? Have you setup it properly so there's a transforms.conf entry for it called "keyword"?

0 Karma
Get Updates on the Splunk Community!

Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...