PI Services

Le blog des collaborateurs de PI Services

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

 

 

 

SCOM - Déplacer le rôle Reporting

Lors du déplacement du rôle Reporting vers un nouveau serveur SSRS pour le compte d’un client, je me suis heurté à quelques problèmes et interrogations pour lesquelles les réponses n’étaient pas toujours très claires ou accessibles.

On retrouve par exemple un certain nombre de billets de blogs ou de posts sur les forums technet qui indiquent qu’il n’est pas possible de déplacer la base de données utilisée par SSRS, et donc pas possible de conserver facilement tous les rapports personnalisés et planifiés.
Il est en réalité parfaitement possible de procéder à une nouvelle installation de SCOM Reporting Services sur un SSRS vierge puis de restaurer la base du précédent SSRS, à condition de bien réaliser les opérations dans cet ordre :

  • Sur l’ancien serveur de reporting, sauvegarder les bases ReportServer et ReportServerTempDb (noms par défaut) ainsi que la clé de chiffrement de SSRS et le fichier config
  • Installer SSRS sur le nouveau serveur
  • Installer le rôle SCOM Reporting sur le serveur
  • Restaurer les bases ReportServer et ReportServerTempDb ainsi que la clé de chiffrement
  • Reconnecter SSRS aux bases restaurées
  • Reprendre les paramètres dans web.config

 

Un nouveau problème peut survenir dans la foulée : si le serveur SSRS d’origine exécutait une licence supérieure de SSRS (par exemple Datacenter alors que le nouveau serveur SSRS ne dispose que d’une licence Standard), un message d’erreur vous accueillera lorsque vous tenterez de vous connecter au portail :

 

La solution la plus logique serait de supprimer toute référence à l’ancien serveur dans l’onglet Scale-Out de la console SSRS, mais malheureusement cela ne fonctionne pas.

Il est alors nécessaire de passer directement par l’édition de la table dbo.Keys dans la base ReportServer pour en retirer toute référence à l’ancien serveur en supprimant la ligne correspondante :

DELETE FROM [ReportServer].[dbo].[Keys]

WHERE MachineName = 'AncienServeur'

Redémarrez ensuite SSRS, et confirmez que le portail fonctionne maintenant bien et que vous pouvez y retrouver toutes vos personnalisations antérieures !

 

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


SCOM - Réinitialiser complètement la console

Si vous développez des management packs, il vous est probablement déjà arrivé un bug assez agaçant : les colonnes affichées dans les vues (état, alertes…) ne reflètent pas ce que vous avez défini dans votre code.

L’ordre des colonnes n’est pas respecté, certaines colonnes sont visibles alors qu’elles ne devrait pas l’être et réciproquement…

Et en plus le résultat n’est pas le même d’un utilisateur à l’autre !

Vous connaissez probablement déjà le mode /clearcache de la console, mais cela ne change rien au problème.

Il s’agit en réalité d’une conservation des paramètres de personnalisation des vues dans la base de registre, qui n’est pas affectée par l’utilisation de /clearcache.

Pour réinitialiser complètement la console, une seule solution : fermez la console, lancez regedit et supprimez la clé  HKCU\Software\Microsoft\Microsoft Operations Manager\3.0\Console.

Relancez alors la console (tant qu’à faire en mode /clearcache, pour être sûr) et normalement tout sera rentré dans l’ordre… du moins pour votre profil et sur le poste où vous avez effectué le nettoyage.

Si d’autres utilisateurs/d’autres postes sont impactés, il faudra reproduire la manipulation !

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

Supervision – Script de corrélation a la seconde

 

Une demande m’a été faite récemment pour la détection de l’occurrence de deux events distinct a la même seconde, ce cas particulier traduisant un problème de sécurité spécifique.

Indépendamment de l’objectif final, il s’agit d’un cas intéressant auquel le script ci-dessous a répondu. Il contient des éléments propre a l’api scom mais peux bien sur être adapté pour être utilisé indépendamment.

 

 

  ##############################################################
### SCRIPT TO DETECT SPECIFIC TWO EVENTS OCCURING AT SAME TIME #####
##############################################################

# PARAMETERS:
### $EventLog: Event Log to look in
### $EventSource: Event Source to search for 

### $FirstEventId: First event to correlate

### $SecondEventId: second event to correlate

### $LastMinutes: Last Time Window to search in
### $DayOfWeekToExclude: Day Of Week To Exclude (Example: "('Saturday','Sunday')" )




param(
$Arguments,
$EventLog,
$EventSource,
$FirstEventId,
$SecondEventId,
$LastMinutes,
$DayOfWeekToExclude
)

$ScriptName = "CorrelateTwoSpecEvent.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 of script execution 
NewEventSource
 
write-eventlog -logname "Operations Manager" -Source $ScriptName -EventID 1000 -Message "Execution du script $ScriptName" -EntryType Information
 


# Create local variables from override value

.([Scriptblock]::Create($Arguments))

# Determine the moment in the week
if ((Get-date).DayOfWeek -in $DayOfWeekToExclude)
{
# If the day is in $DayOfWeekToExclude -> NO ACTION - END OF SCRIPT
Write-Host "$((Get-date).DayOfWeek) : NO ACTION - END OF SCRIPT"
Exit 0

}

# Create the Scom property bag
$ScomAPI = New-Object -comObject "MOM.ScriptAPI"
$PropertyBag = $ScomAPI.CreatePropertyBag()



$Message =     "SEARCH CRITERIAS: Log: $EventLog - Source: $EventSource - EventId: $FirstEventId or $SecondEventId `n"
$Message


try
{
New-Variable -Name "$($FirstEventId)_Events" -Force -Value $(Get-WinEvent -ErrorAction SilentlyContinue -FilterHashtable @{logname=$EventLog;ProviderName=$EventSource;id=$FirstEventId;StartTime=$(get-date).AddMinutes(-$LastMinutes)})
New-Variable -Name "$($SecondEventId)_Events" -Force -Value $(Get-WinEvent -ErrorAction SilentlyContinue -FilterHashtable @{logname=$EventLog;ProviderName=$EventSource;id=$SecondEventId;StartTime=$(get-date).AddMinutes(-$LastMinutes)})
}
catch
{
$Message = "Error during retrieve of events in the $ScriptName script"
$Message

NewEventSource
Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1001 -EntryType Warning -Message "$Message"
Exit 1

}

#If no one of the two events id have occurence no need to continue
if (!$(Get-Variable "$($FirstEventId)_Events").Value -and !$(Get-Variable "$($SecondEventId)_Events").Value)
   
{
   
$Message =  "No one of the two events id have occurences in last $LastMinutes minutes - END OF SCRIPT"
   
$Message

   
Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1002 -EntryType Information -Message "$Message"

   
Exit 0

   
}


#If Only one of the two events id have occurences no need to continue
if (!$(Get-Variable "$($FirstEventId)_Events").Value -or !$(Get-Variable "$($SecondEventId)_Events").Value)
   
{
   
$Message = "Only one of the two events id have occurences - END OF SCRIPT"
   
$Message

   
Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1003 -EntryType Information -Message $Message

   
Exit 0
   
}



$Message = "$($(Get-Variable "$($FirstEventId)_Events").Value.count) occurence of event $FirstEventId and $($(Get-Variable "$($SecondEventId)_Events").Value.count) occurence of event $SecondEventId in the last $LastMinutes minutes"
$Message +=
"`nSTART OF COMPARAISON...`n"
#$Message



#Compare DateTimes at second level
try
{
$CompareResult = Compare-Object -ReferenceObject $(Get-Variable -Name "$($FirstEventId)_Events").Value.timecreated.second -DifferenceObject $(Get-Variable -Name "$($SecondEventId)_Events").Value.timecreated.second -ExcludeDifferent -IncludeEqual -Verbose
}
catch
{
$Message += "Error during comparaison of Date Creation"
$Message

NewEventSource
Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1004 -EntryType Warning -Message $Message
Exit 1
}

#If $CompareResult is null, Events have not occureat the same time
If (!($CompareResult))
   
{
   
$Message += "Events $FirstEventId and $SecondEventId have not occured at the same second - No correlation"
   
$Message

   
NewEventSource   
   
Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1004 -EntryType Information -Message $Message
   
   
exit 0

   
}

Else
   
{
   
NewEventSource
   
#
   
$Message += "EVENT $FirstEventId and $SecondEventId have occured at same second $($CompareResult.count) times `n"
   
$Message +=
"`nEVENTS OF LAST $LastMinutes MINUTES:`n"
       
   
$Message += $(Get-Variable "$($FirstEventId)_Events").value | foreach {$_} | Out-String
 
   
$Message += $(Get-Variable "$($SecondEventId)_Events").value | foreach {$_} | Out-String

   
$Message
   
Write-EventLog -LogName "operations manager" -Source $ScriptName -EventId 1005 -EntryType Information -Message $Message
   
$PropertyBag.AddValue("State","CRITICAL")
   
$PropertyBag.AddValue("Message",$Message)
   
   
$PropertyBag
   
}