PowerShell – Alertes par email pour les Runbooks Orchestrator

Bonjour,

Voici un petit script dont la fonction est de rechercher les erreurs des runbooks Orchestrator, survenues les 10 dernières minutes, et générer un rapport HTML envoyé par email.

Pour le faire fonctionner dans votre environnement, il suffit de changer les valeurs des variables pour la connexion SMTP et SQL (le serveur hébergeant la DB utilisée par Orchestrator). Prêtez également attention à la valeur retournée par la variable $10minutesAgo, il faudra l’adapter en fonction de votre Time Zone, voir de l’heure d’été/hiver. La valeur retournée doit être, comme son nom l’indique, l’heure qu’il était il y 10 minutes.

Un rapport HTML par Runbook contenant des erreurs sera ainsi envoyé, ce rapport contiendra le résumé des erreurs survenues les 10 dernières minutes, avec le message d’erreur par activité.

A exécuter avec un compte ayant les permissions nécessaires sur le serveur SQL, et si nécessaire, modifier la commande Send-Message pour renseigner des credentials (ou faire tourner sur un serveur étant autorisé dans le serveur SMTP).

<#
    .SYNOPSIS
        This script will report every Runbook failure occured in the past 10 minutes
    .DESCRIPTION
        This script will query the Orchestrator database to gather information about every Runbook with failed activities in the past 10 minutes.
        For every failed Runbook, the script will generate an HTML repport then send it via email.
    .Notes
        Author : Antoine DELRUE - antoine@delrue.me
        Script name : SCO-Runbook_Failures_Reporter.ps1
#>

#region variables and functions 

# SMTP variables used to send the email repport
$SMTP_From = "no-reply@yourdomain.com"
$SMTP_Relay = "smtp.yourdomain.com"
$SMTP_To = "you@yourdomain.com"

# SQL varialbes and function to connec to the Orchestrator Database
$DB_Name = "Orchestrator"
$DB_Server = "dbserver.yourdomain.com"

Function Invoke-SQL {
    param(
        [string] $dataSource,
        [string] $database,
        [string] $sqlCommand = $(throw "Please specify a query.")
        )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables
}
# Exemple : Invoke-SQL -dataSource $DB_Server -database $DB_Name -sqlCommand "SELECT *"

#endregion

#Format the current date/time. Pay attention the times are 'GMT' on the Orchestrator database, that's why we substract 70 minutes to get last 10 minutes info and 130 minutes in DST (summer time)
# Yes, we have to change this value for Daylight saving time (Summer time)
$10minutesAgo = "'" + (Get-Date).AddMinutes(-130).ToString('yyyy-MM-dd HH:mm:ss') + "'"

# Get all Runbooks basic information (used to get Runbook path)
$AllRunbooks = Invoke-SQL -dataSource $DB_Server -database $DB_Name -sqlCommand "SELECT [id],[Path] from [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks]"

# Get Runbook Failures occured last 10 minutes

$All_Failures = (Invoke-SQL -dataSource $DB_Server -database $DB_Name -sqlCommand "
    Select i.UniqueID, i.policyid
    from POLICYINSTANCES i inner join POLICIES p on i.policyid = p.[UniqueID] 
    where i.Status <> 'success' and i.TimeEnded > $10minutesAgo Order By TimeEnded desc")

$Runbooks_Failed = $All_Failures.policyid.Guid | Select-Object -Unique

# For each Runbook with failures, confirm then format each failed instances into a single email per Runbook (to avoid spam)
foreach ($Runbook in $Runbooks_Failed)
{
    $failures = $array01 = $path = $null
    $failures = ($All_Failures | ? {$_.policyid.guid -ilike "*$Runbook*"}).UniqueID.Guid
    $array01 =  @()

    # Confirm each failure
    foreach ($failure in $failures)
    {
        $failure_id = "'" + $failure + "'"
        $confirm = Invoke-SQL -dataSource $DB_Server -database $DB_Name -sqlCommand "declare @max int
        set @max = (SELECT max(oi.InstanceNumber)FROM OBJECTINSTANCES oi
        where InstanceID = $failure_id )


        SELECT case when oi.ObjectStatus = 'success' then 1 else 0 end
        FROM OBJECTINSTANCES oi
        where InstanceID = $failure_id and oi.InstanceNumber = @max
        Order by InstanceNumber"

        # Confirmation : 1 = False-positive ; 0 = Confirmed-failure
        $confirm_value = $confirm.Column1

        $Object = New-Object PSObject        
        $Object | Add-Member -MemberType NoteProperty -Name "Failure" -Value $failure
        $Object | Add-Member -MemberType NoteProperty -Name "Confirmation" -Value $confirm_value    
        $array01 += $Object
    }


    $confirmed_failures = ($array01 | ? {$_.Confirmation -eq "0"}).Failure
    $count = $confirmed_failures.count

    # Get detail for each confirmed failure

    if ($confirmed_failures)
    {
        $array02 = @()

        foreach ($confirmed_failure in $confirmed_failures)
        {
            $confirmed_failure_id = "'" + $confirmed_failure + "'"
            $details = $Object2 = $path2 = $null
            $details = Invoke-SQL -dataSource $DB_Server -database $DB_Name -sqlCommand "
                SELECT p.name, p.UniqueID, o.Name, oi.InstanceNumber, oi.ObjectStatus, CONVERT(VARCHAR(10), oi.StartTime, 101) + ' ' + CONVERT(VARCHAR(26), oi.StartTime, 108) as 'StartTime', case when oi.EndTime is null then '' else CONVERT(VARCHAR(10), oi.EndTime, 101) + ' ' + CONVERT(VARCHAR(26), oi.EndTime, 108) end as 'EndTime', oi.UniqueID, od.[Key], od.Value 
                FROM OBJECTINSTANCES oi join [OBJECTS] o on o.UniqueID = oi.ObjectID
                join OBJECTINSTANCEDATA od on od.ObjectInstanceID = oi.UniqueID
                join POLICYINSTANCES i on oi.InstanceID = i.[UniqueID] 
                join POLICIES p on i.policyid = p.[UniqueID] 
                where InstanceID = $confirmed_failure_id
                Order by InstanceNumber"

            $RunbookName = $details.name | select -First 1
            $RunbookID = $details.UniqueID | select -First 1
            $RunbookPath = ($AllRunbooks | ? {$_.Id -like "$RunbookID"}).path

            $result = $details | Select-Object -Property  @{Label="RunbookName";Expression={($_.name)}},@{Label="ActivityName";Expression={($_.Name1)}},@{Label="ActivityStartTime";Expression={($_.StartTime)}},@{Label="ActivityEndTime";Expression={($_.EndTime)}},@{Label="ActivityStatus";Expression={($_.objectStatus)}},@{Label="MessageType";Expression={($_.Key)}},@{Label="MessageValue";Expression={($_.Value)}}
            $array02 += $result

            # Add an empty row to distinguish each failure
            $break = ''
            $array02 += $break
        }

        [string]$body = $array02 | ConvertTo-Html -Head "<style>table {border-collapse: collapse;padding:5px;}table, th, td {border: 1px solid black;}th{white-space: nowrap;background-color:lightgrey;}</style>" -Title "$RunbookName FAILED" -PreContent "<H1>$RunbookName : $count failed instance(s) detected last 10 minutes</H1><br /><h3><u>Runbook path :</u></h3><p>$RunbookPath</p><h3><u>Activities Details :</u></h3>" -PostContent "<hr /><p>Repport generated by the SCO-Runbook_Failures_Reporter PowerShell Script"
        # Send the email
        Send-MailMessage -body $body -BodyAsHtml -From $SMTP_From -SmtpServer $SMTP_Relay -Subject "Runbook Failed : $RunbookName" -To $SMTP_To
     
    }

    else {"No confirmed failure for this runbook : $Runbook"}          
}

OldComputer

 

J’espère que cela pourra vous être utile !

Laisser un commentaire