All Apps and Add-ons

How to use lookup table in DB Query?

sadkha
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

kmattern
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

sadkha
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

kmattern
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

sadkha
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

kmattern
Builder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...