DBXlookup function in report causing random fields to be blank?

[BradOH]

We have a simple report which collates several lookups into a single lookup each night to support our dashboards. We recently added a dbxlookup command to add their user_id from one system, but for whatever strange reason, this causes other source fields to be vanish from the final report. See below...

| inputlookup region1_employees.csv | append [ inputlookup region2_employees.csv ] | append [ inputlookup region3_employees.csv ] 
... do a bunch of stuff...(ldapfilters, etc..)
... add user_id from database...
| dbxlookup connection="DATABASE" query="SELECT User_Id,User_Login FROM db.Users" "User_Login" AS "User_Login" OUTPUT "User_Id" AS "User_Id"
| table Email_Address Name Position Site User_Id User_Login User_First_Name User_Last_Name userAccountControl

If the dbxlookup is NOT included in the report, all fields are returned (except User_Id of course).  When the dbxlookup function is included, some fields are blank in the table (e.g. Position and Site have no values in this example).  What is going on?!?!

[masonreed11]

This usually points to a field collision or the lookup replacing values unexpectedly. Verify that User_Login is unique in the database and that no returned fields conflict with existing ones. Running | fieldsummary or comparing the output immediately before and after dbxlookup should reveal where those fields are getting lost. If everything looks correct, it may be worth checking your DB Connect version, as there have been issues with dbxlookup behavior in some releases.

[BradOH]

Hey, quick update on this. Splunk support this appears to be an issue with dbconnect. Here's their response:

When the input dataset exceeds an internal processing threshold, dbxlookup divides the events into multiple batches (chunks) and processes them separately before merging the results back together. In certain versions of DB Connect running on Windows, the merge operation may not correctly preserve fields that are neither part of the lookup key nor explicitly returned as output columns.

Additionally, the quadratic increase in result size that you observed may be another symptom of the same chunk-processing behavior, where intermediate results are appended instead of replaced.

For the moment, we've split the report using an intermediate search using a dbxquery to dump the necessary fields to a CSV lookup, then use that to replace the dbxlookups in the main search. 

I've asked if this issue will be addressed in a future release of the dbconnect add-on.  Guess we'll see...

In any case, thanks for your assistance on this one.

[tscroggins]

Thanks for the update! Hopefully, they'll get both issues fixed up soon!

EDIT: On a side note, I've since found SQLite and DuckDB and their :memory: databases to be good, simple test harnesses for DBX functionality. DuckDB could also be a fun alternative for CSV-based lookups with a SQL overlay, although safety would be a concern. You'd need to carefully coordinate outputlookup, outputcsv, or external update mechanisms.

[tscroggins]

Hi @BradOH,

Which Splunk DB Connect version and JBDC driver and version are you using? Does the problem only occur when append subsearches are present? Does the problem occur when using makresults, e.g.:

| makeresults format=csv data="
User_Login
jdoe1
"
| dbxlookup connection="DATABASE" query="SELECT User_Id, User_Login FROM db.Users" User_Login output User_Id

On a side note, you can chain inputlookup commands directly without using append subsearches:

| inputlookup region1_employees.csv
| inputlookup append=t region2_employees.csv
| inputlookup append=t region3_employees.csv

[BradOH]

Further to this, I tried putting all the lookups together into a single lookup, then running a separate dbxlookup against that lookup, same results (blank columns in the data). It appears the issue is related to the number of results being feed into the dbxlookup causing fields to be dropped...

[BradOH]

Thanks for your quick response, as always.

We're recently updated to dbconnect 4.2.3 (it took months due to issues with the new version and Windows environments).  I see there's a new version but am hesitant to update at this point due to the issues with the previous version.

The lookup works fine just using makeresults or with smaller data sets.  For example, if I reduce it down to only two inputs, the report works fine.  The final report actually merges 12 lookups, if I reduce that to 3 or 4 lookups, works, once I add more, blank fields appear in the results when the dbxlookup is added. I did some testing to tease out if it is related to a specific number of results, but could find nothing definitive.  Tres strange...

P.S. I removed the appends and converted to chained lookups and the same issue occurs.

[tscroggins]

I haven't been able to reproduce the issue myself in Splunk Enterprise 10.4,  Splunk DB Connect 4.3.0, and OpenJDK 21 on RHEL 10. Performance should vary by JDBC driver and RDBMS, and I'm testing with the PostgreSQL instance that ships with Splunk.

You can increase logging verbosity in the UI or in $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/dbx_settings.conf. Try setting dbxquery and dbxlookup to DEBUG.

After running a "bad" search, check:

index=_internal source=*splunk_app_db_connect_commands.*

In my own experience, dbxlookup does not scale. For large lookups, you may prefer to periodically pull data into a CSV or KV store lookup through a scheduled search using dbxquery. From there, use the standard lookup command. Keep an eye on the max_memtable_bytes setting (CSV) and  kvstore limits, depending on your choice.

[BradOH]

Thanks, I tried switching to verbose and ran some tests. Unfortunately, nothing stood out in any of the db_connect logs, no errors, warnings or anything.  Looking at the raw returns from the dbxlookup, it appears the fields are all being returned correctly, so why they're being dropped by Splunk is confounding.

For the moment, we went ahead and build a separate report to create a user_id to user_login lookup table in Splunk from the database. The larger report works fine when the dbxlookup is replaced by lookup. Annoying we need to create a support table for such a simple report.

Are there any other logs we could look at which may identify what's causing these fields to be dropped by Splunk?  

[tscroggins]

With debug logging enabled, the dbxlookup command should generate output similar to this:

2026-07-03 16:55:29.952 Trace-Id=51e10057-f38f-4de7-8ef7-b149c1835677 [main] INFO  c.s.dbx.command.lookup.service.DbxLookupService - feature=lookup component=service action=do_lookup name=null status=success message=complete chunk for lookup, dataset_size=1000, batch_count=1

2026-07-03 16:55:29.954 Trace-Id=51e10057-f38f-4de7-8ef7-b149c1835677 [main] DEBUG c.s.d.c.lookup.processor.LookupRecordProcessor - feature=lookup component=processor action=process_chunk row_size=2

2026-07-03 16:55:29.954 Trace-Id=51e10057-f38f-4de7-8ef7-b149c1835677 [main] DEBUG c.s.d.c.lookup.processor.LookupRecordProcessor - feature=lookup component=processor action=process_chunk message=processing data, data={usesysid=10, usename=postgres_admin}

...

2026-07-03 16:55:29.995 Trace-Id=51e10057-f38f-4de7-8ef7-b149c1835677 [main] DEBUG com.splunk.dbx.command.DbxLookupCommand - feature=lookup component=command action=write_chunk  record={_time: 1783112129,usesysid: 10,foo: bar,usename: postgres_admin}

2026-07-03 16:55:29.995 Trace-Id=51e10057-f38f-4de7-8ef7-b149c1835677 [main] DEBUG com.splunk.dbx.command.DbxLookupCommand - feature=lookup component=command action=write_chunk  record={_time: 1783112129,usesysid: 3,foo: bar,usename: null}

...

2026-07-03 16:55:29.996 Trace-Id=51e10057-f38f-4de7-8ef7-b149c1835677 [main] INFO  com.splunk.dbx.command.DbxLookupCommand - feature=lookup component=command action=execute_lookup name=null status=success elapsed_time=778

from SPL like this:

| makeresults count=1000
| eval usesysid=random()%11, foo="bar"
| dbxlookup chunksize=1000 connection=postgresql_splunk query="SELECT usesysid AS usesysid, usename AS usename FROM pg_catalog.pg_user" usesysid as usesysid output usename as usename

If an error occurs when writing a chunk, you'll see a message like:

... Trace-Id=... [main] ERROR com.splunk.dbx.command.DbxLookupCommand - feature=lookup component=command action=write_chunk status=failed message=failed to write record to splunk, record={...}

The Java process doesn't flush log output until the search is complete, so you won't see the log entries in real-time.

In the action=write_chunk lines, you can see the field-value pairs associated with each event, including both lookup and non-lookup fields.

If your missing field values are not missing in the log output, they're being lost in Splunk's reading of stdout from the Java process.

If your field values are also missing in the log output, they're being lost somewhere in the Java process.

What do you see?

[tscroggins]

As an aside, my dbxlookup tests appear to be scaling quadratically or O(n^2).

Test results:

| makeresults format=csv data="
count,iteration,elapsed_time
100000,1,21.530
100000,2,23.001
100000,3,21.602
150000,1,46.030
150000,2,35.049
150000,3,50.500
200000,1,77.613
200000,2,79.297
200000,3,97.364
250000,1,159.102
250000,2,171.384
250000,3,154.734
300000,1,264.394
300000,2,275.097
300000,3,277.158
"
| chart values(elapsed_time) as elasped_time over count by iteration

Linear scale:

Log scale:

While I avoid dbxlookup in practice and prefer the cached lookup approach I recommended earlier, the results are disappointing. There's likely a nested loop somewhere in the dbxlookup code.

Since we're not using the same JDBC driver and RDBMS, it might be worth trying to duplicate your problem using Acolyte http://acolyte.eu.org/ or another JDBC test harness that doesn't interface with a real database.

[BradOH]

Interesting that you noticed this, I discovered same when looking at the search logs. I was looking at the rows returned from dbxlookup and left scratching my head.  The numbers went exponential very quickly.  Definitely something weird going on there.  I'm going to submit a ticket to get this logged and hopefully some attention to it.