Splunk Search
Highlighted

Search index using search values from database

Engager

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
Jim 1234
Joe 2345

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?

Update:

I was able to get this almost working using this search

index=* [ | dbquery "dbName" "SELECT empName FROM tblName where mail='joe.smith@gmail.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.

Tags (4)
0 Karma
Highlighted

Re: Search index using search values from database

Champion

It may be wrong because it is not good English.
Or that the field name that becomes lowercase problem?

SQL result)
empName->empname

May be renamed in the sub-search if so
[(your subsearch)|renmae empname as empName]

Highlighted

Re: Search index using search values from database

Engager

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.

0 Karma
Highlighted

Re: Search index using search values from database

Champion

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]

0 Karma
Highlighted

Re: Search index using search values from database

Engager

Changing the As to "query" fixed it. Everything working as needed now.

0 Karma
Highlighted

Re: Search index using search values from database

Splunk Employee
Splunk Employee

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.

0 Karma
Highlighted

Re: Search index using search values from database

Engager

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?

0 Karma