All Apps and Add-ons

How to pass in a variable to dbxquery

a212830
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
1 Solution

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

a212830
Champion

Thanks! Works like a charm.

0 Karma

sloshburch
Splunk Employee
Splunk Employee

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...