Multiple Microsoft SQL Instances with multiple databases - Using director

Hi @moreamazingnick

Icingaweb2 Version 2.10.1
Director 1.9.1

If I call it in my browser I get the same errors on icinagaweb2.
I’m not sure if the host and service I created is correct.

servicename or hostname is incorrect

Hi @moreamazingnick

Thank you, ill try removing the how and the service and recreate them.
Ill send you feedback later.

Thank you for your help. appreciated!!!

Hi @moreamazingnick

I created a new service called testservice updated the scrip and it is working now:

StatusCode        : 200
StatusDescription : OK
Content           : {
                        "host": "panther.company.co.za",
                        "imports": [
                            "SQLCustomCheck"
                        ],
                        "object_name": "testservice",
                        "object_type": "object"
                    }
                    
RawContent        : HTTP/1.1 200 OK
                    Content-Length: 157
                    Content-Type: application/json
                    Date: Tue, 14 Jun 2022 16:12:10 GMT
                    Server: Apache/2.4.41 (Ubuntu)
                    
                    {
                        "host": "panther.compnay.co.za",
                        "imports": ...
Forms             : {}
Headers           : {[Content-Length, 157], [Content-Type, application/json], [Date, Tue, 14 Jun 2022 16:12:10 GMT], [Server, 
                    Apache/2.4.41 (Ubuntu)]}
Images            : {}
InputFields       : {}
Links             : {}
ParsedHtml        : mshtml.HTMLDocumentClass
RawContentLength  : 157

Hi @moreamazingnick

Here is the reply for the correct service:

StatusCode        : 200
StatusDescription : OK
Content           : {
                        "host": "panther.company.co.za",
                        "imports": [
                            "SQLCustomCheck"
                        ],
                        "object_name": "SQLCustomCheck",
                        "object_type": "object",
                        "vars": {
                            "ps_command": "& 'C...
RawContent        : HTTP/1.1 200 OK
                    Content-Length: 338
                    Content-Type: application/json
                    Date: Tue, 14 Jun 2022 16:53:29 GMT
                    Server: Apache/2.4.41 (Ubuntu)
                    
                    {
                        "host": "panther.company.co.za",
                        "imports": ...
Forms             : {}
Headers           : {[Content-Length, 338], [Content-Type, application/json], [Date, Tue, 14 Jun 2022 16:53:29 GMT], [Server, Apache/2.4.41 (Ubuntu)]}
Images            : {}
InputFields       : {}
Links             : {}
ParsedHtml        : mshtml.HTMLDocumentClass
RawContentLength  : 338

how would I go about doing the POST:
here is what I need to change for the post to create each service:

Content           : {
                        "host": "panther.company.co.za",       I should be able to set this to new host
                        "imports": [
                            "SQLCustomCheck"                       Remains as is
                        ],
                        "object_name": "SQLCustomCheck", Should change for each iteration 
                        "object_type": "object",
                        "vars": {
                            "ps_command": "& 'C...     This line should change for each instance / DB the GET cut this off but the line should read as below...
  "ps_command": "& 'C:\\Program Files\\ICINGA2\\sbin\\customprpsqlcheck.ps1' -Instance panther\development -Database DEV1 -Test buzzsessions

In the above line -instance -Database -Test values should change 

what would a sample POST look like, I’m not sure where to begin or what the POST should look like.
could you provide a sample? of a post to director?
Thank you

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/invoke-webrequest?view=powershell-7.2#example-2-use-a-stateful-web-service

try something out and if it works post it here, if it doesn’t work post it here :slight_smile:

Hi @moreamazingnick

I tried to get 1 service created via the API post as suggested. It returns 200 but nothing happens in director. Never worked with API post before not sure what is wrong with the post. but its definitely not working. Here is the script.


$securepassword = ConvertTo-SecureString "P@ssw*****" -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"; 

$Test = "sessions"
$Instance = "panther\development"
[string[]] $Database=@("DEV1")


foreach($D in $Database){
$Body = {
host:  "panther.company.co.za"
imports: "SQLCustomCheck"
object_name: "SQLCustomCheckTEST"
object_type: "object"
vars: "ps_command: & 'C:\\Program Files\\ICINGA2\\sbin\\customprpsqlcheck.ps1' -Instance $instance -Database $d -Test $Test"
}

try
{
    $Response = Invoke-WebRequest -Uri "https://monitordir.company.co.za/icingaweb2/director/service?host=panther.company.co.za" -Credential $credentials -Method Post -Body $Body
    # This will only execute if the Invoke-WebRequest is successful.
    $StatusCode = $Response.StatusCode
}
catch
{
    $StatusCode = $_.Exception.Response.StatusCode.value__
}
$StatusCode
}


imports should be an array.
vars should be a hashable.

try to print the response content. this helps a lot debugging your script

Hi @moreamazingnick

What does this mean… vars should be hashable? can you show example how to make it hashable

Hi @moreamazingnick

Thank you for the help, unfortunately this system is just way to difficult and not user friendly to monitor multiple instances and databases on MS SQL. I have multiple data centers to monitor and don’t have days to get just the monitoring system going. Ill go try other solutions.

Thank you for our time.

sorry typo, hashtable

I will be happy to help you further on write your own script.
If you want code snippets please use google or the forum search.
I fell that you did not try enough, you want something that is ready to go, but I’m sorry that’s nothing I will provide for free if there is not enough effort provided from your side.

Programming is about debugging an finding a solution

Anyhow, happy to help and have a nice day

Hi @moreamazingnick

Managed to fix the issue it is working now. Thank you for the assistance., The checks are now showing after POST via API in director ready for deployment.

Regards
Francois