Splunk Search

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

kavana
Explorer

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'

0 Karma

Elsurion
Communicator

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.

0 Karma

kavana
Explorer

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?

0 Karma

Elsurion
Communicator

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