topic Re: Using DB CONNECT to query data but the value of "where condition" is variable in Splunk Search

Using DB CONNECT to query data but the value of "where condition" is variable

kavana — Wed, 04 Apr 2018 07:31:04 GMT

We want to query data from DB Using DB CONNECT but the value of "where condition" is variable.

For example,the value of PID is variable as the sql below, we wanna pass the value of input box on the dashboard to sql below
which used by DB CONNECT

SELECT A,B,C FROM TABLE1 WHERE PID='1001002003'

Re: Using DB CONNECT to query data but the value of "where condition" is variable

Elsurion — Wed, 04 Apr 2018 08:20:44 GMT

You can use the SPL for the DBX for that Usecase:

| dbxquery query="select jobno, orderno, applgroup, nodegrp, failcount, order_time from cmr_ajf_jobs where endrun > '20180404000000'" connection="testsystem"

When you feeding the SPL via Dashboard then you can use the $value$ substitution in the search.

Re: Using DB CONNECT to query data but the value of "where condition" is variable

kavana — Wed, 04 Apr 2018 09:58:00 GMT

Thank you for your reply.

We've used SPL for the DBX before, but it was too slow so that we are looking for a new way.

So, it is no way for DB CONNECT to pass variable value to SQL, isn't it?

So, we have to use the SPL for the DBX for that usecase, right?

Re: Using DB CONNECT to query data but the value of "where condition" is variable

Elsurion — Wed, 04 Apr 2018 10:19:33 GMT

The only way you could do this, is to create a custom script, where you can handle your SQL. But you have to manage the SQL handling by yourself.
Normally i'm using a rising column for DB Input or i'm reading the whole table.

The Documentation is here:
http://docs.splunk.com/Documentation/Splunk/7.0.3/Search/Writeasearchcommand

Example:
https://docs.splunk.com/Documentation/Splunk/7.0.3/Search/Customsearchcommandshape

Here a stub i'm using, stored in etc/apps/<app>/bin:

#!/usr/bin/python

__doc__ = '''

    Ein Konverter fuer die base36 Konversion im Splunk Stream

'''

import sys, splunk.Intersplunk

#splunk_home = os.getenv('SPLUNK_HOME')
#if not splunk_home:
#    raise ConfigError('Environment variable SPLUNK_HOME must be set. Run: source ~/bin/setSplunkEnv')

# Start script
#***************************************************************
if __name__ == '__main__':

    #Get the arguments from search
    #(isgetinfo, sys.argv) = splunk.Intersplunk.isGetInfo(sys.argv)

    #if len(sys.argv) != 2:
    #        splunk.Intersplunk.parseError("Usage | ctmbase36 __EXECUTE__ <field>")

    #thefield=sys.argv[1]

    reader=splunk.Intersplunk.readResults(None,None,True)

    for row in reader:
        #print "Inputvalue"
        value=int(row['ORDERNO'])
        #print value
        # Here your SQL Function
        convert = base36encode(value)
        #print convert

        # Set a value    
        row['orderid'] = convert

    splunk.Intersplunk.outputResults(reader)

#
# EOF
#

To call this script then within the SQL you need a commands.conf

#
# Commands.conf for the base36 convert
#

[ctmbase36]
type = python
filename = ctm_base36.py
local = true
stderr_dest = message
supports_getinfo = false

#
# EOF
#

in the SPL the call would be like this then:

| eval ORDERNO=00a2doz
| ctmbase36