Limiting a search using a CSV lookup file

[ks5752]

Hi, I saw similar questions about this. I've tried the suggestions and have been unsuccessful. Please excuse the redundant question, but I thought if I post a question that is very customized to my issue, it may help...

Ok.... I'm the newest of newbies! I've been messing with this issue for hours and still have no solution. I would like to control a search using criteria in a csv file. I have a single column spreadsheet with two or three values in as many rows. I save out of excel as a csv file.

from there, I load in the csv file under MANAGER>LOOKUPS>LOOKUP TABLE FILES. for illustration, I name this file testlookup.csv

from there, I define the lookup under MANAGER>LOOKUPS>LOOKUP DEFINITIONS. I use a FILE BASED definition and call the lookup testlookup and reference the testlookup.csv file previously loaded.

then, when I run the search:

host="myhost" (limiting the time)

I get a return of over 500 records

then, if I manually put in the criteria that is in the csv file, that limits my search to 8 records

host="myhost" string1 string2

however, when I attempt to use the lookup, I can not get any results.

host="myhost" | inputlookup testlookup

this search throws an error "Error in 'inputlookup' command: This command must be the first command of a search."

FINALLY, any other combinations that I have tried with LOOKUP, INPUTLOOKUP etc yield either an error or 0 results....

any assistance would be greatly appreciated.

With this exercise, I'm trying to get this to work as an AND function, but would also appreciate any guidance to have the CSV file records perform the OR function as well.

Kos

[martin_mueller]

Consider this:

| gentimes start=-10 increment=5m | search [| inputlookup test.csv | fields starttime]

With a test.csv containing this:

starttime
1359936000
1359936300
1359936600
1359936900
1359937200

Instead of the 2880 results without the search you'll get only five - those matching the values in the CSV.

[ks5752]

this works. it is a great example, but for the life of me, I can't seem to get my csv file to provide the variable for more than the first record... for instance, if I had a CSV file with one field (f1) with the records of VALUE1, VALUE2, VALUE3 when I use the subquery I've only been successful with returning records that match VALUE1. it seems that VALUE2 & VALUE3 don't even come into play.

[Ayn]

I think it would be a good idea to use a subsearch to achieve what you want. Something like

host="myhost" [|inputlookup testlookup | return $yourlookupfield]

(replace yourlookupfield with what it's actually called in your CSV file)

This will first of all run the search within the brackets, and expand that search's results in a particular format that's suitable to be used as a filter string by the outer search command. Read more about subsearches here:
http://docs.splunk.com/Documentation/Splunk/5.0.1/Tutorial/Useasubsearch
http://docs.splunk.com/Documentation/Splunk/5.0.1/Search/Aboutsubsearches

In this case, when the subsearch is done and returns its results it will be expanded to something like

host="myhost" string1 string2

which should be in line with what you want.

[ks5752]

OK... as fate would have it, shortly after clicking go, I stumbled upon the syntax of RETURN by adding the OPTIONAL COUNT # to the mix, I am seeing more stuff....and it appears to perform the OR function for me.

host="myhost" [|inputlookup testlookup | return $yourlookupfield]

so that leads me to an academic question.... is there a way to use this same technique, but perform the AND function for the lookup variables?

[ks5752]

Ok, first off thanks for the assistance... I'm learning. when I incorporate this suggestion, it works to a point, but the records returned are ONLY based on the first entry in the CSV file. It does not seem to do anything with the other entries in the lookup file. Any suggestions on that?

Additionally, I would prefer the lookup file to provide the variables for an OR function.