Look icingadb-web in Module Map

Icinga Web Modules Icinga DB Web
1

Hello,

I’m trying to modify the MAP module with the end result being a look similar to icingadb-web.

display_icingadbweb
display_icingadbweb200×121 3.94 KB

The advantage for users like me, with 8k hosts and 250K services, would be that far fewer lines need to be processed to render the view in the MAP module.
The SQL would only return one result per host, so I’d only need to process 8k lines to build the map.

What I can’t figure out is how to run the query against ICINGADB.
I should be able to create a query like this.

    $hostQuery = Host::on($this->icingadbUtils->getDb())
         ->columns([
                  'id',
                  'name',
                  'display_name',
                  'vars.geolocation',
                  'vars.map_icon',
                  'hosts_down_handled'          => new Expression('SUM(CASE WHEN state.soft_state = 1 AND (state.is_handled = \'y\' OR state.is_reachable = \'n\') THEN 1 ELSE 0 END)'),
                  'hosts_down_unhandled'        => new Expression('SUM(CASE WHEN state.soft_state = 1 AND state.is_handled = \'n\' AND state.is_reachable = \'y\' THEN 1 ELSE 0 END)'),
                  'hosts_pending'               => new Expression('SUM(CASE WHEN state.soft_state = 99 THEN 1 ELSE 0 END)'),
                  'hosts_total'                 => new Expression('SUM(CASE WHEN id IS NOT NULL THEN 1 ELSE 0 END)'),
                  'hosts_up'                    => new Expression('SUM(CASE WHEN state.soft_state = 0 THEN 1 ELSE 0 END)'),
                  'services_critical_handled'   => new Expression('SUM(CASE WHEN service.state.soft_state = 2 AND (service.state.is_handled = \'y\' OR service.state.is_reachable = \'n\') THEN 1 ELSE 0 END)'),
                  'services_critical_unhandled' => new Expression('SUM(CASE WHEN service.state.soft_state = 2 AND service.state.is_handled = \'n\' AND service.state.is_reachable = \'y\' THEN 1 ELSE 0 END)'),
                  'services_ok'                 => new Expression('SUM(CASE WHEN service.state.soft_state = 0 THEN 1 ELSE 0 END)'),
                  'services_pending'            => new Expression('SUM(CASE WHEN service.state.soft_state = 99 THEN 1 ELSE 0 END)'),
                  'services_total'              => new Expression('SUM(CASE WHEN service.id IS NOT NULL THEN 1 ELSE 0 END)'),
                  'services_unknown_handled'    => new Expression('SUM(CASE WHEN service.state.soft_state = 3 AND (service.state.is_handled = \'y\' OR service.state.is_reachable = \'n\') THEN 1 ELSE 0 END)'),
                  'services_unknown_unhandled'  => new Expression('SUM(CASE WHEN service.state.soft_state = 3 AND service.state.is_handled = \'n\' AND service.state.is_reachable = \'y\' THEN 1 ELSE 0 END)'),
                  'services_warning_handled'    => new Expression('SUM(CASE WHEN service.state.soft_state = 1 AND (service.state.is_handled = \'y\' OR service.state.is_reachable = \'n\') THEN 1 ELSE 0 END)'),
                  'services_warning_unhandled'  => new Expression('SUM(CASE WHEN service.state.soft_state = 1 AND service.state.is_handled = \'n\' AND service.state.is_reachable = \'y\' THEN 1 ELSE 0 END)')
        ])
        ->filter(IplFilter::like('host.vars.geolocation', '*'))
        ->setResultSetClass(VolatileStateResults::class);

It gives me an error on

sqlerror

My job isn’t as a PHP programmer, which is why I’m asking if anyone has a solution for this.
Or is the SQL simply not possible via the MAP module?

2

Hi GeBi,

I’m not a PHP programmer as well, but from database point of view the issue is, that you don’t tell the system on what table the select should run. You define which database to use and what columns you want to see, but there is no table name anywhere and this is exactly what the message tells you: missing “from” clause.

Sorry that I can’t provide the code to fix that, because your syntax is different to those used in other modules and I’m not fit in PHP…

Still hope that hint helps.
Regards,
Dirk

3

I dont know much about PHP as well, but looking at the Icinga PHP Library it looks like you are specifying your used database pretty much early on: ipl-sql/doc/01-Features.md at main · Icinga/ipl-sql · GitHub

4

Hi @gbin2265,

The joins are missing in your query. The correct format would be the following:

$hostQuery = Host::on($this->icingadbUtils->getDb())
    ->with(['host.state', 'service', 'service.state']) // this was missing
    ->columns([
        'host.id',
        'host.name',
        'host.display_name',
        'vars.geolocation',
        'vars.map_icon',
        'service.id',
        'hosts_down_handled'          => new Expression('SUM(CASE WHEN host_state.soft_state = 1 AND (host_state.is_handled = \'y\' OR host_state.is_reachable = \'n\') THEN 1 ELSE 0 END)'),
        'hosts_down_unhandled'        => new Expression('SUM(CASE WHEN host_state.soft_state = 1 AND host_state.is_handled = \'n\' AND host_state.is_reachable = \'y\' THEN 1 ELSE 0 END)'),
        'hosts_pending'               => new Expression('SUM(CASE WHEN host_state.soft_state = 99 THEN 1 ELSE 0 END)'),
        'hosts_total'                 => new Expression('SUM(CASE WHEN host.id IS NOT NULL THEN 1 ELSE 0 END)'),
        'hosts_up'                    => new Expression('SUM(CASE WHEN host_state.soft_state = 0 THEN 1 ELSE 0 END)'),
        'services_critical_handled'   => new Expression('SUM(CASE WHEN host_service_state.soft_state = 2 AND (host_service_state.is_handled = \'y\' OR host_service_state.is_reachable = \'n\') THEN 1 ELSE 0 END)'),
        'services_critical_unhandled' => new Expression('SUM(CASE WHEN host_service_state.soft_state = 2 AND host_service_state.is_handled = \'n\' AND host_service_state.is_reachable = \'y\' THEN 1 ELSE 0 END)'),
        'services_ok'                 => new Expression('SUM(CASE WHEN host_service_state.soft_state = 0 THEN 1 ELSE 0 END)'),
        'services_pending'            => new Expression('SUM(CASE WHEN host_service_state.soft_state = 99 THEN 1 ELSE 0 END)'),
        'services_total'              => new Expression('SUM(CASE WHEN service_id IS NOT NULL THEN 1 ELSE 0 END)'),
        'services_unknown_handled'    => new Expression('SUM(CASE WHEN host_service_state.soft_state = 3 AND (host_service_state.is_handled = \'y\' OR host_service_state.is_reachable = \'n\') THEN 1 ELSE 0 END)'),
        'services_unknown_unhandled'  => new Expression('SUM(CASE WHEN host_service_state.soft_state = 3 AND host_service_state.is_handled = \'n\' AND host_service_state.is_reachable = \'y\' THEN 1 ELSE 0 END)'),
        'services_warning_handled'    => new Expression('SUM(CASE WHEN host_service_state.soft_state = 1 AND (host_service_state.is_handled = \'y\' OR host_service_state.is_reachable = \'n\') THEN 1 ELSE 0 END)'),
        'services_warning_unhandled'  => new Expression('SUM(CASE WHEN host_service_state.soft_state = 1 AND host_service_state.is_handled = \'n\' AND host_service_state.is_reachable = \'y\' THEN 1 ELSE 0 END)'),
        'services_warning_unhandled2'  => new Expression('SUM(CASE WHEN host_service_state.soft_state = 1 AND host_service_state.is_handled = \'n\' AND host_service_state.is_reachable = \'y\' THEN 1 ELSE 0 END)'),
    ])
    ...

Note: The expression requires the base table prefix host_. This means that host.state.is_handled becomes host_state.is_handled and service.state.is_handled becomes host_sevice_state.is_handled.

OR

You can pass the columns using the string formatting supported by the expression class.

...
 'services_warning_unhandled'  => new Expression(
            'SUM(CASE WHEN %s = 1 AND %s = \'n\' AND %s = \'y\' THEN 1 ELSE 0 END)',
            [
                'host.service.state.soft_state',
                'host.service.state.is_handled',
                'host.service.state.is_reachable'
            ]
        ),
...
5

Hi @sdhillon,

Thank you!

This was a great help.

I create the query I wanted.
I also learned that by adding this,

    $hostQuery
       ->getSelectBase()
       ->groupBy(['host.id','host.name','host.display_name','host_vars_geolocation','host_vars_map_icon']);

you can perform the groupBy.

Now it’s a matter of figuring out how I can generate the map output based on my query result,
and try to give the detail screen the look and feel of icingadb-web.