All Apps and Add-ons
Highlighted

How to use lookup table in DB Query?

Path Finder

I have a list of 140,000 server names that I need to pull information on.

I have a DB query that allows me to do this, but I'm struggling with being able to search for all 140,000 server names. The search goes :

| db query "SELECT D.node as 'Server' ......

FROM detail_eposccm AS D left outer join remedyimport AS R ON D.node = R.NODE

Where d.node='abcd' OR d.node='qrst' OR d.node='iuty'"

When I had a smaller list I would just manually list out the server names using OR however that won't work for this list of 100,000 plus. I've created a csv file with the ServerName as column 1 and full d.node path (d.node='servername') in column 2, and have saved it as a lookup table inside the search app. However I can't get the query to pick up the input table for some reason.

any advice would be greatly appreciated!

0 Karma
Highlighted

Re: How to use lookup table in DB Query?

Builder

Can you provide an example of how you are trying to access the lookup table?

0 Karma
Highlighted

Re: How to use lookup table in DB Query?

Path Finder

I'm not sure how to access it.

I tried using inputlookup after WHERE but it won't let me include the subsearch on the dbquery. Were you asking for something else?

0 Karma
Highlighted

Re: How to use lookup table in DB Query?

Builder

You can't include Splunk search language inside an SQL query. I have used SQL queries many times followed by Splunk search language statements, including lookups, but never an input lookup. Normally you use inputlookup in order to drive the sub searches.

0 Karma
Highlighted

Re: How to use lookup table in DB Query?

Builder

What data is in the lookup that is not in the database table? If, for example, the database returns node and the lookup contains the server name you might do something like this

| db query "SELECT node as 'Server' ......"
| lookup masterserverlist.csv node OUTPUT ServerName
| table node, servername

Where you are looking for the node and outputting the servername field. You can output multiple fields and even search on multiple fields.
,

0 Karma
Highlighted

Re: How to use lookup table in DB Query?

Path Finder

I see where you're going with that.

I should clarify- the database table has records for over 200,000 servers. I'd like to only view the records for the servers that are on the list that I have as a .csv - which is why I tried using the "WHERE" clause. So it's not that the lookup has any information to add, I just want to use that as the search parameter if thats possible. Is there any way to do this with a macro?

0 Karma