Keyword search using a lookup table and other search value

[Armyeric]

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"

[Armyeric]

I have added the debug info to my original post

[Ayn]

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

[Armyeric]

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.

[hartfoml]

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]

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]

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.

[hartfoml]

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.

[Ayn]

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

[Armyeric]

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

[Ayn]

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