topic Re: DB-Connect 3 - dbxlookup with query does not work in All Apps and Add-ons

DB-Connect 3 - dbxlookup with query does not work

Dohrendorf_DB — Wed, 30 Sep 2020 01:41:39 GMT

Hi fellow Splunkers,
I am trying to use DB-Connect to enrich search results by doing a dbxlookup against a table.

This is my command:

| makeresults
| eval value_i_know_to_be_in_database="218024571471"
| dbxlookup query="SELECT * FROM my_table" connection="my_connection" DATABASE_FIELDNAME as value_i_know_to_be_in_database OUTPUT CONTENT as dbx_content

For all I know this is exactly in line with the documentation. To verify that i did not goof the query i also ran:

| dbxquery query="SELECT * FROM my_table" connection="my_connection" 
| outputlookup dbxtest.csv

This returns results and looks right.
I added it to my previos search like this:

| makeresults
| eval value_i_know_to_be_in_database="218024571471"
| dbxlookup query="SELECT * FROM my_table" connection="my_connection" DATABASE_FIELDNAME as value_i_know_to_be_in_database OUTPUT CONTENT as dbx_content
| lookup dbxtest.csv DATABASE_FIELDNAME as value_i_know_to_be_in_database OUTPUT CONTENT as lookup_content

Now I get the expected value in the lookup_content field and still nothing in the dbx_content field.

I have been at this problem for multiple hours now and can't seem to get anywhere. I also tried to use a predefined lookup (which in actual production I can not use because my query needs to be dynamic). Did not work either.

At this point I don`t know what else to try. I have read the docs forward and back, but I can not get anything.

Help me answers.splunk. You are my only hope.

Re: DB-Connect 3 - dbxlookup with query does not work

manunairadavakk — Tue, 03 Sep 2019 11:07:27 GMT

@Dohrendorf_DB

Did you get any help or were able to find any solution?
I am also facing the same issue

Re: DB-Connect 3 - dbxlookup with query does not work

Dohrendorf_DB — Tue, 03 Sep 2019 11:09:11 GMT

Sadly no. There was no response to this question in any shape or form so far.

For now I solved my problem by running a subsearch with a join, which seems very wrong, but i couldn't solve it any other way.

Please post in here if you find anything.

Re: DB-Connect 3 - dbxlookup with query does not work

drodman29 — Tue, 19 Oct 2021 17:56:28 GMT

Still broken.

Solved my issue by running dbxquery output to a csv lookup, then using the normal lookups.

Re: DB-Connect 3 - dbxlookup with query does not work

louismai — Fri, 14 Jan 2022 12:26:20 GMT

It seems to be the only one solution for now. I have to use your solution because the bug is still there until now.

Re: DB-Connect 3 - dbxlookup with query does not work

SinghK — Fri, 14 Jan 2022 14:14:27 GMT

When you run the query from UI donyou get expected results??

Re: DB-Connect 3 - dbxlookup with query does not work

louismai — Sun, 16 Jan 2022 07:54:04 GMT

Hi @SinghK ,

I go to DataLab > Lookups > New Lookups,

Step 1: Set Reference Search with time range : results as expected

Step 2: Set Lookup SQL: results as expected

Step 3: Field Mapping:

- Search Fields Match Table Columns (case sensitive)

- Lookup Fields: Table columns chosen

Preview Results:

(...) | dbxlookup connection="Pro_DB" query="SELECT TOP(1000) [SID] ,
        [LogTime] ,
        [DocID]
FROM [Storage].[dbo].[DocStates]
ORDER BY [LogTime] DESC" "SID" AS "SID" OUTPUT "LogTime" AS "Log_Time", "DocID" AS "Doc_ID"

Then I clicked on "Open In Search", only the SPL query returns result, but the dbxlookup fields are all blank.

Tks

Louis