How do I search for unknown columns using DB Connect?

[genesiusj]

Hello,

Need to perform a search using a list of values and I do not know the field names.

In Splunk, this works.

index=employees AND (123456789 OR SMITH OR "1 MAIN STREET")
| table *

In SQL I can do this. Note: These are not my table or field names. This is an example from the Internet.

DECLARE @ColName varchar(126) = 'col_name'
DECLARE @ColVal varchar(64) = 'col_val'
DECLARE @SQL AS nvarchar(MAX)
SET @SQL = ''
SELECT @SQL = @SQL + 'SELECT * FROM ' + IST.TABLE_SCHEMA + '.' + IST.TABLE_NAME +' where '+ @ColName +' = '''+ @ColVal +''' ' + CHAR(13)
FROM 
    INFORMATION_SCHEMA.TABLES IST
    INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC ON IST.TABLE_NAME = ISC.TABLE_NAME
WHERE
    ISC.COLUMN_NAME = @ColName
EXEC (@SQL)

 Is it possible to write the above for DBConnect?

Thanks and God bless,
Genesius

[richgalloway]

DB Connect just runs SQL queries so if you can write the SQL for it (within reason - I've had complex queries fail under DBX) then it should work.  The example SQL probably won't work, but something like this should:

SELECT * FROM table

You then can use `| table *` to display the columns without knowing their names.

[genesiusj]

Thanks, @richgalloway 

That is pretty much what I am doing when I am using Splunk against log data. The problem is I need to search through millions of database records. The number of db records needs to be limited to just those containing the values found before I table them. Otherwise, the dbxquery or dbxlookup commands will take 20, 30 or minutes to complete.

Thanks and God bless,
Genesius

[richgalloway]

That calls for a Rising Column.  A Rising Column is a database column with an ever-increasing, non-repeating value that Splunk can use to say "give me everything after *this*".  Of course, you must know the name of that column so you can specify it in the SQL where clause and so Splunk can keep track of it.  See https://docs.splunk.com/Documentation/DBX/3.10.0/DeployDBX/Createandmanagedatabaseinputs#Choose_inpu... for details.