PI Services

Le blog des collaborateurs de PI Services

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
}


[Powershell] Excel ne s'enregistre pas via le Planificateur de tâches

Lancer un script Powershell via le planificateur de tâches ne pose aucun problème, en revanche si ce dernier souhaite utiliser Excel (par l'intermédiaire de la commande "new-object -comobject excel.application") on peut rencontrer un problème.

Je dis "on peut" car cela fonctionne si l'on a pas coché la case "Run Whether user is logged on or not", en revanche si cette dernière est cochée, Excel ne sera pas en mesure d'enregistrer le fichier.

Pourquoi ?

Par défaut l'application Excel s'exécute avec le compte "Utilisateur exécutant (The Launching User)", mais si j'utilise une tâche planifiée qui ne nécessite pas que je sois connecté Excel ne parvient pas a déterminer qui fait appel à lui.

Que Faire ?

Simplement en déclarant qui  exécute l'application Excel via la MMC -> Services des composants.

Je vous renvoie au point 2 (Gestion de l'Excel distant) de mon précédent article : [Powershell] Ecrire dans un Excel distant.

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
   
}


 
 

Powershell : Limite de requête Active Directory via ADWS

Problème

Lors de l'utilisation d'un script qui génère plusieurs requêtes depuis plusieurs serveurs en simultané afin d'alimenter une banque de données, je me suis heurté au message d'erreur suivant :

get-adcomputer : A connection to the directory on which to process the request was unavailable. This is likely a 
transient condition.
At C:\temp\Fusion.ps1:97 char:1
+ get-adcomputer -Filter {DNSHostName -eq $FullName} -Properties OperatingSystem | Select-Object -Property N ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-ADComputer], ADException
    + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.GetADComputer

Explication

Cette erreur est liée aux valeurs par défaut du service "Active Directory Web Services" définies dans le fichier de configuration sur les DC sous : 

C:\Windows\ADWS\Microsoft.ActiveDirectory.WebServices.exe.config

En effet si vous éditez le fichier de configuration vous pourrez voir que par défaut le maximum de connexions LDAP par utilisateur est de 5.

<add key="MaxConnectionsPerUser" value="5" />

Ce qui signifie que je ne peux exécuter plus de 5 connexions en simultanée via Active Directory Web Services avec les même identifiants.

Il est possible de modifier cette valeur, mais cette action n'est pas recommandée car elle peut avoir un impact sur les performances de l'AD.

Si toutefois vous souhaitez la modifier, certaines précautions sont à prendre; il est clairement précisé dans le fichier de configuration que la valeur de "MaxConnectionsPerUser" ne peut pas être plus grande que la valeur de "MaxPoolConnections", il faudra donc aussi prendre en compte la valeur suivante :

<add key="MaxPoolConnections" value="10" />

Cette dernière spécifie que le nombre maximal de connexions LDAP, pour chaque instance de service d'annuaire prise en charge par le service ADWS s'exécutant sur un serveur.

Il peut être aussi intéressant de regarder la valeur de la clé ci-dessous : 

<add key="MaxPercentageReservedConnections" value="50" />

Celle-ci permet, de spécifier le pourcentage maximal de connexions LDAP pouvant être utilisées pour effectuer des requêtes pour chaque instance de service d'annuaire prise en charge par le service ADWS sur un serveur.

 

Attention :

Même s'il est possible de modifier les paramètres ci-dessus, Microsoft ne le préconise pas (cf: note ci-dessous) :

Plusieurs paramètres de configuration du service ADWS affectent la limitation de la bande passante sur un serveur Windows Server 2008 R2 sur lequel le service ADWS est en cours d'exécution.
Nous recommandons aux administrateurs de modifier les valeurs par défaut des seuls paramètres suivants:
MaxConcurrentCalls, MaxConcurrentSessions, MaxReceivedMessageSize et MaxStringContentLength.

 

Compléments d'informations:

https://technet.microsoft.com/en-us/library/373e68b3-abfc-4da4-ae89-72a15cfc7543

SCOM – Script Exemple de rapport Cmdline pour un monitor

Le script ci-dessous montre comment facilement sortir de petit rapport en ligne de commande de l’état d’un monitor pour tout les agents.

Dans cet exemple on affiche l’état du rollup monitor de l’espace disque (FreeSpaceMonitorRollup) et l’information de maintenance mode.

 

Function GetLogicDiskFreeSpState 
     
{

     
param(
     
[string]$dataSource = "SQLSERV1\OPSMGR",
     
[string]$database = "OperationsManager",
     
[string]$UnitMonitor = "Microsoft.Windows.Server.%.LogicalDisk.FreeSpaceMonitorRollup",
     
[string]$TypeName = "Microsoft.Windows.Server.%.LogicalDisk",
     
[string]$sqlCommand = 
     
$(
"
                                                             
      /* QUERY THAT GET STATE OF SPECIFIC MONITOR STATE FOR ALL COMPUTERS */
      Use $database
                                                                                    
      DECLARE @UnitMonitor VARCHAR(100)
      DECLARE @TypeName VARCHAR(100)
                                           
                       
      SET @UnitMonitor = '%'+'$UnitMonitor'+'%'
      SET @TypeName = '$TypeName'
                                          
      PRINT 'MONITOR: ' + @UnitMonitor
      PRINT 'CRITERIAS:'
                                           
     ;
     WITH
                                                       
     MAININFO (Monitor,Computer,Disk,HealthState, LastModified,IsAvailable,InMaintenanceMode)
     AS
     (                                           
      SELECT
      MV.Name as Monitor
     ,MEGV.path as Computer
     ,MEGV.Name as Disk
     ,HealthState = CASE WHEN InMaintenanceMode = '0' OR InMaintenanceMode is null  
        THEN     
            CASE MEGV.IsAvailable
                  WHEN '0' THEN 'KO - STATE IS NOT AVAILABLE' -- THIS MEAN THAT THE STATE IS GRAYED IN SCOM CONSOLE DESPITE OF THE OBJECT IS NOT IN MAINTENANCE MODE (AGENT FUNCTIONNAL PROBLEM)
                  WHEN '1' THEN
                    CASE SV.[HealthState]
                        WHEN '0' THEN 'Not Monitored'
                        WHEN '1' THEN 'OK'
                        WHEN '2' THEN 'Warning'
                        WHEN '3' THEN 'Critical'
                    END
                                                   
                END                   
                                                       
            WHEN InMaintenanceMode = '1'
        THEN
            CASE MEGV.IsAvailable
            WHEN '0' THEN 'KO - STATE IS NOT AVAILABLE' -- THIS MEAN THAT THE STATE IS GRAYED IN SCOM CONSOLE DESPITE OF THE OBJECT IS IN MAINTENANCE MODE (AGENT FUNCTIONNAL PROBLEM)
            WHEN '1' THEN
                CASE SV.[HealthState]
                WHEN '0' THEN 'In Maintenance Mode'
                WHEN '1' THEN 'OK'
                WHEN '2' THEN 'Warning'
                WHEN '3' THEN 'Critical'
                END
                                                       
            END                                                                                           
        END

    ,SV.[LastModified] as LastModified
    ,MEGV.IsAvailable
    ,MEGV.InMaintenanceMode
    FROM [OperationsManager].[dbo].[StateView] SV
    INNER JOIN [dbo].[ManagedEntityGenericView] MEGV on SV.BaseManagedEntityId = MEGV.BaseManagedEntityId
    INNER JOIN [dbo].[MonitorView] MV on SV.MonitorId = MV.id
    INNER JOIN [dbo].[ManagedTypeView] MTV on MEGV.MonitoringClassId = MTV.Id
    WHERE MV.Name like @UnitMonitor
    AND MTV.Name like @TypeName
        )
    SELECT
        
    MAININFO.Monitor
   ,MAININFO.Computer
   ,MAININFO.Disk
   ,MAININFO.HealthState
   ,MAININFO.LastModified
   ,MAININFO.IsAvailable
   ,MAININFO.InMaintenanceMode
    FROM MAININFO
            
   ORDER BY computer,Disk
  "
    
)

 
)

 
$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)
 
$command.CommandTimeout=300
 
$connection.Open()

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

 
$connection.Close()

 
#Display Time of Query
"`n"
 
write-host "Query Date: $(get-date -Format F)" -NoNewline
"`n"

#Display Criterias
Write-Host "STATE OF MONITOR `"$UnitMonitor`":"
"`n"
#Display Nb of rows

write-host Nb Of Object: $($dataset.Tables.defaultview.Count)
"`n"

$dataSet.Tables
}



try
{
GetLogicDiskFreeSpState | ft -AutoSize
}
catch
{
write-host -F Red "ERROR DURING EXECUTION OF GetLogicDiskFreeSpState FUNCTION - CHECK THAT YOU ARE LOGGED WITH A RIGHT ACCOUNT OR THAT THE SQL QUERY IS CORRECT"
}