Why is dbxquery with subsearch creating an extra space after query=?

[falkyre]

Hi all,

I've opened a case with support but am wondering if anyone has come across this issue. I just set up the latest Splunk (7.1.2) on RHEL 7 with Spunk DB Connect 3.1.3. I have an old setup (Splunk 6.2 with DB Connect 2.1.x) that I pulled a working dbxquery from and copied into my new system. When I run the query which contains a subsearch, the job inspector is showing that the |dbxquery connection=NCReporter query= contains a space. Here's my search:

| dbxquery connection=NCreporter query=[search index=nc_alarms sourcetype=nc_alarms NODE=* NETWORK=233 (ALERTGROUP=DOOR OR ALERTGROUP=DOOR.D OR ALERTKEY=*Mobility* OR LOCATION=*CELL*) NOT (ALERTGROUP=*A) | table SERVERSERIAL,SERVERNAME | format | eval filter=replace(search,"\"","'") | eval dbquery=("\"SELECT NODE,LOCATION,FIRSTOCCURRENCE,LASTOCCURRENCE,SUMMARY,CLEARTIME,TALLY,ALERTGROUP,ALERTKEY from REPORTER_STATUS where ".search+"\"")|return $dbquery] 

It returns a dbx error -1. Looking at the job inspector I see:

|dbxquery connection=NCreporter query= "SELECT NODE,LOCATION,FIRSTOCCURRENCE,LASTOCCURRENCE,SUMMARY,CLEARTIME,TALLY,ALERTGROUP,ALERTKEY from REPORTER_STATUS where ( ( SERVERNAME="WPG2NCOMS_P" AND SERVERSERIAL="1040188954" ) OR ( SERVERNAME="WPG2NCOMS_P" AND SERVERSERIAL="1040200242" ) )" | fields keepcolorder=t "*" "_bkt" "_cd" "_si" "host" "index" "linecount" "source" "sourcetype" "splunk_server" | remotetl nb=300 et=1532386071.000000 lt=1532386971.000000 remove=true max_count=1000 max_prefetch=100

There is a space being added by the dbxquery. If I take the dbxquery out of the mix and just do a normal subsearch, I get the proper string (but of course it does nothing for me as I need to send that query to the database with dbxquery).

Has anyone come across this?

Thanks,

Sean

[twhite_splunk]

Hey there! Very late response to this, but my team recently came across this issue, and we wanted to share our solution - we construct the query first, and then use the "map" command to call dbxquery. See below where I've converted your search to do this inverse:

search index=nc_alarms sourcetype=nc_alarms NODE=* NETWORK=233 (ALERTGROUP=DOOR OR ALERTGROUP=DOOR.D OR ALERTKEY=*Mobility* OR LOCATION=*CELL*) NOT (ALERTGROUP=*A)
| table SERVERSERIAL,SERVERNAME
| format
| eval filter=replace(search,"\"","'")
| eval dbquery=("\"SELECT NODE,LOCATION,FIRSTOCCURRENCE,LASTOCCURRENCE,SUMMARY,CLEARTIME,TALLY,ALERTGROUP,ALERTKEY from REPORTER_STATUS where ".search+"\"")
| fields dbquery
| map search="| dbxquery connection=NCreporter query=$dbquery$"

Note you might need to do some cleanup around the quotes and whatnot, but overall this should work for you!

[corey_dick]

There are at least a few things to change but without having your datasets I can't test exactly what you are trying to do. Try this to see if that helps or not:
| dbxquery connection=NCreporter query=[| search index=nc_alarms sourcetype=nc_alarms NODE=* NETWORK=233 (ALERTGROUP=DOOR OR ALERTGROUP=DOOR.D OR ALERTKEY=Mobility OR LOCATION=CELL) NOT (ALERTGROUP=*A) | table SERVERSERIAL,SERVERNAME | format | eval search=replace(search,"\"","'") | eval dbquery=("\"SELECT NODE,LOCATION,FIRSTOCCURRENCE,LASTOCCURRENCE,SUMMARY,CLEARTIME,TALLY,ALERTGROUP,ALERTKEY from REPORTER_STATUS where ".search."\"") | return $dbquery]