How to run sql custom query in incinga2?

Question

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
                        )

                )

        )

)

You can't because icinga2 doesn't provide a mysql interface. You would rather do this using the mysql shell, phpmyadmin or php libraries like pdo. — digijay, Sep 12 '19 at 05:04

score 0 · Answer 1 · answered Sep 12 '19 at 05:36

0

You can use PDO.

Very Nice article to get started 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);
}

answered Sep 12 '19 at 05:36

Dark Knight

6,116
1
15
37

i cant run this in incing2 i have to do this with php [https://stackoverflow.com/questions/57825536/max-number-of-consecutive-actions-that-the-user-performed-within-30-minutes-of-t] – savtri sharma Sep 12 '19 at 05:51
PDO stands for PHP Data Objects. So this code is PHP only. Use this in PHP. See the first reference and see how to connect to mysql, after that this code will return you the required output. – Dark Knight Sep 12 '19 at 05:54
i have updated my question,this is icinga2 is use for datatbase – savtri sharma Sep 12 '19 at 06:02
Will check on that. – Dark Knight Sep 12 '19 at 06:21
icinga2 seems to be like MySQL monitoring system, not the actual wrapper for data fetching. Still I saw some examples that allows you to run some queries but mostly all are of status check. I don't know about Incinga but still let me know? Are you able to run a simple query like `select * from some_table`? – Dark Knight Sep 12 '19 at 15:49
[How to monitor your MySQL Server](https://icinga.com/2017/06/12/how-to-monitor-your-mysql-servers-with-icinga-2/) – Dark Knight Sep 12 '19 at 15:53
@savtrisharma, did you check on this? – Dark Knight Sep 13 '19 at 19:57
no sorry did not check before that i checked its documentation of ipl module so there was function to fetch data from sql – savtri sharma Sep 15 '19 at 15:51

score 0 · Answer 2 · answered Sep 13 '19 at 06:22

First Install any database resource module in incinga like Icinga Web 2 - IPL it will help us to call pdo queries in incinga then you can check its documentation how to run any statement with binding parameter. example:

use ipl\Sql\Select;
use PDO;
$stmt = 'Select * from table where id=?';
$id=1;
$execute=$this->dbConnection->fetchAll($stmt,[$id]);

How to run sql custom query in incinga2?

2 Answers2