3
responses

Hi. How can I write SQL statement to the variable.
For example:
define variable $sql_statement = SELECT some_func()
and in module om_dbi use this variable like
Module om_dbi
SQL $sql_statement
Driver pgsql

AskedNovember 6, 2019 - 1:41pm

Answer (1)

Bohdan,

You need to use im_dbi instead of om_dbi. Result of query cannot be save to a variable, but you can use output modules to write it to a file.

<Input dbi>
Module im_dbi
Driver mysql
Option host 127.0.0.1
Option username mysql
Option password mysql
Option dbname logdb
SQL SELECT id, facility, severity, hostname, \
timestamp, application, message \
FROM log
</Input>
<Output file>
Module om_file
File "tmp/output"
</Output>
<Route dbi_to_file>
Path dbi => file
</Route>

Comments (2)

  • Bohdan Lisovskyi's picture

    My question wasn't clear enough.
    I do not want to execute SQL to variable.
    I just want to init variable with SQL and then use this variable in SQL options in om_dbi or im_dbi.
    For example:
    I have the procedure:
    <Procedure>
    Module om_null
    <Exec>
    $some_sql = <SQL statemant 1>
    if true {
    $some_sql = <SQL statemant 2>
    }
    else {
    $some_sql = <SQL statemant 3>
    }
    </Exec>

    Then in some im_dbi or om_dbi module use the variable $some_sql

    <Input>
    Module im_dbi
    SQL $some_sql <---------HERE
    </Input>

  • manuel.munoz's picture
    (NXLog)

    Bohdan,

    Apologies, I was wrong.

    You can save queries in a variable, to be passed to SQL command.

    define QUERY SQL SELECT id, facility, severity, hostname, timestamp, application, message FROM log  
    <Input dbi>
    ...
    SQL %QUERY%
    ...
    </Input>
    

    Also you can find (and modify) result of query in $raw_event variable.