All Apps and Add-ons

DB-Connect 3 - dbxlookup with query does not work

Dohrendorf_DB
Engager

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.

Tags (1)
0 Karma

SinghK
Builder

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

0 Karma

louismai
Path Finder

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

 

0 Karma

manunairadavakk
Path Finder

@Dohrendorf_DB

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

0 Karma

Dohrendorf_DB
Engager

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.

0 Karma

drodman29
Path Finder

Still broken. 

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

louismai
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Splunk Smartness with Brandon Sternfield | Episode 3

Hello and welcome to another episode of "Splunk Smartness," the interview series where we explore the power of ...

Monitoring Postgres with OpenTelemetry

Behind every business-critical application, you’ll find databases. These behind-the-scenes stores power ...

Mastering Synthetic Browser Testing: Pro Tips to Keep Your Web App Running Smoothly

To start, if you're new to synthetic monitoring, I recommend exploring this synthetic monitoring overview. In ...