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?!?!

[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.

[livehybrid]

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful

• Marking it as the solution if it resolved your issue

• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing.