How to run sql custom query in incinga2?

I am new to incinga2 framework,how to run sql raw or custom query in incinga2 like below

SELECT 
   `user`,
   COUNT(0) as Total_Count,
   MAX(range_count) Max_Range_Count
FROM (
   SELECT
       a.`user`, 
       a.change_time, 
       COUNT(0) range_count
   FROM audit_log a
   INNER JOIN audit_log b ON a.`user` = b.`user` 
   WHERE b.change_time BETWEEN a.change_time AND a.change_time + INTERVAL 30 MINUTE
   GROUP BY a.`user`, a.change_time
) AS user_range_count
GROUP BY `user`

This database object in icinga2 for connecting database and fetch data. They use adapter but pdo is empty. Can A add here my pdo? Is it right with icniga2 framework?

ipl\Sql\Connection Object
(
    [config:protected] => ipl\Sql\Config Object
        (
            [db] => mysql
            [host] => localhost
            [port] => 3306
            [dbname] => auditlog
            [username] => root
            [password] => 
            [charset] => utf8
            [options] => 
            [type] => db
            [persistent] => 0
        )

    [pdo:protected] => 
    [queryBuilder:protected] => 
    [adapter:protected] => ipl\Sql\Adapter\Mysql Object
        (
            [quoteCharacter:protected] => Array
                (
                    [0] => `
                    [1] => `
                )

            [escapeCharatcer:protected] => ``
            [escapeCharacter:protected] => \"
            [options:protected] => Array
                (
                    [8] => 0
                    [20] => 
                    [3] => 2
                    [11] => 0
                    [17] => 
                )

        )

    [pluginLoaders:protected] => Array
        (
            [adapter] => Array
                (
                    [0] => ipl\Stdlib\Loader\AutoLoadingPluginLoader Object
                        (
                            [namespace:protected] => ipl\Sql\Adapter
                            [postfix:protected] => 
                            [uppercaseFirst:protected] => 1
                        )

                )

        )

)

1 answer

  • answered 2019-09-12 05:36 Jitendra Yadav

    You can use PDO.

    Very Nice article to get started with PDO.

    1. How to connect to MySQL using PDO
    2. Select query with PDO
    $strQuery = "SELECT 
       `user`,
       COUNT(0) as Total_Count,
       MAX(range_count) Max_Range_Count
    FROM (
       SELECT
           a.`user`, 
           a.change_time, 
           COUNT(0) range_count
       FROM audit_log a
       INNER JOIN audit_log b ON a.`user` = b.`user` 
       WHERE b.change_time BETWEEN a.change_time AND a.change_time + INTERVAL 30 MINUTE
       GROUP BY a.`user`, a.change_time
    ) AS user_range_count
    GROUP BY `user`";
    
    foreach ($conn->query($strQuery) as $row) {
        print_r($row);
    }