Multiple Microsoft SQL Instances with multiple databases - Using director

Icinga Web Modules Icinga Director
1

Hi All

New to the community. I hope you can assist me.

How can I use director to monitor multiple Microsoft SQL instances on a single host.
We have multiple instances of SQL running on our data server, each one of those instances have multiple databases within those databases we have a stored procedures that runs multiple checks against the databases.

I created a simple check in director that uses a powershell script with parameters.
On director is a simple command with custom variable that points to the shellscript on the SQL server and passes a few arguments.
Something like this:
ps_command & ‘C:\Program Files\ICINGA2\sbin\customprpsqlcheck.ps1’ -Instance server\sqldevelopment -Database DEV1 -Test mydevicesessions

The challenge is that I don’t want to create checks for each SQL Instance, Database and Test … I’m going to end up with 500 individual “checks” like the sample command above, there are multiple instances with multiple databases containing multiple stored procedures with multiple “Tests”.

How can I get this one check I created to run over all instances and DB’s without having to create every check individually?

Here is an example I found on how to do it using conf files and mysql in Icinga2 “https://icinga.com/blog/2017/06/12/how-to-monitor-your-mysql-servers-with-icinga-2/”, but how do I go about this using Microsoft SQL and Director?

There must be a way in director to tell it to duplicate a specific check so that it creates a new check with new parameters i.e (-Instance server\sql2dev -Database DEV2 -Test mydeviceclocks) for each test in each database on every SQL instance.

Any assistance would be appreciated.

Regards
Francois

2

the problem I see here is that icinga doesn’t know which instances are available.

I think you have 2 possibility:

  1. adapt your ps1 file to iterate over all instances, and execute your check procedure for every instance via powershell.(one services)
    https://www.sqlshack.com/six-methods-to-automatically-discover-sql-server-instances/
  2. write a powershell script that interates over all instances and create the 500+ services in icinga director via icingadirector restapi call
3

Thank you @moreamazingnick for the reply.

option 1: This returns all the results in one service and is unmanageable, there is no way to separate the results by instance and database. I need to generate a separate service for each test in each instance in each database.

options 2: This might help me – write a powershell script that iterates over all instances and create the 500+ services in icinga director via icingadirector restapi call.

  • Where can I find information on how this works? Any examples available?

Thank you, your ongoing assistance is greatly appreciated.

4

  • start with creating on reference service in icinga director for reference host.
    Hint, do not use any spaces or special chars, or take care of proper encoding

  • perform a GET on this url with basic auth, use a icingaweb2 username/password that has director RW access or full access :
    “https://” + icinga_instance + ‘/icingaweb2/director/service?host=’ + reference_host + “&name=” + reference_service

  • look at the response, now you know what icinga director expects as a service object for your service.

  • change object_name and host and vars->your_instance_var
    you can json decode it manipulate it, and POST it to:
    “https://” + icinga_instance + ‘/icingaweb2/director/service?host=’ + other_host

  • if run this in a loop on a host with sql instances for each instance → you get services for all instances on this host

5

Hi @moreamazingnick

Please can you provide more detail for this line: * start with creating on reference service in icinga director for reference host. Hint, do not use any spaces or special chars, or take care of proper encoding

Should I create a Host and service in director and use it in the following api call:
"https://myserver.company.com/icingaweb2/director/service?host=thehosticreated&name=theservicecreated”

when I try to perform the GET I get error:
Invoke-WebRequest : The underlying connection was closed: An unexpected error occurred on a send.

6

yes sound good but post your webrequest script because the error is unspecific

7

Hi @moreamazingnick

Here is the script:

$securepassword = ConvertTo-SecureString “password********” -AsPlainText -Force
$credentials = New-Object System.Management.Automation.PSCredential(“icingaweb2”, $securepassword)

[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

Invoke-WebRequest -Uri “https://monitordir.company.co.za/icingaweb2/director/service?host=panther.company.co.za&name=SQLCustomCheck” -Credential $credentials

8 
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

that does not work on my system

try that instead

add-type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
    public class TrustAllCertsPolicy : ICertificatePolicy {
        public bool CheckValidationResult(
            ServicePoint srvPoint, X509Certificate certificate,
            WebRequest request, int certificateProblem) {
            return true;
        }
    }
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

Invoke-WebRequest -Uri "https://monitordir.company.co.za/icingaweb2/director/service?host=panther.company.co.za&name=SQLCustomCheck" -Credential $credentials -ContentType 'application/json' -Headers @{"accept"="application/json"}
9

Hi @moreamazingnick

When you run Invoke-WebRequest or Invoke-RestMethod command, sometimes you get the error “The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel .” because there could be a certificate issue or the required windows version doesn’t support the TLS or SSL version. You can use the command to bypass this error.

I still get the same error even if I remove line: [System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

the error is referencing the https:// line see below so something not working in the Https:// line
Invoke-WebRequest : The underlying connection was closed: An unexpected error occurred on a send.
At line:6 char:1

  • Invoke-WebRequest -Uri "https://monitordir.company.co.za/icingaw …
  •   + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
  + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand
10

nobody said anything about removing a line.

the script in my post works on my server.

try that:
[Net.ServicePointManager]::SecurityProtocol = "tls12, tls11";

11

HI @moreamazingnick

im using this scrypt:

$securepassword = ConvertTo-SecureString “*****ppassword” -AsPlainText -Force
$credentials = New-Object System.Management.Automation.PSCredential(“director”, $securepassword)
add-type @"
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
public bool CheckValidationResult(
ServicePoint srvPoint, X509Certificate certificate,
WebRequest request, int certificateProblem) {
return true;
}
}
"@
[Net.ServicePointManager]::SecurityProtocol = “tls12, tls11”;

Invoke-WebRequest -Uri “https://monitordir.company.co.za/icingaweb2/director/service?host=panther.company.co.za&name=SQLCustomCheck” -Credential $credentials -ContentType ‘application/json’ -Headers @{“accept”=“application/json”}

error i get now:

add-type : Cannot add type. The type name ‘TrustAllCertsPolicy’ already exists.
At line:3 char:1

  • add-type @"
  •   + CategoryInfo          : InvalidOperation: (TrustAllCertsPolicy:String) [Add-Type], Exception
  + FullyQualifiedErrorId : TYPE_ALREADY_EXISTS,Microsoft.PowerShell.Commands.AddTypeCommand

Invoke-WebRequest : The underlying connection was closed: An unexpected error occurred on a send.
At line:16 char:1

  • Invoke-WebRequest -Uri "https://monitordir.company.co.za/icingaw …
  •   + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
  + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand
12

I just executed that and it works like a charm:

add-type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
    public class TrustAllCertsPolicy : ICertificatePolicy {
        public bool CheckValidationResult(
            ServicePoint srvPoint, X509Certificate certificate,
            WebRequest request, int certificateProblem) {
            return true;
        }
    }
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
[Net.ServicePointManager]::SecurityProtocol = "tls12, tls11"; 
Invoke-WebRequest -Uri "https://monitordir.company.co.za/icingaweb2/director/service?host=panther.company.co.za&name=SQLCustomCheck" -Credential $credentials -ContentType 'application/json' -Headers @{"accept"="application/json"}
13

hi @moreamazingnick

Ok, I tried this again and now I get a different error:
Invoke-WebRequest : The remote server returned an error: (401) Unauthorized.

14

post you script and use the </> preformat tags

15

Hi @moreamazingnick

$securepassword = ConvertTo-SecureString "***password" -AsPlainText -Force
$credentials = New-Object System.Management.Automation.PSCredential("icingaweb2", $securepassword)
add-type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
    public class TrustAllCertsPolicy : ICertificatePolicy {
        public bool CheckValidationResult(
            ServicePoint srvPoint, X509Certificate certificate,
            WebRequest request, int certificateProblem) {
            return true;
        }
    }
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
[Net.ServicePointManager]::SecurityProtocol = "tls12, tls11"; 
Invoke-WebRequest -Uri "https://monitordir.company.co.za/icingaweb2/director/service?host=panther.company.co.za&name=SQLCustomCheck" -Credential $credentials -ContentType 'application/json' -Headers @{"accept"="application/json"}
16

now try to login in icingaweb2 with your browser using the credential in your script

17

Hi @moreamazingnick

It does not allow me to login using those credentials.
Those credentials are for the API

18

you want to connect to https://monitordir.company.co.za/icingaweb2

so you need to use icingaweb2 credentials.
the icingaweb2 user needs to have permission for the director

19

Hi @moreamazingnick

Ok I understand, I used my user and password for icingaweb2 in the script and it went pass the previous error but now it returns another error:

Invoke-WebRequest : {“status”:“error”,“message”:“Uncaught Error: Call to a member function toString() on null in
/usr/share/icingaweb2/modules/director/application/controllers/ServiceController.php:254\nStack trace:\n#0
/usr/share/icingaweb2/modules/director/library/Director/Web/Controller/ObjectController.php(497):
Icinga\Module\Director\Controllers\ServiceController->loadObject()\n#1
/usr/share/icingaweb2/modules/director/library/Director/Web/Controller/ObjectController.php(70):
Icinga\Module\Director\Web\Controller\ObjectController->loadOptionalObject()\n#2
/usr/share/icingaweb2/modules/director/application/controllers/ServiceController.php(46):
Icinga\Module\Director\Web\Controller\ObjectController->init()\n#3
/usr/share/php/Icinga/Web/Controller/ActionController.php(170):
Icinga\Module\Director\Controllers\ServiceController->init()\n#4 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(59):
Icinga\Web\Controller\ActionController->__construct()\n#5
/usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch()\n#6
/usr/share/php/Icinga/Application/Web.php(304): Zend_Controller_Front->dispatch()\n#7
/usr/share/php/Icinga/Application/webrouter.php(109): Icinga\Application\Web->dispatch()\n#8
/usr/share/icingaweb2/public/index.php(4): require_once(’…’)\n#9 {main}\n thrown”}
At C:\Users\francois.bruere\Documents\TEST.ps1:16 char:1

  • Invoke-WebRequest -Uri "https://monitordir.company.co.za/icingaweb2/director/serv …
  •   + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
  + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand
20

can you call “https://monitordir.company.co.za/icingaweb2/director/service?host=panther.company.co.za&name=SQLCustomCheck” with your browser?

  • What icingaweb2 version do you use?
  • What director version do you use?

Please use the formating tags!