All Apps and Add-ons
Highlighted

How to pass in a variable to dbxquery

Champion

Hi,

I need to run a query, take the output, and pass it to a dbxquery search. The base command is: index=main sourcetype=ampData_source
| table BATCHSEQUENCE
| dedup BATCHSEQUENCE
| sort - BATCHSEQUENCE
| head 1

This will result in a number being returned. I then must pass this number to the following: dbxquery query="SELECT analyticsutil.closeBatchFunction(XXXX,'Y') from dual;" connection="ERPN'

Any help is appreciated.

0 Karma
Highlighted

Re: How to pass in a variable to dbxquery

Legend

If this is in search you can try the map command:

Try something like the following:

index=main sourcetype=ampData_source 
| fields BATCHSEQUENCE 
| dedup BATCHSEQUENCE 
| sort 0 - BATCHSEQUENCE 
| head 1
| table BATCHSEQUENCE
| map search="| dbxquery query=\"SELECT analyticsutil.closeBatchFunction($BATCHSEQUENCE$,'Y') from dual;\" connection=\"ERPN\"" maxsearches=1

PS: Double quotes " within map command search query are escaped using backslash \.

View solution in original post

Highlighted

Re: How to pass in a variable to dbxquery

Champion

Thanks! Works like a charm.

0 Karma
Highlighted

Re: How to pass in a variable to dbxquery

Ultra Champion

Some search improvements that might speed this up.

index=main sourcetype=ampData_source 
| stats count BY BATCHSEQUENCE 
| fields BATCHSEQUENCE 
| sort 1 - BATCHSEQUENCE 
| map search="| dbxquery query=\"SELECT analyticsutil.closeBatchFunction($BATCHSEQUENCE$,'Y') from dual;\" connection=\"ERPN\"" maxsearches=1

The stats should be faster than a dedup. The sort command will return a single result using the numeric param.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.