PI Services

Le blog des collaborateurs de PI Services

Script Powershell - Fonction de test de ports reseaux

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 
    }
}





 

SCOM - Script de Fermeture des alertes liées a des monitors en état Healthy

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

 

 

Zabbix API with Powershell – Example

L'API de Zabbix, basée sur le standard JSON-RPC 2.0 peux bien sur etre intérrogée aussi avec Powershell.

 Ci-dessous un script montrant le principe d'interrogation de lA'PI, en recuperant certaines infos.

 Vous devez renseigner un compte (<my_zabbix_account>) avec au minimum les droits de lecture sur Zabbix, et le nom ou l'ip du serveur Front Web (<zabbix_frontweb_server>).

 

### Query Zabbix Through native zabbix json api

    $credential = Get-Credential -Credential "my_zabbix_account"

$baseurl = 'https://<name_or_ip_of_front_server>/zabbix'
$params = @{
    body =  @{
        "jsonrpc"= "2.0"
        "method"= "user.login"
        "params"= @{
            "user"= $credential.UserName
            "password"= $credential.GetNetworkCredential().Password
        }
        "id"= 1
        "auth"= $null
    } | ConvertTo-Json
    uri = "$baseurl/api_jsonrpc.php"
    headers = @{"Content-Type" = "application/json"}
    method = "Post"
}

[System.Net.ServicePointManager]::SecurityProtocol = 'tls12'
[Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}

$result = Invoke-WebRequest @params -UseBasicParsing



$params.body = @{
    "jsonrpc"= "2.0"
    "method"= "host.get"
    "params"= @{
        output = "extend"
		selectFunctions = "extend"
		selectLastEvent = "extend"
		selectGroups = "extend"
		selectHosts = "extend"
    }
    auth = ($result.Content | ConvertFrom-Json).result
    id = 2
} | ConvertTo-Json

$result = Invoke-WebRequest @params -UseBasicParsing
$result = $result.Content | ConvertFrom-Json

 

 

 

Script Powershell – SCOM – Suppression d’overrides de monitor

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()
}










Script Powershell – SCOM - Création d’override de monitor pour une instance specifique de la classe cible

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()

Script Powershell – SCOM – Creation d’overrides de monitor pour une classe entiere

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()


 

Script Powershell – Transfert Files Through SFTP

 

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)}









Script Powershell – Extract SCOM to CSV

 

Le script suivant extrait les agents, management servers et gateways vers un fichier CSV en ne conservant que le plus recent.

Decommentez la section <# + $datetime#> pour ajouter au nom du fichier, la date de création.

 

#############################################################
### EXTRACT OF ALL AGENTS MONITORED BY SCOM TO A CSV FILE ###
#############################################################



Param(
[Parameter(Mandatory=$true)]$MS,
[Parameter(Mandatory=$true)]$OutPutPath
)

$ScriptName = "ExtractAssetFromSCOM.ps1"

#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"


#Import of SCOM PS Module
try
{
Import-Module -Name OperationsManager -ErrorAction stop
}
catch
{
$Message = "Error during import of SCOM PS Module"
write-host -ForegroundColor red $Message
NewEventSource
Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1001 -EntryType Warning -Message "$Message"
exit 1
}

#Connection to $MS
try
{
$ScomCon = New-SCOMManagementGroupConnection -ComputerName $MS -PassThru
}
catch
{
$Message = "Error during connection to $MS - Check the credentials used"
write-host -ForegroundColor red $Message
NewEventSource
Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1002 -EntryType Warning -Message "$Message"
exit 1
}


$MGroup = $ScomCon.ManagementGroupName

#AGENT INFO SECTION

#$Agents - ALL AGENTS + ALL MS AND GW, SORTED BY DISPLAYNAME
try
{
$Agents = Get-SCOMAgent | select DisplayName -ExpandProperty DisplayName
$Agents += Get-SCOMManagementServer | select DisplayName -ExpandProperty DisplayName
$Agents = $Agents | Sort-Object -Property DisplayName
}
catch
{
$Message = "Error during retrieve of agents list"
write-host -ForegroundColor red $Message
NewEventSource
Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1003 -EntryType Warning -Message "$Message"
exit 1
}




# Initiate the table
$export_array = @();

# Fill of table
foreach ($Ag in $Agents)
{
$export_array += @($Ag+','+$MGroup);
}


# Naming of CSV file
$datetime = Get-Date -Format "yyyy.MM.dd_HH-mm-ss";
$file_name = "Extract_SCOM_$MGroup`_"<# + $datetime#> + ".csv"; # Uncoment "<# + $datetime#>" to add creation date of file
$file_path = "$OutPutPath\" + $file_name;


# Fill of CSV File
foreach($ColItem in $export_array)
{
$csv_string = "
";
foreach($item in $ColItem)
{
$csv_string = $csv_string + $item ;
}
[array]$content += $csv_string
}

Set-Content $file_path $content


# Check of File Creation
if (Test-Path $file_path)
{
$Message = "
Creation of CSV file`"$file_path`" OK!"
write-host -ForegroundColor Green $Message
NewEventSource
Write-EventLog -LogName "
operations manager" -Source $ScriptName -EventId 1004 -EntryType Information -Message "$Message"

}
else
{
$Message = "
Creation of CSV file KO!"
write-host -ForegroundColor Red $Message
NewEventSource
Write-EventLog -LogName "
operations manager" -Source $ScriptName -EventId 1005 -EntryType Warning -Message "$Message"
exit 1
}

Write-Host $file_name
Write-Host $MGroup


# Deletion of oldest files (keep only the newest)

Write-Host "
...Deletion of oldest files..."
if ($(Get-ChildItem "
$OutPutPath\*SCOM_$MGroup*").count -gt 1)
{
$Newest = Get-ChildItem "
$OutPutPath\*SCOM_$MGroup*" | sort creationtime | select -last 1
Get-ChildItem "
$OutPutPath\*SCOM_$MGroup*" | Where-Object {$_.Name -ne $Newest.Name} | Remove-Item
}


Grafana – Ajout de Scom en tant que Data Source et création d’un dashboard des alertes

 

Grafana (https://grafana.com/) est une solution de Dashboard open source particulièrement souple et ouverte grâce a de nombreux plugin et data source intégrables.

Dans cet exemple nous allons ajouter l’accès a une base sql SCOM en tant que Data Source et créer un Dashboard des alertes.

 

clip_image002

Dans la zone Configuration de l’interface Grafana nous selectionnons “Data Sources”

clip_image004

Clic sur “Add data source”

clip_image006

On renseigne un nom

On selectionne le Type “Microsoft SQL Server”

On renseigne le nom de l’instance, la base et le compte d’acces.

Clic sur “Save and Test”

clip_image008

clip_image010

clip_image012

Dans la zone “Create” de Grafana, on selectionne “Dashboard”

On ajoute maintenant au Dashboard, un panel qui sera en fait une brique du dashboard.

clip_image014

On sélectionne le modèle “Table”

clip_image016

On clique sur “Panel Title” en haut du tableau et on sélectionne “Edit”

clip_image018

Dans l’onglet “General”, Dans Title, on renseigne un nom.

clip_image020

On se positionne sur l’onglet “Metrics” et on selectionne dans “Data Source” la source de donnée.

clip_image022

Dans le champ de la première Query (A), on colle le code sql correspondant a une sélection des alertes non closes (fichier sql ci-dessous)

 

 

 

clip_image024

Les données sont immédiatement affichées.

on va maintenant effectuer quelques configuration de formatage.

clip_image026

Dans l’onglet “Options” on augmente le champs “Rows per page” a 1000 pour eviter d’avoir trop de pages.

clip_image028

La colonne Severity nous affiche deux decimale apres la virgule. Inutile dans notre cas car cette valeur correspond a des codes de severité (0,1,2)

clip_image030

Pour cela, dans l’onglet “Column Styles”, on ajoute une règle (+Add)

clip_image032

On selectionne dans “Apply to Columns named” la colonne Severity (au passage, notre règle prend le nom de la colonne sélectionnée)

Et on positionne le champ “Decimals” a 0.

clip_image034

Tres bien, mais il serait plus parlant d’avoir les noms de séverité correspondant a ces codes…

clip_image036

On positionne le champ “Type” a String

clip_image038

Et on fait correspondre a chaque code, la valeur adéquate.

clip_image040

Tres bien. Et pour parfaire ce formatage, on va ajouter un peu de couleur…

clip_image042

Dans le champ Threshold, on va definir que les alertes de séverité 1 prennent une couleur orange (WARNING), et les alertes de séverité 2 une couleur rouge (CRITICAL).

NB: L’ordre des couleurs suit l’ordre des seuils (Threshold), séparés par une virgule.

clip_image044

Et l’on peut appliquer ce formattage de couleur a la celulle, en selectionnant la valeur Cell dans le champ “Color Mode”

clip_image046

clip_image048

clip_image050

N’oublions pas de sauvegarder notre dashboard en haut a droite de l’interface Grafana

clip_image052

clip_image054

Dernier petit réglage pour que notre dashboard se rafraichisse automatiquement

clip_image056

On clique sur les settings du dashboard en haut a droite de l’interface Grafana

clip_image058

clip_image060

On positionne la Time Zone a celle du navigateur (Local browser time) et l’auto-refresh a 1m (1 minute).

On sauvegarde ces réglages via le bouton Save.

Supervision – SQL – 3 scripts pour la supervision de Always On

 

Dans le cadre d’un portage de règle de supervision, les trois scripts ci-dessous ont été crées pour remonter l’état de:

- Un Availability Group

- Un Availability Replica

- Un Database Replica

 

En pratique ils utilisent a peu près la même requête SQL. Ils contiennent des éléments propre a l’api scom mais peuvent bien sur être adapté pour être utilisé indépendamment.

Ci-dessous les 3 scripts et l’exemple du code du premier, Check_SQL_AO_AvailabilityGroupStatus_Query_Version.ps1.

 

 

#######################################################################################
#         
#
         
#          Script: SQLAlwaysOnAvailGroupStatus.ps1

#          Purpose: Shows AlwaysOn Availability Group Status
#         
#          Parameters:

#           $DBServer: ShortName of DB Server
#          $InstanceFullName: Name of SQL Instance
#          $AvailGroupName: Availability Group Name
#         
#

########################################################################################

param ( 
           
$Arguments,

           
[string]$DBServer,
           
[string]$InstanceFullName,
       
[string]$AvailGroupName
           
                          
           
)


# Create local variables from override value
.([Scriptblock]::Create($Arguments))

$Scriptname = "SQLAlwaysOnAvailabilityReplicaStatus.ps1"


# Name of Instance

$ServerInstance = $InstanceFullName.Split('\')[1]



    #Determine TcpPort Used for Instance
    try
    {
    $TcpPort = Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.$ServerInstance\MSSQLServer\SuperSocketNetLib\Tcp\IpAll" | select TcpPort -exp tcpport
    }
    catch
    {
    $Message = "Error during Retrieve of TCP Port Used - Check the execution of the script"
    write-host -f Yellow $Message
    $PropertyBag.AddValue("State","WARNING")
    $PropertyBag.AddValue("Message",$Message)
    $PropertyBag.AddValue("ReplicaName",$AvailabilityReplicaName)
    $PropertyBag
    Exit 1
    }





# Scom Object and Property Bag
$api = New-Object -comObject “MOM.ScriptAPI” 
$PropertyBag = $api.CreatePropertyBag()


# Function  GetAODBRepStatus
Function GetAOAvailGroupStatus
                        {
                        param(
                                    [string]$TargetComputer=$DBServer,
                                    $global:Source = "$DBServer\$ServerInstance",
                                    [string]$sqlCommand =
                                            $("
                                            SELECT
                                           
                                            AO_AG.name as AvailGroupName
                                            ,HADR_AO_AGS.synchronization_health as AvailGroup_SyncHealth
                                            ,HADR_AO_AGS.synchronization_health_desc as AvailGroup_SyncHealth_Desc
                                            --,SYSDB.name as DBName
                                           
                                            --,AO_AVREP.replica_server_name
                                            --,HADR_AVAIL_REP_STATE.synchronization_health as AvailReplica_SyncHealth
                                           
                                            --,HADR_AVAIL_REP_STATE.synchronization_health_desc as AvailReplica_SyncHealth_Desc

                                            --,HADR_DB_REP_STATE.synchronization_state as DBReplica_SyncState
                                            --,HADR_DB_REP_STATE.synchronization_state_desc as DBReplica_SyncState_Desc
                                            --,HADR_DB_REP_STATE.synchronization_health_desc as DBReplica_SyncHealth_Desc

                                           
                                            FROM
                                            [sys].[availability_databases_cluster] AO_DB_CLUS
                                            --INNER JOIN sys.databases SYSDB on CAST(SYSDB.group_database_id AS VARCHAR(50)) =  CAST(AO_DB_CLUS.group_database_id AS VARCHAR(50))
                                            INNER JOIN sys.dm_hadr_database_replica_states HADR_DB_REP_STATE on CAST(HADR_DB_REP_STATE.group_database_id AS VARCHAR(50)) = CAST(AO_DB_CLUS.group_database_id AS VARCHAR(50))
                                            --INNER JOIN sys.dm_hadr_availability_replica_states HADR_AVAIL_REP_STATE on HADR_AVAIL_REP_STATE.group_id = HADR_DB_REP_STATE.group_id
                                            INNER JOIN sys.availability_groups AO_AG on AO_AG.group_id = HADR_DB_REP_STATE.group_id
                                            INNER JOIN sys.dm_hadr_availability_group_states HADR_AO_AGS on HADR_AO_AGS.group_id = AO_AG.group_id
                                            --INNER JOIN [sys].[availability_replicas] AO_AVREP on AO_AVREP.replica_id = HADR_DB_REP_STATE.replica_id
                                            WHERE AO_AG.name = '
$AvailGroupName
'
                                           
                                            GROUP BY
                                            AO_AG.name
                                            ,HADR_AO_AGS.synchronization_health
                                            ,HADR_AO_AGS.synchronization_health_desc
                                            "
                                            )
                               )

                               Try
                               {

                                                                                           
                                               

                                            $global:connectionString = "Data Source=$Source,$TcpPort;" +
                                            "Integrated Security=SSPI; " +
                                            "Initial Catalog=master"

                                           
                                            $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
                                           
                               
                                                               
                                            $connection.Open()
                                                                                 
                                                                              


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

                                }
                                catch
                                {
                                write-host -F Red $("Error during sql connection - check the credentials used").ToUpper()
                                #exit 1
                                }

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

                                $connection.Close()
                                $Set.Tables

                               

                              }




# Execute function and get data
try
{
[array]$AvailGroup = GetAOAvailGroupStatus
}
catch
{
$Message = "WARNING - Error during Connection to master database or execution of sql query"
write-host -F Yellow $Message
$PropertyBag.AddValue("State","WARNING")
$PropertyBag.AddValue("Message",$Message)
$PropertyBag.AddValue("DBServer",$DBServer)
$PropertyBag.AddValue("connectstring",$connectionString)
$PropertyBag.AddValue("AvailGroupName",$AvailGroupName)
$PropertyBag.AddValue("AvailGroupStatus","no_data")
$PropertyBag
exit 1
}



if (!($AvailGroup))
{
$Message = "WARNING - Error - No Availability Group have been found"
write-host -F Yellow $Message
$PropertyBag.AddValue("State","WARNING")
$PropertyBag.AddValue("Message",$Message)
$PropertyBag.AddValue("DBServer",$DBServer)
$PropertyBag.AddValue("AvailGroupName","no_data")
$PropertyBag.AddValue("AvailGroupStatus","no_data")
$PropertyBag
exit 1
}



 

try
{
$AvailGroupState = $AvailGroup  | select AvailGroup_SyncHealth_Desc -ExpandProperty AvailGroup_SyncHealth_Desc
}
catch
{
$Message = "Error during Retrieve of Availability Group State"
Write-Host -ForegroundColor Yellow $Message
$PropertyBag.AddValue("State","WARNING")
$PropertyBag.AddValue("Message",$Message)
$PropertyBag.AddValue("DBServer",$DBServer)
$PropertyBag.AddValue("AvailGroupName",$AvailGroupName)
$PropertyBag.AddValue("AvailGroupStatus","no_data")
$PropertyBag
exit 1
}




"AVAILABILITY GROUP: $AvailGroupName"



If ($AvailGroupState -eq "Healthy")

        {
        $Message = "OK - Status of $AvailGroupName Availability Group is Healthy"
        write-host -f Green $Message
        $PropertyBag.AddValue("State","OK")
        $PropertyBag.AddValue("Message",$Message)
        $PropertyBag.AddValue("DBServer",$DBServer)
        $PropertyBag.AddValue("AvailGroupName",$AvailGroupName)
        $PropertyBag.AddValue("AvailGroupStatus","Healthy")
        $PropertyBag
        Exit 0
        }
       


ElseIf ($AvailGroupState -eq "Error")

   
        {
        $Message = "CRITICAL - Status of $AvailGroupName Availability Group is Error"
        write-host -f Red $Message
        $PropertyBag.AddValue("State","CRITICAL")
        $PropertyBag.AddValue("Message",$Message)
      $PropertyBag.AddValue("DBServer",$DBServer)
        $PropertyBag.AddValue("AvailGroupName",$AvailGroupName)
        $PropertyBag.AddValue("AvailGroupStatus","Error")
        $PropertyBag
        Exit 0
        }
       
Else   {
        $Message = "WARNING - Status of $AvailGroupName Availability Group cannot be determined"
        write-host -f yellow $Message
        $PropertyBag.AddValue("State","WARNING")
        $PropertyBag.AddValue("Message",$Message)
      $PropertyBag.AddValue("DBServer",$DBServer)
        $PropertyBag.AddValue("AvailGroupName",$AvailGroupName)
        $PropertyBag.AddValue("AvailGroupStatus","no_data")
        $PropertyBag
        Exit 1
        }
 
 
########################################################################################