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

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




| eval message="Happy Splunking!!!"


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