How to pass in a variable to dbxquery

[a212830]

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.

[niketn · Accepted solution]

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

[a212830]

Thanks! Works like a charm.

[sloshburch]

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.