PI Services

Le blog des collaborateurs de PI Services

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

SCOM – Requête SQL des Heartbeat Failure par Primary Management Server

 

La requête ci-dessous permet de lister depuis la base OperationsManager, avec un paramètre J-n, les alertes Heartbeat Failure des agents, par Primary Server, en affichant la durée de l’alerte, son eventuel ticket associé, son status et le status du maintenance mode de l’objet.

 

 

/****** HEARTBEAT FAILURE FOR AGENTS PRIMARY MANAGED BY SPECIFIC MS WITH DURATION, STATUS AND OBJECT MAINTENANCE MODE STATUS ******/

Use OperationsManager

DECLARE @PrimaryServer	VARCHAR(50)
DECLARE @startdate	datetime
DECLARE @Offset	int
DECLARE @enddate	datetime
DECLARE @seconds int
DECLARE @AlertPattern VARCHAR(50)
DECLARE @TicketPattern VARCHAR(50)

SET @PrimaryServer = 'MyPrimaryMS' – Provide a name of Primary if you need to filter on.
SET @enddate = GETDATE()
SET @Offset = 4 – Provide number of days to look back (limited to operational db retention
SET @startdate = DATEADD(day,-@Offset,@enddate)
SET @AlertPattern = 'heartbeat'
SET @TicketPattern = 'Ticket_Number'	-- Provide string pattern that must be found in each ticket


;

WITH 
PrimaryRelation (SourceEntityId,agent,PrimaryServer,TargetEntityId)
AS
(
SELECT R.SourceEntityID,SourceBME.DisplayName as Agent,TargetBME.DisplayName as PrimaryServer, R.TargetEntityID
FROM Relationship R WITH (NOLOCK) 
JOIN BaseManagedEntity SourceBME 
ON R.SourceEntityID = SourceBME.BaseManagedEntityID 
JOIN BaseManagedEntity TargetBME 
ON R.TargetEntityID = TargetBME.BaseManagedEntityID 
WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication() 
AND TargetBME.IsDeleted <> '1'  -- AND THE PRIMARY SERVER IS NOT WAITING TO BE DELETED
AND SourceBME.IsDeleted <> '1' -- AND THE AGENT IS NOT WAITING TO BE DELETED
)


-- Get alert

SELECT
PrimaryServer 
,AlertStringName
,MonitoringObjectDisplayName AS Computer
,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)) as DownDateTime
,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeResolved)) as RestoredDateTime

,Duration = (
	SELECT CONVERT(varchar, (DateDiff (s, (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)), (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeResolved)))) / 86400 ) + ' days ' + 
	CONVERT(varchar, (DateDiff (s, (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)), (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeResolved)))) % 86400 / 3600) + ' hours ' +
	CONVERT(varchar, (DateDiff (s, (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)), (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeResolved)))) % 86400 % 3600 / 60) + ' min ' +
	CONVERT(varchar, (DateDiff (s, (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)), (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeResolved)))) % 86400 % 3600 % 60 % 60) + ' sec ' 
	
	)

,TicketID = CASE
	WHEN TicketID is null THEN 'NO_TICKET'
	WHEN TicketId not like '%'+@TicketPattern+'%' THEN 'NO_TICKET'
	ELSE TicketID
	END
,Alert_ResolutionState = CASE
	WHEN AV.ResolutionState = '255' THEN 'Closed'
	WHEN AV.ResolutionState = '254' THEN 'Resolved'
	WHEN AV.ResolutionState = '251' THEN 'Blackout'
	WHEN AV.ResolutionState = '250' THEN 'Scheduled'
	WHEN AV.ResolutionState = '249' THEN 'Acknowledge'
	WHEN AV.ResolutionState = '248' THEN 'Assigned to Engineering'
	WHEN AV.ResolutionState = '247' THEN 'Awaiting Evidence'
	WHEN AV.ResolutionState = '3' THEN 'Notified'
	WHEN AV.ResolutionState = '0' THEN 'New'
	END
,'Object_was_InMM' = CASE
	WHEN av.MonitoringObjectInMaintenanceMode = '0' THEN 'NO'
	WHEN av.MonitoringObjectInMaintenanceMode = '1' THEN 'YES'
	END
FROM [dbo].[AlertView] av
INNER JOIN [OperationsManager].[dbo].[MTV_HealthService] as MTV_HS on MTV_HS.DisplayName = MonitoringObjectDisplayName
INNER JOIN PrimaryRelation on PrimaryRelation.SourceEntityId = MTV_HS.BaseManagedEntityId
WHERE name like '%'+@AlertPattern+'%'
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)) between DATEADD(day,-@Offset,@enddate) and @enddate
--AND PrimaryServer like '%'+@PrimaryServer+'%' -- Uncomment to see agents managed from specific primary server

SCOM – Requête SQL des agents gérés par une gateway spécifique

La requête SQL ci-dessous affiche les agents gérés par une Gateway spécifique ainsi que le failover configuré ou non.

Use OperationsManager
 
DECLARE @PrimaryServer VARCHAR(50)
SET @PrimaryServer = 'MyGateway'
 
;
 
WITH 
PrimaryRelation (SourceEntityId,agent,PrimaryServer,TargetEntityId)
AS
(
SELECT R.SourceEntityID,SourceBME.DisplayName as Agent,TargetBME.DisplayName as PrimaryServer, R.TargetEntityID
FROM Relationship R WITH (NOLOCK) 
JOIN BaseManagedEntity SourceBME 
ON R.SourceEntityID = SourceBME.BaseManagedEntityID 
JOIN BaseManagedEntity TargetBME 
ON R.TargetEntityID = TargetBME.BaseManagedEntityID 
WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication() 
AND TargetBME.IsDeleted <> '1'  -- AND THE PRIMARY SERVER IS NOT WAITING TO BE DELETED
AND SourceBME.IsDeleted <> '1' -- AND THE AGENT IS NOT WAITING TO BE DELETED
)
,
FailoverRelation (SourceEntityId,agent,FailoverServer,TargetEntityId)
AS
(
SELECT R.SourceEntityID,SourceBME.DisplayName as Agent,TargetBME.DisplayName as FailoverServer, R.TargetEntityID
FROM Relationship R WITH (NOLOCK) 
JOIN BaseManagedEntity SourceBME 
ON R.SourceEntityID = SourceBME.BaseManagedEntityID 
JOIN BaseManagedEntity TargetBME 
ON R.TargetEntityID = TargetBME.BaseManagedEntityID 
WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceSecondaryCommunication()
AND TargetBME.IsDeleted <> '1'  -- AND THE FAILOVER SERVER IS NOT WAITING TO BE DELETED
AND SourceBME.IsDeleted <> '1' -- AND THE AGENT IS NOT WAITING TO BE DELETED 
)
 
 
SELECT 
       MTV_HS.[DisplayName] as Agent
       
      ,PrimaryRelation.PrimaryServer
      ,FailoverServer =  -- FIELD RELATED TO 'LEFT OUTER JOIN' ON FailoverRelation TABLE
        CASE
        WHEN FailoverRelation.FailoverServer IS NULL THEN 'NO FAILOVER'
        ELSE FailoverRelation.FailoverServer
        END
      ,[Port]
      ,[InstallTime]
      ,[MaximumQueueSize]
      ,Patch = CASE 
        WHEN [PatchList] like '%UR4%' THEN 'RU4'
        WHEN [PatchList] like '%UR8%' THEN 'RU8'
        WHEN [PatchList] like '%UR11%' THEN 'RU11'
        WHEN [PatchList] = '' THEN '[NO_DATA]'
        END
      ,[IsManuallyInstalled]
      ,[Version]
      ,[ActionAccountIdentity]
      ,[ProxyingEnabled]
      ,[HeartbeatInterval]
      ,[NumberOfMissingHeartBeatsToMarkMachineDown_27AD2E30_EFE0_1A73_8C9D_F0A22B073227] as NumberOfMissingHeartBeatsToMarkMachineDown
      ,MTV_OS.DisplayName as OS
      ,MTV_OS.LogicalProcessors_5CAE4847_F75B_01D0_156E_1658D557B739 as Logic_CPU
      ,MTV_OS.CSDVersion_AFE62B62_74FC_2F06_D8A0_DEE31F14CD33 as ServicePack
      
      
  FROM [OperationsManager].[dbo].[MTV_HealthService] as MTV_HS
  INNER JOIN  [dbo].[MTV_Microsoft$Windows$OperatingSystem] as MTV_OS on MTV_HS.DisplayName = MTV_OS.PrincipalName
 
  INNER JOIN PrimaryRelation on PrimaryRelation.SourceEntityId = MTV_HS.BaseManagedEntityId
  -- THE LEFT OUTER JOIN CAN BE COMMENTED (AND RELATED FIELDS) TO DISPLAY ONLY THE PRIMARY SERVER)
  LEFT OUTER JOIN FailoverRelation on FailoverRelation.SourceEntityId = MTV_HS.BaseManagedEntityId
 
  WHERE IsAgent = '1'
  AND PrimaryServer like '%'+@PrimaryServer+'%'
 
 
  order by MTV_HS.[DisplayName]

SCOM – SQL – Trois requêtes d’inventaires des overrides par type d’objet.

Un peu a la manière du précédent post sur l’inventaire des overrides, les trois requêtes suivantes permettent de lister les overrides selon les types d’objet “Rule”, “Monitor” et “Discovery”.

 

 

/* All Overrides for Rules of language code EUN and FRA, with Original MP and Rule, Parameter Name, Override Value, Scope and Containing MP */ Use OperationsManager SELECT mpv2.DisplayName as Rule_MP_Name ,rv.DisplayName as Rule_Name ,IsEnabledByDefault = CASE WHEN rv.Enabled = '0' THEN 'NO' WHEN rv.Enabled <> '0' THEN 'YES' END ,op.OverrideableParameterName as Overrideable_Parameter ,mo.Value as Override_Value ,IsEnforced = CASE WHEN mo.Enforced = '0' THEN 'NO' WHEN mo.Enforced = '1' THEN 'YES' END ,mt.TypeName as Override_Scope ,bme.DisplayName as Override_InstanceName ,bme.Path as Override_InstancePath ,mpv.DisplayName as Override_MP_Name FROM ModuleOverride mo INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId INNER JOIN ruleview rv on rv.Id = mo.ParentId INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId INNER JOIN managementpackview mpv2 on mpv2.Id = rv.ManagementPackId LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext WHERE mpv.Sealed = 0 AND rv.LanguageCode in ('ENU','FRA') AND mpv.LanguageCode in ('ENU','FRA') AND mpv2.LanguageCode in ('ENU','FRA') --ORDER BY mpv2.DisplayName

 

/*All Overrides for Monitor of language code EUN and FRA, with Original MP and Monitor, Parameter Name, Override Value, Scope and Containing MP */ Use OperationsManager SELECT mpv2.DisplayName as Monitor_MP_Name ,mv.DisplayName as Monitor_Name ,IsEnabledByDefault = CASE WHEN mv.Enabled = '0' THEN 'NO' WHEN mv.Enabled <> '0' THEN 'YES' END ,op.OverrideableParameterName as Overrideable_Parameter ,mo.Value as Override_Value ,IsEnforced = CASE WHEN mo.Enforced = '0' THEN 'NO' WHEN mo.Enforced = '1' THEN 'YES' END ,mt.TypeName as Override_Scope ,bme.DisplayName as Override_InstanceName ,bme.Path as Override_InstancePath ,mpv.DisplayName as Override_MP_Name FROM MonitorOverride mo INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId INNER JOIN monitorview mv on mv.Id = mo.MonitorId INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId INNER JOIN managementpackview mpv2 on mpv2.Id = mv.ManagementPackId LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext WHERE mpv.Sealed = 0 AND mv.LanguageCode in ('ENU','FRA') AND mpv.LanguageCode in ('ENU','FRA') AND mpv2.LanguageCode in ('ENU','FRA') ORDER BY mpv2.DisplayName

 

-- All Overrides for Discoveries of language code EUN and FRA, with Original MP and Discovery, Parameter Name, Override Value, Scope and Containing MP */ Use OperationsManager SELECT mpv2.DisplayName as Discovery_MP_Name ,dv.DisplayName as Discovery_Name ,mo.ParentType ,IsEnabledByDefault = CASE WHEN dv.Enabled = '0' THEN 'NO' WHEN dv.Enabled <> '0' THEN 'YES' END ,op.OverrideableParameterName as Overrideable_Parameter ,mo.Value as Override_Value ,IsEnforced = CASE WHEN mo.Enforced = '0' THEN 'NO' WHEN mo.Enforced = '1' THEN 'YES' END ,mt.TypeName as Override_Scope ,bme.DisplayName as Override_InstanceName ,bme.Path as Override_InstancePath ,mpv.DisplayName as Override_MP_Name FROM ModuleOverride mo INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId INNER JOIN DiscoveryView dv on dv.Id = mo.ParentId INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId INNER JOIN managementpackview mpv2 on mpv2.Id = dv.ManagementPackId LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext WHERE mpv.Sealed = 0 AND dv.LanguageCode in ('ENU','FRA') AND mpv.LanguageCode in ('ENU','FRA') AND mpv2.LanguageCode in ('ENU','FRA') AND mo.ParentType = 'Discovery' ORDER BY mpv2.DisplayName

SCOM - Requête SQL pour afficher toutes les règles ayant au moins un override ou aucun

 

 

-- QUERY TO DISPLAY ALL RULES WITH TARGET CLASSES AND THEIR OVERRIDE STATE (ANYWAY MORE THAN 1 OVERRIDE PER RULE) - (FILTERED ON 'ENU' AND 'FRA' LANGUAGES) -- THE FINAL 'GROUP BY' CLAUSE IS TO DISPLAY ONLY ONE RULE OCCURENCE (WE ONLY WANT TO KNOW THAT RULE IS OVERRIDEN AT LEAST ONE TIME. -- THAT IS EQUIVALENT TO DELETE THE 'ALL' STATEMENT IN UNION CLAUSE. PRINT 'ALL RULES THAT IS OVERRIDEN AT LEAST 1 TIME OR NOT'; WITH TEMP1 (Rule_Name,Target_Class,Override_State) AS ( SELECT rv.DisplayName as Rule_Name ,MTV.DisplayName as Target_Class ,Override_State = CASE WHEN mo.ParentId is null THEN 'NOT OVERRIDEN' WHEN mo.ParentId is not null THEN 'OVERRIDEN' END FROM RuleView rv FULL JOIN ModuleOverride mo on rv.Id = mo.ParentId INNER JOIN [dbo].[ManagedTypeView] MTV on rv.TargetMonitoringClassId = MTV.Id WHERE rv.Id not in (select mo.ParentId from ModuleOverride mo) AND rv.LanguageCode in ('ENU','FRA') AND MTV.LanguageCode in ('ENU','FRA') UNION ALL SELECT rv.DisplayName as Rule_name ,MTV.DisplayName as Target_Class ,Override_State = CASE WHEN mo.ParentId is null THEN 'NOT OVERRIDEN' WHEN mo.ParentId is not null THEN 'OVERRIDEN' END FROM RuleView rv FULL JOIN ModuleOverride mo on rv.Id = mo.ParentId JOIN [dbo].[ManagedTypeView] MTV on rv.TargetMonitoringClassId = MTV.Id WHERE rv.Id in (select mo.ParentId from ModuleOverride mo) AND rv.LanguageCode in ('ENU','FRA') AND MTV.LanguageCode in ('ENU','FRA') ) SELECT Rule_Name ,Target_Class ,Override_State FROM TEMP1 GROUP BY Rule_Name,Target_Class,Override_State ORDER BY Rule_Name

SCOM – Script Mode Maintenance des Health Service Watcher

Lors du basculement d’agents gérés par une gateway vers une gateway de secours (failover), il peut arriver que quelques alertes “Heartbeat Failure” indésirables soit générées.

Le script suivant propose de lister, démarrer  ou arrêter les modes maintenance des instances Health Service Watcher des agents gérés par une gateway/management server donné.

Cette action est bien entendu possible pour une autre classe, dans la mesure ou le DisplayName  de l’instance correspond a celui de l’agent.

 

 

########################################################################################################################################### ## SCRIPT TO START/STOP/DISPLAY MAINTENANCE MODE FOR HEALTH SERVICE WATCHER OBJECTS THAT DEPENDS FROM SPECIFIC MANAGEMENT SERVER OR GATEWAY ## Author: CJOURDAN ########################################################################################################################################### #Parameters # $MGroup : Management Group to connect to # $MStoConnect : Management server to connect to # $PrimMS : Management server or Gateway FQDN from which depend agents. # $Reason : Reason for Maintenance Mode (Valid Reason are: PlannedOther # UnPlannedOther # PlannedHardwareMaintenance # UnplannedHardwareMaintenance # PlannedHardwareInstallation # UnplannedHardwareInstallation # PlannedOperatingSystemReconfiguration # UnplannedOperatingSystemReconfiguration # PlannedApplicationMaintenance # ApplicationInstallation # ApplicationUnresponsive # ApplicationUnstable # SecurityIssue # LossOfNetworkConnectivity) # # $Comment : Comment for Maintenance Mode # $MMAction : START or STOP OR DISPLAY Maintenance Mode # $MMDuration : Maintenance Mode Duration in minutes (only valuable for START $MMAction) # EXAMPLES: # Display Health Service Watcher Maintenance Mode Status for agents primary managed by MyPriMaryMS.mydomain.home # .\ScomSwitchMM.ps1 -MGroup MyMGROUP -MStoConnect MyMS.mydomain.home -PrimMS MyPriMaryMS.mydomain.home -MMAction display # Start Health Service Watcher Maintenance Mode for agents primary managed by MyPriMaryMS.mydomain.home during 60 min # .\ScomSwitchMM.ps1 -MGroup MyMGROUP -MStoConnect MyMS.mydomain.home -PrimMS MyPriMaryMS.mydomain.home -MMAction start -MMDuration 60 Param( [Parameter(Mandatory=$false)] $ClassName = "Health Service Watcher", [Parameter(Mandatory=$false)] $MGroup = "MyMGROUP", [Parameter(Mandatory=$false)] $MStoConnect= "MyMS.mydomain.home", [Parameter(Mandatory=$true)] $PrimMS= "MyPriMaryMS.mydomain.home", [Parameter(Mandatory=$false)] $Reason = "PlannedOther", [Parameter(Mandatory=$false)] $Comment = "", [Parameter(Mandatory=$true)] $MMAction = $(Read-Host -Prompt "PROVIDE TYPE OF MAINTENANCE MODE ACTION: START or STOP OR DISPLAY"), [Parameter(Mandatory=$false)] # $MMDuration in minutes $MMDuration = 0 ) if ($MMAction -notmatch ".*STOP.*|.*START.*|.*DISPLAY.*") { write-host -ForegroundColor Yellow "YOU MUST PROVIDE VALID MAINTENANCE MODE ACTION TYPE: START or STOP or DISPLAY" EXIT 1 } $cred = $(Get-Credential -Credential "MyDomain\") #Import du module SCOM try { Import-Module -Name OperationsManager -ErrorAction stop } catch { write-host -ForegroundColor red "Error during import of SCOM PS Module" EXIT 1 } #Connection au management group $MGroup try { New-SCOMManagementGroupConnection -ComputerName $MStoConnect -Credential $cred } catch { write-host -ForegroundColor red "Error during connection to MS $MStoConnect - CHECK CREDENTIALS USED" EXIT 1 } # Get Scom agents that have $PrimMS as Primary Management Server $agents = get-scomagent | where {$_.PrimaryManagementServerName -eq $PrimMS} if (!($agents)) { write-host "NO SCOM AGENTS FOUND FOR SERVER $PrimMS - CHECK THAT THE NAME IS CORRECT (YOU MUST PROVIDE FQDN) OR THAT THIS SERVER BELONG TO $MGroup MANAGEMENT GROUP" EXIT 1 } # Get "Health Service Watcher" Class Instances $class = Get-SCOMClass -DisplayName $ClassName switch($MMAction) { START { # Get instances of "Health Service Watcher" Class $ClassInstances = Get-SCOMClassInstance -Class $class # Class to put in MM Table $ClassInstToMM = @() # Get class instances that match agents foreach ($agent in $agents) { $ClassInstToMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname}) } $starttime = $(get-date).ToUniversalTime() $endtime = $(get-date).AddMinutes($MMDuration).ToUniversalTime() # PUT IN Maintenance Mode $($ClassInstToMM | foreach {$_.schedulemaintenancemode($starttime,$endtime,$Reason,$Comment)}) # Display effective instances in MM write-host "EFFECTIVE INSTANCES OF CLASS '$ClassName' IN MAINTENANCE MODE (Primary Managed by $PrimMS):" # Get instances of "Health Service Watcher" Class $ClassInstances = Get-SCOMClassInstance -Class $class $ClassInstInMM = @() # Get class instances that match agents foreach ($agent in $agents) { $ClassInstInMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname}) } $ClassInstInMM } STOP { # Get instances of "Health Service Watcher" Class $ClassInstances = Get-SCOMClassInstance -Class $class # Class to put out of MM Table $ClassInstOoMM = @() # Get class instances that match agents foreach ($agent in $agents) { $ClassInstOoMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname}) } # Only Endttime in necessary and it is now $endtime = $(get-date).ToUniversalTime() # PUT IN Maintenance Mode $($ClassInstOoMM | foreach {$_.stopmaintenancemode($endtime)}) # Display effective instances in MM write-host "EFFECTIVE INSTANCES OF CLASS '$ClassName' OUT OF MAINTENANCE MODE (Primary Managed by $PrimMS):" # Get instances of "Health Service Watcher" Class $ClassInstances = Get-SCOMClassInstance -Class $class $ClassInstOoMM = @() # Get class instances that match agents foreach ($agent in $agents) { $ClassInstOoMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname}) } $ClassInstOoMM } DISPLAY { # Display effective instances in MM write-host "`n INSTANCES OF CLASS '$ClassName' MAINTENANCE MODE STATE (Primary Managed by $PrimMS):" # Get instances of "Health Service Watcher" Class $ClassInstances = Get-SCOMClassInstance -Class $class $ClassInstMM = @() # Get class instances that match agents foreach ($agent in $agents) { $ClassInstMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname}) } $ClassInstMM } }

SCOM – SQL – Requête d’inventaire des overrides

La requête ci-dessous permet de lister tout les overrides de manière ‘clarifiés’ afin d’identifier les objets a la source de ces overrides, les paramètres concernés et les management pack sources et de destination.

Les paramètres @Startdate et @EndDate permettent de spécifier une fenêtre de temps concernant les dates de création et de dernière modification des overrides.

 

 

declare @StartDate  datetime declare @EndDate    datetime set @EndDate = GETDATE() set @StartDate = DATEADD(day,-60,@EndDate)   SELECT DISTINCT MP.MPFriendlyName AS 'Management Pack' , aov.ParentType AS 'Type' ,(CASE        WHEN AOV.OverrideType = 'RuleProperty' THEN R.RuleName        WHEN AOV.OverrideType = 'MonitorProperty' THEN M.MonitorName        WHEN AOV.OverrideType = 'RuleConfiguration' THEN R.RuleName        WHEN AOV.OverrideType = 'MonitorConfiguration' THEN M.MonitorName  END) AS 'NameType', mt.DisplayName AS ContextDisplayName , aov.OverrideableParameterName , aov.Value, (CASE Enforced        WHEN '0' THEN 'False'        WHEN '1' THEN 'True'   END) AS 'Enforced'   , aov.LastModified   , aov.TimeAdded   ,(CASE   WHEN AOV.OverrideType = 'RuleProperty' THEN              (CASE R.RuleEnabled              WHEN '0' THEN 'False'              WHEN '2' THEN 'True'              WHEN '3' THEN 'True'              WHEN '4' THEN 'True'              END)     WHEN AOV.OverrideType = 'MonitorProperty' THEN              (CASE M.MonitorEnabled              WHEN '0' THEN 'False'              WHEN '2' THEN 'True'              WHEN '3' THEN 'True'              WHEN '4' THEN 'True'              END)     WHEN AOV.OverrideType = 'RuleConfiguration' THEN              (CASE R.RuleEnabled              WHEN '0' THEN 'False'              WHEN '2' THEN 'True'              WHEN '3' THEN 'True'              WHEN '4' THEN 'True'              END)                WHEN AOV.OverrideType = 'MonitorConfiguration' THEN              (CASE M.MonitorEnabled              WHEN '0' THEN 'False'              WHEN '2' THEN 'True'              WHEN '3' THEN 'True'              WHEN '4' THEN 'True'              END) END) AS 'Enable_by_default', MPSTORE.MPFriendlyName AS 'MP Stored'   FROM AllOverrideView AS aov LEFT OUTER JOIN Rules AS R WITH (nolock) ON aov.TargetId = R.RuleId AND (aov.OverrideType = 'RuleProperty' OR aov.OverrideType = 'RuleConfiguration') LEFT OUTER JOIN Monitor AS M WITH (nolock) ON aov.TargetId = M.MonitorId AND (aov.OverrideType = 'MonitorProperty' OR aov.OverrideType = 'MonitorConfiguration') LEFT OUTER JOIN  ManagementPack AS MP WITH (nolock) ON (CASE                                                                                               WHEN AOV.OverrideType = 'RuleProperty' THEN R.ManagementPackId                                                                                               WHEN AOV.OverrideType = 'RuleConfiguration' THEN R.ManagementPackId                                                                                               WHEN AOV.OverrideType = 'MonitorProperty' THEN M.ManagementPackId                                                                                               WHEN AOV.OverrideType = 'MonitorConfiguration' THEN M.ManagementPackId                                                                                               END) = MP.ManagementPackId INNER JOIN ManagementPack AS MPSTORE WITH (nolock) ON MPSTORE.ManagementPackId = aov.ManagementPackId LEFT OUTER JOIN ManagedTypeView AS mt WITH (nolock) ON mt.Id = aov.ContextId LEFT OUTER JOIN ManagedTypeView AS mtv WITH (nolock) ON mtv.Id = aov.ContextObjectId   WHERE (MP.MPFriendlyName IS NOT NULL) AND (aov.LastModified BETWEEN @StartDate AND @EndDate) OR (MP.MPFriendlyName IS NOT NULL) AND (aov.TimeAdded BETWEEN @StartDate AND @EndDate)   ORDER BY aov.TimeAdded DESC