I want to be able to do a search of an index with search parameters returned from a database lookup. An example would be a table like:
User Name | Employee ID
Then my search could either search the index for all Names or be able to pass an employee id to just return the one name. I would eventually want to create a view with a form where the user can either enter an Employee ID or * for all. I have installed DB Connect and can view the database and run queries but can't figure out how to get the lookup to work from an normal search command from my app. Can anyone tell me if this is possible and if so how it can be done or where I should start with?
I was able to get this almost working using this search
index=* [ | dbquery "dbName" "SELECT empName FROM tblName where email@example.com'"]
The problem I am now having is the name is found in one of my indexes but not the other. The difference is in one index the name is upper/lowercase just like the db and it is found. On the ohter is is all lowercase so it doesn't seem to find it. I have tried using LOWER(empName) inside the sql cmd and also eval lower([.....]) but neither return any results. I know splunk is supposed to be case insensitive but I have found some posts that say that may not be true for some types of data. Anyone have any ideas how I can get this last piece to work.
It may be wrong because it is not good English.
Or that the field name that becomes lowercase problem?
May be renamed in the sub-search if so
[(your subsearch)|renmae empname as empName]
Thanks for the reply. All the column names are spelled correctly. To prove it was an upper/lowercase issue I changed the name in my test database to all lower case and it is then able to find it in the second index but not the one it was before. Again this is only on a test database so this will not work as a production solution.
Does "query" know?
Sub search result
(A="1") OR (A="2") OR (A="3")
If a field name is changed into "query"
("1") OR ("2") OR ("3")
[(your subsearch)|renmae empname as query]
When you click on the job inspector (the blue 'i' icon near the time picker), what does it say the subsearch evaluated to? I'd be more concerned that the dbquery spat out an error or found no results than case sensitivity.
I never tried the "blue 'i' icon". That is a very cool feature. It did help me figure out the problem is not lowercase at all. My query not only returns the name but also returns the db column like empName="Joe". It appears it is only searching for a field called empName with Joe. This is fine for the first index but the second index the field is called firstName. When I change the dbquery to Select empName as firstName.... it then finds it in the second query but not the first. What I really want to do is search every field in the index, any ideas on how to get rid of the field name?