Splunk DB connect DBX Query error

[anitaroseline]

Dear all ,

I have splunk db connect and using many input connections successfully.One specific connection throws this error

Error in 'dbxquery' command: External search command exited unexpectedly with non-zero error code 1.

/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [NUMBER]
,[OPEN]
,[CATEGORY]
,[SUBCATEGORY]
,[MODEL]
,[CURRENT_PHASE]
,[IMPACT]
,[STATUS]
,[PRIORITY]
,[APPROVAL_STATUS]
,[ALERT]
,[ALERT_NAMES]
,[PENDING_GROUPS]
,[REASON]
,[SUBMIT_DATE]
,[UPDATE_DATE]
,[CLOSE_DATE]
,[CANCELLED_DATE]
,[REQUESTOR_NAME]
,[COORDINATOR_NAME]
,[COORDINATOR_DEPT]
,[ASSIGNED_TO]
,[SHIP_TO_CODE]
,[BILL_TO_CODE]
,[TOTAL_COST]
,[BILL_TO_EXT]
,[SHIP_TO_EXT]
,[PROJECT_ID]
,[PLANNED_START]
,[PLANNED_END]
,[REQUESTED_FOR]
,[BRIEF_DESCRIPTION]
,[FUTURE_GROUPS]
,[APPROVED_GROUPS]
,[BILL_TO_DEPT]
,[COMPANY]
,[ALERT_STATUS]
,[SVC_OPTIONS]
,[FOLDER]
,[SLA_BREACH]
,[NEXT_BREACH]
,[SVCCARTID]
,[AGREEMENT_IDS]
,[ASSIGNED_GROUP]
,[UPDATE_ACTION]
,[CUST_VISIBLE]
,[CLOSURE_CODE]
,[CLOSURE_COMMENTS]
,[DELIVERY_DATE]
,[COST_CURRENCY_CODE]
,[CLOSED_BY]
,[DESCRIPTION]
,[GLOBAL_LEAD_TIME]
,[REQUESTED_DATE]
,[MODELNAME]
,[SYSMODTIME]
,[SYSMODUSER]
,[SYSMODCOUNT]
,[SEVERITY]
,[OPENED_BY]
,[AFFECTED_ITEM]
,[LOGICAL_NAME]
,[ESCALATED]
,[OWNER]
,[LABOR]
,[FOREIGN_ID]
,[OTRSREFERENCENUMBER]
,[LASTASSIGNMENTGROUP]
,[REFERENCE_ID]
,[TICKET_TYPE]
,[OTRSINTERFACE]
,[ATTACHDATA]
,[ATTACHFILENAME]
,[LANGUAGE]
,[FULFILMENT_DATEOLY]
,[REQUEST_SUBSTATUS]
,[ATTACHMENTLOCATION]
,[OTRSFILENAME]
,[OTRSFILENAMES]
,[INCIDENT_ID]
,[REQVIPUSER]
,[EUCDEVICETYPE]
,[OSTEREFERENCENUMBER]
,[UPDATEACTION]
,[KPF_ID]
,[BACKTOFULFILLDATE]
,[BACKTOFULFILL]
,[OLY_TTR]
,[OLY_IO_OFFICER]
,[OLY_SALES_OFFICER]
,[OLY_COUNTRY]
,[OLY_ORIGIN]
,[OLY_FIELD_TECHNICIAN]
,[OLY_FIELD_TECHNICIAN1]
,[OLY_BACKTOFULFILL_LIST]
FROM [servicemanager].[dbo].[REQUESTM1] where SYSMODTIME > ? ORDER BY SYSMODTIME ASC

[rapmancz]

I have the same issue, but for all connections, "Error in 'dbxquery' command: External search command exited unexpectedly with non-zero error code 1." Splunk is indexing already existing database inputs (from MySQL), I am not able to add any new input because of this failure.  I could not find any records in log files so I do not have a clue what I can change to fix it....

[hpbrand]

Hi, had the same problem.  After weeks of troubleshooting I found following entry under default/commands.conf

####### uncomment following lines to revert dbxquery to 3.2.0 version
# [dbxquery]
# run_in_preview = false
# filename = java.path
# chunked = true
# command.arg.1 = -Dlogback.configurationFile=../config/command_logback.xml
# command.arg.2 = -DDBX_COMMAND_LOG_LEVEL=INFO
# command.arg.3 = -cp
# command.arg.4 = ../jars/dbxquery.jar
# command.arg.5 = com.splunk.dbx.command.DbxQueryCommand

Gave it  a try and copied all over to local/commands.conf and it works.

[reswob4]

Have you tried that query in the SQL Explorer tab on your DB Connect? I found that when I was having problems, running the query there helped me troubleshoot.

[anitaroseline]

Yes i did and same error persists

[reswob4]

I'm working with Splunk Support on a similar issue. One suggestion they made to help troubleshoot is to run the query from the Search window.

Here's a copy of the instructions they sent me:

| dbxquery query="LONG_QUERY" connection="YOUR_CONNECTION_NAME" timeout=6000

The easiest way to do this is to hit the “Open In Search” button on the SQL Explorer screen after you have written out the full query (the button is to the upper right corner). When the query opens on the next page just add timeout=6000 to the search as shown above.

As you probably can guess, this will enable you to test different portions of your query quickly. I'm using it to try and narrow down which part of my query is giving me trouble.

You can add or subtract or remove the timeout part......

[richgalloway]

Have you checked the dbx logs? Do the logs on the DB side shed any light on the problem?

[anitaroseline]

The same query works well while i run in sql studio

[joebisesi]

You said "one specific connection". Can you run other queries against that 'connection'?

[anitaroseline]

yes it works

[anitaroseline]

No such errors found.