Le cas présenté ici, peut être indépendant de l'outil de monitoring. Il s'agit de devoir alerter et donc définir des seuil sur une donnée qui est présenté par défaut sous forme de texte.

Pour être un peu plus clair, dans le contexte, un équipement snmp renvoi l'information d'utilisation de son file system sous la forme suivante:

"/tmp=0, /var=26, /var/log=0, /=22"

il s'agit en effet de la valeur de l'OID atpDiskUsage dans la MIB ATP-SNMP-MIB (Symantec Advanced Threat Protection)

Pour pouvoir traiter les valeurs d'utilisation du file system comme des nombre, les expressions régulières sont d'un grand secours.

Supposons que l'on veuille alerter sur le fait que "/var" soit au dessus de 80.

Dans ce cas l'expression proposé est la suivante régulière sera la suivante:

^.*(\/var=)[8-9][0-9]|100,.*$

Explication --> ^ : début de chaine

                      .*(\/var=) : n caractère n fois, suivi de "/var="  (on doit échapper le caractère '/' avec '\')

                      [8-9][0-9] : le range de chiffre est de 80 a 99

                      |100 : ou bien sur 100.

                       ,.* : la virgule présente, suivi de n caractère n fois

                       $: fin de chaine

Ce qui, pour nos 4 points de montage du file system donnera:

^.*(\/var=)[8-9][0-9]|100,.*$

^.*(\/tmp=)[8-9][0-9]|100,.*$

^.*(\/var\/log=)[8-9][0-9]|100,.*$

^.*(\/=)[8-9][0-9]|100.*$

Dans le contexte de Zabbix, Les expressions régulières ci-dessus ont donc été intégrées chacune dans un trigger utilisant la fonction "regexp() - Regular expression matching last value in period (1- match, 0 - not match)".

Indépendamment de l'outil de supervision, n'hésitez pas a user et abuser d'un outil de construction de regex tel que https://regex101.com/

Le script ci-dessous intègre la requête SQL de récupération des instances d'une machine, sous forme de fonction.

All_InstancesForOneAgent_vSQLQuery.ps1

# SCRIPT THAT QUERY SCOM DATABASE TO GET ALL INSTANCES OF ALL CLASSES FOR A GIVEN COMPUTER ($TargetAgent)
# $TargetComputer must be Short name of computer because we look for this specific string in name, displayname and path of instances.

param(
#Short name of computer
$TargetComputer = "MyComputer"
)


# Function  Invoke-InstancesFromSQL

Function Invoke-InstancesFromSQL
                            {
                                param(
                                    [string]$dataSource = "MyScomSQLServer\OPSMGR",
                                    [string]$database = "OperationsManager",
                                    [string]$TargetComputer,
                                    [string]$sqlCommand = 
                                            $("Use $Database
                                                SELECT 
                                                   MTV.DisplayName as ClassName
	                                               ,MEGV.Path as Instance_Path
	                                               ,MEGV.Id as Instance_Id
	                                               ,MEGV.[DisplayName] as 'Entity_DisplayName'
	                                               ,MEGV.[Name] as 'Entity_Name'
	                                               ,MEGV.[FullName] as Entity_FullName
                                                   ,[IsManaged]
                                                   ,[IsDeleted]
                                                   ,HealthState = 
													CASE WHEN InMaintenanceMode = '0'
														  THEN 
															CASE [HealthState]
															WHEN '0' THEN 'Not Monitored'
															WHEN '1' THEN 'OK'
															WHEN '2' THEN 'Warning'
															WHEN '3' THEN 'Critical'
															END
														WHEN InMaintenanceMode = '1'
														THEN 
															CASE [HealthState]
															WHEN '0' THEN 'In Maintenance Mode'
															WHEN '1' THEN 'OK'
															WHEN '2' THEN 'Warning'
															WHEN '3' THEN 'Critical'
															END
													END
                                                  ,Is_Available = CASE [IsAvailable]
			                                            WHEN '1' THEN 'YES'
			                                            WHEN '2' THEN 'NO'
			                                            END
                                                  
                                                  ,In_MaintenanceMode = CASE [InMaintenanceMode]
			                                            WHEN '0' THEN 'NO'
			                                            WHEN '1' THEN 'YES'
			                                            END
                                                  
                                                  ,Start_Of_Maintenance = CASE WHEN InMaintenanceMode = '0' 
			                                            THEN null
			                                            ELSE MMV.StartTime
			                                            END
	                                              ,End_Of_Maintenance = CASE WHEN InMaintenanceMode = '0'
			                                            THEN null
			                                            ELSE MMV.ScheduledEndTime
			                                            END
                                                  	                                              
                                                  ,Maintenance_RootCause = 
														CASE WHEN InMaintenanceMode = '0'
														  THEN null
															ELSE 
																CASE MMV.ReasonCode
																WHEN '0' THEN 'Other (Planned)'
																WHEN '1' THEN 'Other (Unplanned)'
																  WHEN '2' THEN 'Hardware: Maintenance (Planned)'
																  WHEN '3' THEN 'Hardware: Maintenance (Unplanned)'
																  WHEN '4' THEN 'Hardware: Installation (Planned)'
																  WHEN '5' THEN 'Hardware: Installation (Unplanned)'
																  WHEN '6' THEN 'Operating System: Reconfiguration (Planned)'
																  WHEN '7' THEN 'Operating System: Reconfiguration (Unplanned)'
																  WHEN '8' THEN 'Application: Maintenance (Planned)'
																  WHEN '9' THEN 'Application: Maintenance (Unplanned)'
																  WHEN '10' THEN 'Application: Installation (Planned)'
																  WHEN '11' THEN 'Application: Unresponsive'
																  WHEN '12' THEN 'Application:  Unstable'
																  WHEN '13' THEN 'Security Issue'
																  WHEN '14' THEN 'Loss of network connectivity (Unplanned)'
																END
															END
                                                  
		                                            ,Maintenance_Reason =   
														CASE WHEN InMaintenanceMode = '0' 
														    THEN null
															    ELSE MMV.Comments
														END
	  
      
                                              FROM [OperationsManager].[dbo].[ManagedEntityGenericView] MEGV

                                              INNER JOIN [dbo].[ManagedTypeView] MTV on MEGV.MonitoringClassId = MTV.Id
                                              INNER JOIN [OperationsManager].[dbo].[MaintenanceModeView] MMV on MEGV.id = MMV.BaseManagedEntityId
                                              WHERE (MEGV.Name  like '%$TargetComputer%' OR MEGV.DisplayName  like '%$TargetComputer%' OR MEGV.Path  like '%$TargetComputer%')
                                              and MTV.LanguageCode = 'ENU'
                                              and MEGV.HealthState is not null
                                              and MEGV.IsDeleted <> '1'
                                              ORDER BY MTV.DisplayName
                                            ")
                                           )


                                    $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)
                                
                                try
                                {
                                $connection.Open()
                                }
                                catch
                                {
                                write-host -F Red $("Error during sql connection - check the credentials used").ToUpper()
                                exit 1
                                }

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

                                $connection.Close()
                                $dataSet.Tables

                              }


[array]$table = Invoke-InstancesFromSQL -TargetComputer $TargetComputer


write-host "`nCOMPUTER: $TargetComputer"

Write-Host "`nNb Of Objects:"$table.count""

$table | ft -Property ClassName,Instance_Path,Entity_DisplayName,Entity_Name,Entity_FullName,HealthState,Is_Available,In_MaintenanceMode,Start_Of_Maintenance,End_Of_Maintenance,Maintenance_RootCause,Maintenance_Reason



# TO FILTER ON SPECIFIC CLASS
# $table | Where-Object {$_.CLASS_NAME -in ('Windows computer','Health Service Watcher','VMware vSphere Host','VMWare Virtual Machine','HPE ProLiant Server')} | ft -AutoSize



# SIMULATE START OF MAINTENANCE MODE FOR THE INSTANCES
#$table | foreach {Get-SCOMClassInstance -id $_.instance_id} | foreach {start-SCOMMaintenanceMode -instance $_ -WhatIf -EndTime $((Get-Date).AddHours(1))}

L'exemple ci-dessous utilise le module PowerShell PSBBIX qui encapsule les commandes de l'API Zabbix.

Parmi les nombreuses commandes, pour exemple, le script propose de désactiver ou activer les items (points de supervision) d'un host spécifique.

N'hésitez pas pour plus de détails a aller voir le github de PSBBIX (https://github.com/yubu/psbbix-zabbix-api)

ZabbixManageHostItems.ps1

#########################################################
### ENABLE/DISABLED ALL SPECIFIC ITEMS FOR A ZABBIX HOST
### Params:
### $ZabbixSrv: Target Zabbix Server
### $TargetHost: Target host to treat
### $ItemPattern: Pattern of Items to check
### $Action: Disable or Enable
#########################################################


Param(
[Parameter(Mandatory=$true)] $ZabbixSrv,
[Parameter(Mandatory=$true)] $TargetHost,
[Parameter(Mandatory=$true)] $ItemPattern,
[Parameter(Mandatory=$true)] $Action
)


if ($PSBoundParameters.Count -lt 4)
    {
    write-host -F Red "Too few arguments!"
    write-host -F Yellow "ZabbixSrv: Target Zabbix Server`nTargetHost: Target host to treat`nItemPattern: Pattern of Items to check`nAction: Disable or Enable"
    exit 1
    } 

switch($Action)
{
"disable" {$status = 1}
"enable" {$status = 0}
default {Write-Host -F Red "Possible value for Action is 'enable' or 'disable' - END OF SCRIPT" ; exit 0}
}



# Check if psbbix module is loaded
if (!(get-module psbbix))
    {
    write-host -F Yellow "psbbix module is not loaded...search module to load it..."
    $modulepath = $env:PSModulePath -split ‘;’ | foreach {Get-ChildItem -Recurse -Path $_ | Where-Object {$_.name -eq "psbbix.psm1"}}
    if (!$modulepath)
        {
        write-host -F Yellow "unable to find psbbix.psm1...get it on https://github.com/yubu/psbbix-zabbix-api"
        exit 1
        }
    Else
        {
            try
            {
            Import-Module $($modulepath | select -Unique).fullname
            }
            catch
            {
            write-host -F error "Error during import of psbbix module"
            exit 1
            }
        }
    Write-Host -F Green "...Module loaded"

    }


$cred = Get-Credential -Credential "myaccount"


# Connect to zabbix server
try
{
Connect-Zabbix $ZabbixSrv -PSCredential $cred
}
catch
{
Write-Host -F Red "Error during connection to $ZabbixSrv"
exit 1
}


# Get all items that we want to disable
$items = Get-ZabbixItem -hostid $(Get-ZabbixHost -HostName $TargetHost).hostid | Where-Object {$_.name -like $ItemPattern}

if ($items.Count -eq 0)
    {
    Write-Host -F Yellow "No items found with '$ItemPattern' pattern for $TargetHost host"
    }

Else
    {
        try
        {
        $items | foreach {Set-ZabbixItem -itemid $_.itemid -status $status}
        }
        catch
        {
        write-host -F Red "Error during modification of Items status"
        exit 1
        }

    }


Write-host "`nFollowing items have been treated for $TargetHost`:`n"
$items = Get-ZabbixItem -hostid $(Get-ZabbixHost -HostName $TargetHost).hostid | Where-Object {$_.name -like $ItemPattern}
$items | foreach {$_.name +" -- "+  $(switch($_.status){0{"enabled"}1{"disabled"}})}

Ci-dessous une fonction dont le besoin est récurrent, pour tester la disponibilité d'un ou plusieurs ports réseaux sur une ou plusieurs machines.

 test-port.ps1 (10,05 kb)

# TEST-PORT
<#
.SYNOPSIS
         CHECK AVAILABILITY OF NETWORK PORT CONNECTION ON REMOTE COMPUTERS
.OUTPUTS 
         Return Open Status and details
.PARAMETER 
        $computer: One or more target computers
        $port: One or more port to test
        $TCPtimeout: Timeout for TCP, in ms
        $UDPtimeout: Timeout for UDP, in ms
        $TCP: Precise TCP connection
        $UDP: Precise UDP connection
                 

.USAGE:
#Test-Port -computer (get-content .\servers.txt) -port 3389
#Test-Port -computer (get-content .\servers.txt) -port 80,443,23000,17990 -TCP -TCPtimeout 1000
#Test-Port -computer myserver -port 8080,8443,10443,3389,443,80,22 -TCP -UDP
#>



function Test-Port{  




 
[cmdletbinding(  
    DefaultParameterSetName = '',  
    ConfirmImpact = 'low'  
)]  
    Param(  
        [Parameter(  
            Mandatory = $True,  
            Position = 0,  
            ParameterSetName = '',  
            ValueFromPipeline = $True)]  
            [array]$computer,  
        [Parameter(  
            Position = 1,  
            Mandatory = $True,  
            ParameterSetName = '')]  
            [array]$port,  
        [Parameter(  
            Mandatory = $False,  
            ParameterSetName = '')]  
            [int]$TCPtimeout=500,  
        [Parameter(  
            Mandatory = $False,  
            ParameterSetName = '')]  
            [int]$UDPtimeout=500,             
        [Parameter(  
            Mandatory = $False,  
            ParameterSetName = '')]  
            [switch]$TCP,  
        [Parameter(  
            Mandatory = $False,  
            ParameterSetName = '')]  
            [switch]$UDP                                    
        )  
    Begin {  
        If (!$tcp -AND !$udp) {$tcp = $True}  
        
        $ErrorActionPreference = "SilentlyContinue"  
        $report = @()  
    }  
    Process {     
        ForEach ($c in $computer) {  
            ForEach ($p in $port) {  
                If ($tcp) {    
                    #Create temporary holder   
                    $temp = "" | Select hostname,Server, Port, TypePort, Open, Notes  
                    #Create object for connecting to port on computer  
                    $tcpobject = new-Object system.Net.Sockets.TcpClient  
                    #Connect to remote machine's port                
                    $connect = $tcpobject.BeginConnect($c,$p,$null,$null)  
                    #Configure a timeout before quitting  
                    $wait = $connect.AsyncWaitHandle.WaitOne($TCPtimeout,$false)  
                    #If timeout  
                    If(!$wait) {  
                        #Close connection  
                        $tcpobject.Close()  
                        Write-Verbose "Connection Timeout"  
                        #Build report  
                        $temp.hostname= [System.Net.dns]::Resolve($c).hostname
                        $temp.Server = $c  
                        $temp.Port = $p  
                        $temp.TypePort = "TCP"  
                        $temp.Open = "False"  
                        $temp.Notes = "Connection to Port Timed Out"  
                    } Else {  
                        $error.Clear()  
                        $tcpobject.EndConnect($connect) | out-Null  
                        #If error  
                        If($error[0]){  
                            #Begin making error more readable in report  
                            [string]$string = ($error[0].exception).message  
                            $message = (($string.split(":")[1]).replace('"',"")).TrimStart()  
                            $failed = $true  
                        }  
                        #Close connection      
                        $tcpobject.Close()  
                        #If unable to query port to due failure  
                        If($failed){  
                            #Build report 
                            $temp.hostname= [System.Net.dns]::Resolve($c).hostname 
                            $temp.Server = $c  
                            $temp.Port = $p  
                            $temp.TypePort = "TCP"  
                            $temp.Open = "False"  
                            $temp.Notes = "$message"  
                        } Else{  
                            #Build report
                            $temp.hostname= [System.Net.dns]::Resolve($c).hostname  
                            $temp.Server = $c  
                            $temp.Port = $p  
                            $temp.TypePort = "TCP"  
                            $temp.Open = "True"    
                            $temp.Notes = ""  
                        }  
                    }     
                    #Reset failed value  
                    $failed = $Null      
                    #Merge temp array with report              
                    $report += $temp  
                }      
                If ($udp) {  
                    #Create temporary holder   
                    $temp = "" | Select Server, Port, TypePort, Open, Notes                                     
                    #Create object for connecting to port on computer  
                    $udpobject = new-Object system.Net.Sockets.Udpclient
                    #Set a timeout on receiving message 
                    $udpobject.client.ReceiveTimeout = $UDPTimeout 
                    #Connect to remote machine's port                
                    Write-Verbose "Making UDP connection to remote server" 
                    $udpobject.Connect("$c",$p) 
                    #Sends a message to the host to which you have connected. 
                    Write-Verbose "Sending message to remote host" 
                    $a = new-object system.text.asciiencoding 
                    $byte = $a.GetBytes("$(Get-Date)") 
                    [void]$udpobject.Send($byte,$byte.length) 
                    #IPEndPoint object will allow us to read datagrams sent from any source.  
                    Write-Verbose "Creating remote endpoint" 
                    $remoteendpoint = New-Object system.net.ipendpoint([system.net.ipaddress]::Any,0) 
                    Try { 
                        #Blocks until a message returns on this socket from a remote host. 
                        Write-Verbose "Waiting for message return" 
                        $receivebytes = $udpobject.Receive([ref]$remoteendpoint) 
                        [string]$returndata = $a.GetString($receivebytes)
                        If ($returndata) {
                           Write-Verbose "Connection Successful"  
                            #Build report
                            $temp.hostname= [System.Net.dns]::Resolve($c).hostname  
                            $temp.Server = $c  
                            $temp.Port = $p  
                            $temp.TypePort = "UDP"  
                            $temp.Open = "True"  
                            $temp.Notes = $returndata   
                            $udpobject.close()   
                        }                       
                    } Catch { 
                        If ($Error[0].ToString() -match "\bRespond after a period of time\b") { 
                            #Close connection  
                            $udpobject.Close()  
                            #Make sure that the host is online and not a false positive that it is open 
                            If (Test-Connection -comp $c -count 1 -quiet) { 
                                Write-Verbose "Connection Open"  
                                #Build report
                                $temp.hostname= [System.Net.dns]::Resolve($c).hostname  
                                $temp.Server = $c  
                                $temp.Port = $p  
                                $temp.TypePort = "UDP"  
                                $temp.Open = "True"  
                                $temp.Notes = "" 
                            } Else { 
                                <# 
                                It is possible that the host is not online or that the host is online,  
                                but ICMP is blocked by a firewall and this port is actually open. 
                                #> 
                                Write-Verbose "Host maybe unavailable"  
                                #Build report
                                $temp.hostname= [System.Net.dns]::Resolve($c).hostname  
                                $temp.Server = $c  
                                $temp.Port = $p  
                                $temp.TypePort = "UDP"  
                                $temp.Open = "False"  
                                $temp.Notes = "Unable to verify if port is open or if host is unavailable."                                 
                            }                         
                        } ElseIf ($Error[0].ToString() -match "forcibly closed by the remote host" ) { 
                            #Close connection  
                            $udpobject.Close()  
                            Write-Verbose "Connection Timeout"  
                            #Build report 
                            $temp.hostname= [System.Net.dns]::Resolve($c).hostname 
                            $temp.Server = $c  
                            $temp.Port = $p  
                            $temp.TypePort = "UDP"  
                            $temp.Open = "False"  
                            $temp.Notes = "Connection to Port Timed Out"                         
                        } Else {                      
                            $udpobject.close() 
                        } 
                    }     
                    #Merge temp array with report              
                    $report += $temp 
					$temp
                }                                  
            }  
        }                  
    }  
    End {  
        #Generate Report  
        $report |ft -AutoSize 
    }
}

Introduction

Le script suivant permet :

• de récupérer le résultat d’une requête SQL dans un fichier .csv,
• de renseigner les actions réalisées dans l’EventViewer,
• de retourner un code d’erreur (0 ou 1) suite à l’exécution du script.

Prérequis

• Powershell
• SQL

Présentation du script

Variables :

[string] $server = "localhost" : Serveur SQL
[string] $database = "base" : Base SQL
[string] $query = "SELCT * FROM Table" : Requête ou procédure stockée
[string] $extractFilePath = "C:\test.csv" : Emplacemnt de génération du fichier .csv
[string] $delimiter = ";" : Délimiteur à utiliser pour la génération du fichier .csv
[int] $skipline = 1 : Nombre de ligne à tronquer (utile si l’on souhaite retirer la ligne d’entête.

Script

# Variables d'entrée
param
    (
    [string] $server = "localhost", 
    [string] $database = "base",
    [string] $query = "SELECT * FROM Table",
    [string] $extractFilePath = "C:\test.csv",
    [string] $delimiter = ";",
    [int] $skipline = 1

    )

#MAIN
function main
{

#Variables calculées
$full_date = Get-Date;
$custom_date = $full_date.ToString("yyyyMMdd");
$DataSet = New-Object System.Data.DataSet;
$eventsource ="CsvFromSQL_Powershell";
$eventlogname = "Application";
[int] $errorcode = 1;


#Génération de la source d'evenements si inexistante
if ([System.Diagnostics.EventLog]::SourceExists($eventsource) -eq $false) 
    {
        #write-host "Creating event source $eventsource on event log $eventlogname"
        [System.Diagnostics.EventLog]::CreateEventSource($eventsource, $eventlogname)
        #write-host -foregroundcolor green "Event source $eventsource created"
    }
        else
    {
        #write-host -foregroundcolor yellow "Warning: Event source $eventsource already exists. Cannot create this source on Event log $eventlogname"
    }

<#
$logFileExists = Get-EventLog -list | Where-Object {$_.logdisplayname -eq $eventlogname} 
if (! $logFileExists) 
    {
        New-EventLog -LogName $eventlogname -Source $eventsource
    }
#>

#Execution des fonctions et récupération du code d'erreur
$errorcode = Test-SQLConn -_server $server -_database $database;
$errorcode = sql_to_dataset -_server $server -_database $database -_query $query -_dataset $DataSet;
$errorcode = dataset_to_csv -_dataset $DataSet -_extractFilePath $extractFilePath;
return $errorcode;
}

#Test connection SQL
Function Test-SQLConn ($_server, $_database)
{
    $errorcode=0;
    $connectionString = "Data Source=$_server;Integrated Security=true;Initial Catalog=$_database;Connect Timeout=3;";
    $sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString;
    trap
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "Cannot connect to server $_server or database $_database.";
        $errorcode=1;
        Write-Host $errorcode;
        exit
        }
    $sqlConn.Open()
    if ($sqlConn.State -eq 'Open')
        {
        $sqlConn.Close();
        Start-Sleep -s 1;
        Write-EventLog  -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "Connected to server $_server on database $_database.";
        }
}

#Rempli un dataset à partir d'une requête SQL
function sql_to_dataset
    {
    param($_server, $_database, $_query, $_dataset);
    $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};";
    $connectionString = [string]::Format($connectionTemplate, $_server, $_database);
    $errorcode=0;
    try
        {
        $connection = New-Object System.Data.SqlClient.SqlConnection -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL connection has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL connection. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }

    $connection.ConnectionString = $connectionString;

    try
        {
        $command = New-Object System.Data.SqlClient.SqlCommand -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL command has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL command. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }
    
    $command.CommandText = $_query;
    $command.Connection = $connection;

    try
        {
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL DataMapper has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL DataMapper. Error Message : $_.Exception.Message"; 
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }
    
    $SqlAdapter.SelectCommand = $command;

    try
        {
        $SqlAdapter.Fill($_dataSet) | Out-Null -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The Dataset has been filled successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when filling the Dataset. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }

    $connection.Close();
    return $errorcode;
    }

#Rempli un csv avec un dataset
function dataset_to_csv
{
param($_dataset, $_extractFilePath);
$errorcode=0;
    try
        {
        $_dataSet.Tables[0] | ConvertTo-Csv -NoTypeInformation -Delimiter $delimiter | select -Skip $skipline  | Set-Content $_extractFilePath -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The CSV file has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when generating the CSV file. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }
        return $errorcode;
}

main

Ci-dessous une nouvelle version d'un script de fermeture des alertes liées a des monitors en état Healthy. En effet meme si le cas contraire est plus fréquent (alerte fermée alors que le monitor est encore en état Warning ou Critical), il se peut que l'on doivent fermer les alertes de monitors en état Healthy.

Le script affiche clairement la sortie des alertes a traiter et log cette sortie dans l'eventlog.

# SCRIPT TO CHECK INCONSISTENCY BETWEEN NOT CLOSED ALERTS AND HEALTHY MONITORS 


Param(
# Treat alerts that has been modified since less than $LastModifHours
$LastModifHours = 2
)

$ScriptName = "CloseAlertsFromHealthyMonitors.ps1"


# FUNCTIONS

# Check if a source with script name exist in operationsmanager eventlog to log some specific events 
         Function NewEventSource 
         { 
         if(!(Test-Path "HKLM:\SYSTEM\CurrentControlSet\services\eventlog\Operations Manager\$ScriptName")) 
         { 
         New-EventLog -LogName "Operations Manager" -Source $ScriptName 
         }
         } 


# END FUNCTIONS



#Log of script execution 
NewEventSource 
write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1000 -Message "Execution of script $ScriptName (Value of LastModifHours is $LastModifHours hours)" -EntryType Information

# Import of Scom Module
Import-Module OperationsManager


# Get list of closed alerts with following criterias:
# - Not Closed
# - Generated by a monitor

$NotClosedAlerts = Get-SCOMAlert -ResolutionState (0..254)  | where { ($_.ismonitoralert -eq $true) -and $_.LastModified -gt (Get-Date).addhours(-$LastModifHours)}


# Variable to store the result of alert treatment.
# Header
$Result = "`n*********** CHECK INCONSISTENCY BETWEEN NOT CLOSED ALERTS AND HEALTHY MONITORS ***********"

$Result += "`n START `n"


for ($i=0;$i -le $NotClosedAlerts.GetUpperBound(0);$i=$i+1) 
{
# Display alert Nb
$NotClosedAlert = $NotClosedAlerts[$i]
$Result +=  "`n`nalert $i ------------------------------"



# Get IDs from Closed alert 
$mrid = $NotClosedAlert.monitoringruleid 
$mcid = $NotClosedAlert.monitoringclassid 
$moid = $NotClosedAlert.monitoringobjectid 



# Get corresponding class 
$monitoringclass = Get-SCOMClass -id $mcid

# Get the corresponding instance with following criterias:
# - HealthState equal Success 
$MyInstance = Get-SCOMMonitoringObject -Class $monitoringclass | where {$_.id -eq $moid -and $_.HealthState -eq "Success"} 

# If there is no instances no need to reset (exit the loop and treat the next closed alert)
If(!($MyInstance))
{
$Result += "No Instance found in Success state for the alert `"$($NotClosedAlert.Name)`" - NO NEED TO CLOSE ALERT"
}
Else
{
$Result += "`nThe following alert must be closed: `n"
$Result += "NAME: $($NotClosedAlert.Name)`n"
$Result += "ID: $($NotClosedAlert.Id)`n"
$Result += "COMPUTER OR OBJECT: $($NotClosedAlert.MonitoringObjectDisplayName)`n"
$Result += "COMPUTER OR OBJECT FULL PATH: $($NotClosedAlert.MonitoringObjectFullName)`n"
$Result += "COMPUTER OR OBJECT HEALTH STATE: $($NotClosedAlert.MonitoringObjectHealthState)`n"
$Result += "TIME RAISED: $($NotClosedAlert.TimeRaised)`n"
$Result += "TIME LAST MODIFIED: $($NotClosedAlert.LastModified)`n"



    try
    {
    Set-SCOMAlert -Alert $NotClosedAlert -ResolutionState 255
    $Result += "Closing of alert `"$($NotClosedAlert.Name)`"..."
    $Result += "-------------------------------------------------------`n`n`n"
    # Update of Alert History Text
    $NotClosedAlert.Update("Alert closed by $ScriptName script")
    }
    catch
    {
    $Result += "Error during the close of alert `"$($NotClosedAlert.Name)`" (ALERT ID: $($NotClosedAlert.Id))"
    }
}

}

$Result += "`n`n END `n"

# Check if $Result contains error of alert closing
if ($($Result | Out-String).Contains("Error during the close of alert"))
{
$Result += "At least one Error has been encountered during closing of some alerts"
NewEventSource 
write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1001 -Message $Result -EntryType Warning
}
Else
{
$Result += "`n OK - Treatment of Alerts has ending without Error"
NewEventSource 
write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1002 -Message $Result -EntryType Information

}

# Display Result
$Result

Le script suivant supprime tout les overrides correspondant a une propriété donné, pour un ou plusieurs monitors.

### DELETE OVERRIDES FROM MONITORS ###

Param(
$MS
,$cred = $(Get-Credential "MyDomain\Myself")
,$targetMonitorsPatern = "Check My App*"
,$OverrideProp = "GenerateAlert"
)

# Import of SCOM module
try
{
Import-Module -Name OperationsManager -ErrorAction stop
}
catch
{
write-host -ForegroundColor red "Error during import of SCOM module"
}

# Connection to management server $MS
New-SCOMManagementGroupConnection -ComputerName $MS -Credential $cred


 #The monitors 
 $targetMonitors = Get-SCOMMonitor -DisplayName $targetMonitorsPatern


 foreach ($Monitor in $targetMonitors) 
 {
 [array]$OverrideToDelete += Get-SCOMOverride -Monitor $Monitor | Where {$_.Property -eq $OverrideProp} 
 }


 $OverrideToDelete | foreach {

 [array]$targetMP += $_.GetManagementPack()
 $targetMP.FindManagementPackElementByName($_.Name).status='PendingDelete'
 $targetMP.AcceptChanges()
 }

Le script suivant crée un override pour un ou plusieurs monitors, en ciblant une instance de la classe cible, correspondant a un nom de machine donné en paramètre.

### CREATE OVERRIDES FOR MONITORS, FOR SPECIFIC INSTANCE OF TARGET CLASS ###

Param(
$MS
,$cred = $(Get-Credential "MyDomain\Myself")
,$targetMPName = "My App - Overrides"
,$TargetContextComputer = "MyServer.MyDomain.home"
,$targetMonitorsPatern = "Check My App*"
,$OverrideProp = "Enabled"
,$OverrideValue = "True"
,$OverrideSuffix = ".Override_$OverrideProp`_$TargetContextComputer"
)

# Import of SCOM module
try
{
Import-Module -Name OperationsManager -ErrorAction stop
}
catch
{
write-host -ForegroundColor red "Error during import of SCOM module"
}

# Connection to management server $MS
New-SCOMManagementGroupConnection -ComputerName $MS -Credential $cred


# The target MP
 $targetMP = Get-SCOMManagementPack -DisplayName $targetMPName

 # The monitors 
 $targetMonitors = Get-SCOMMonitor -DisplayName $targetMonitorsPatern


foreach ($Monitor in $targetMonitors)
{

    if( $Monitor.AlertSettings.AlertOnState -ne $null)

    {
    $Target= Get-SCOMClass -id $Monitor.Target.Id 
    $TargetContextInstance = Get-SCOMMonitoringObject -Class $Target | Where-Object {$_.'[Microsoft.Windows.Computer].PrincipalName'.value -eq $TargetContextComputer}   # Get the Monitoring Object that match the $TargetContextComputer

    # Name of the override
    $overridename=$Monitor.name+$OverrideSuffix

    # Create Override and fill its properties
    $override = New-Object Microsoft.EnterpriseManagement.Configuration.ManagementPackMonitorPropertyOverride($targetMP,$overridename)
    $override.Monitor = $Monitor
    $override.Property = $OverrideProp
    $override.Value = $OverrideValue
    $override.Context = $Target
    $override.ContextInstance = $TargetContextInstance.Id
    $override.DisplayName = $overridename
    }

}

# Verify and commit the change
$targetMP.Verify()
$targetMP.AcceptChanges()

Le script suivant crée un override pour un groupe de monitor en ciblant toute la classe cible du monitor.

### CREATE OVERRIDES FOR MONITORS, FOR ENTIRE MONITOR TARGET CLASS ###

Param(
$MS
,$cred = $(Get-Credential "MyDomain\Myself")
,$targetMPName = "My App - Overrides"
,$targetMonitorsPatern = "Check My App*"
,$OverrideProp = "GenerateAlert"
,$OverrideValue = "false"
,$OverrideSuffix = ".Override_$OverrideProp`_WholeTarget"
)

# Import of SCOM module
try
{
Import-Module -Name OperationsManager -ErrorAction stop
}
catch
{
write-host -ForegroundColor red "Error during import of SCOM module"
}

# Connection to management server $MS
New-SCOMManagementGroupConnection -ComputerName $MS -Credential $cred



# The target MP
$targetMP = Get-SCOMManagementPack -DisplayName $targetMPName

# The monitors 
$targetMonitors = Get-SCOMMonitor -DisplayName $targetMonitorsPatern


foreach ($Monitor in $targetMonitors)
{

    if($OverrideProp -eq "GenerateAlert" -AND  $OverrideValue -eq "false" -AND $Monitor.AlertSettings.AlertOnState -ne $null) # If we want to change the "GenerateAlert" Property to "false", we check also that Alert is activated

    {
    $Target= Get-SCOMClass -id $Monitor.Target.id
    $overridename=$Monitor.name+$OverrideSuffix

    $override = New-Object Microsoft.EnterpriseManagement.Configuration.ManagementPackMonitorPropertyOverride($targetMP,$overridename)
    $override.Monitor = $Monitor
    $override.Property = $OverrideProp
    $override.Value = $OverrideValue
    $override.Context = $Target
    $override.DisplayName = $overridename
    }

}
$targetMP.Verify()
$targetMP.AcceptChanges()

Dans l’exemple suivant, Le script transfert les extracts scom du précedent article (https://blog.piservices.fr/post/2018/09/27/Script-Powershell-Extract-SCOM-to-CSV) vers un partage unix en utilisant les commandes du module Posh SSH.

Pour plus d’information sur le module Posh-SSH:

https://github.com/darkoperator/Posh-SSH

https://www.it-connect.fr/posh-ssh-connexion-ssh-depuis-powershell-sous-windows/

### TRANSFER CSV FILES THROUGH SFTP TO UNIX SHARE ###

# Requirement: POSH SSH Powershell Module (https://github.com/darkoperator/Posh-SSH)


param(
$UnixServ = ("MyLinuxSrv1","MyLinuxSrv2"),
$FilePath="D:\*.csv",
$SftpPath = "/home/myself/scomextract",
$FileToKeepCount = 2
)

$ScriptName = "SendCSVBySftp.ps1"


$SshModuleFile = "D:\Posh-SSH-master.zip"
$PSModulePath = "$($env:WINDIR)\system32\WindowsPowerShell\v1.0\Modules"



#FUNCTIONS

#Check for the existence of an event source with script name in operation manager eventlog to log some events 
         Function NewEventSource { 
         if(!(Test-Path "HKLM:\SYSTEM\CurrentControlSet\services\eventlog\Operations Manager\$ScriptName")) 
         { 
         New-EventLog -LogName "Operations Manager" -Source $ScriptName 
         }
         } 

#END FUNCTIONS

# LOG THE EXECUTION OF SCRIPT
$Message = "Execution of $ScriptName script"
write-host $Message
NewEventSource
Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1000 -EntryType Information -Message "$Message"



# Get the credentials from the previously secured passfile.txt
$CredPath = "D:\passfile.txt"
if (!(Test-Path $CredPath))
    {
    $message = "Credential secured file is not present - Unable to authenticate"
    Write-Host -F Red $message
    NewEventSource
    Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1001 -EntryType Warning -Message $message
    }


# Import the credential file
$Credential = Import-Clixml -Path $CredPath



# Check that $PSModulePath path exist
if (!(Test-Path $PSModulePath))
    {
    $message = "$PSModulePath path doesn't exist"
    Write-Host -F Red $message
    NewEventSource
    Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1002 -EntryType Warning -Message $message
    exit 1
    }


# Check if module has already been installed, and if not, install it as $PSModulePath+"\Posh-SSH"
If (!(Test-Path ($PSModulePath+"\Posh-SSH")))
            
            {


            #Unzip the SSH Powershell Module to Standard Module Path

            $shell_app=new-object -com shell.application
            $zip_file = $shell_app.namespace($SshModuleFile)
            Write-Host "Uncompressing the Zip file to $($PSModulePath)" -ForegroundColor Cyan
            $destination = $shell_app.namespace($PSModulePath)
            $destination.Copyhere($zip_file.items(), 0x10)


            # Rename the SSH Module Folder
            Write-Host "Renaming folder" -ForegroundColor Cyan
            Rename-Item -Path ($PSModulePath+"\Posh-SSH-master") -NewName "Posh-SSH" -Force

            Write-Host "Module has been installed" -ForegroundColor Green

            }


# Import of SSH Module
Write-Host -F Cyan "SSH Powershell Module already installed"
Write-Host -F Cyan "Import of SSH Powershell Module..."
Import-Module -Name posh-ssh


$UnixServ | foreach {

    # Establish the SFTP connection
    $SftpSession = New-SFTPSession -ComputerName $_ -Credential $Credential -AcceptKey



    # Check that $SftpPath exist
    if (!(Test-SFTPPath -SessionId $SftpSession.SessionId -Path "/$_$SftpPath"))
        {
        $message = "Unable to find `"/$_$SftpPath`" path"
        Write-Host -F Red $message
        NewEventSource
        Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1003 -EntryType Warning -Message $message
        exit 1
        }


        # Upload each file to the SFTP path
        try
        {
        Get-ChildItem -Path $FilePath | foreach {Set-SFTPFile -SessionId $SftpSession.SessionId -LocalFile $_ -RemotePath $SftpPath -Overwrite}
        }
        catch
        {
        $message = "Error during Sftp transfer To `"/$_$SftpPath`" path"
        Write-Host $message
        NewEventSource
        Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1004 -EntryType Warning -Message $message
        exit 1
        }


# Check that files are effectively present on sftp path
Get-ChildItem -Path $FilePath | foreach {

If (!(Test-SFTPPath -SessionId $SftpSession.SessionId -Path "$SftpPath/$($_.Name)"))
    {
    $message = "KO - The $($_.Name) file has not been copied on $($SftpSession.Host)$SftpPath"
    Write-Host -F Red $message
    NewEventSource
    Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1005 -EntryType Warning -Message $message
    exit 1
    }
    Else
    {
    $message = "OK - The $($_.Name) file has well been copied on $($SftpSession.Host)$SftpPath"
    Write-Host -F Green $message
    NewEventSource
    Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1006 -EntryType Information -Message $message
    }

}

}


#Delete older remote files
Get-SFTPSession | foreach {
$FileToDelete = Get-SFTPChildItem -SessionId ($_.SessionId) -Path $SftpPath | Where-Object {$_.fullname -like "*.csv"} | sort lastwritetime | select -First $FileToKeepCount
$FileToDelete
}




# Remove SftpSessions
Get-SFTPSession | foreach {Remove-SFTPSession -SessionId ($_.SessionId)}