Type d'analyse de stratégie système en deux états
Microsoft.SQLServer.2014.AlwaysOn.SystemPolicyScriptMonitorType (UnitMonitorType)
Type d'analyse de script en deux états avec la source de données de script Powershell. Utilisé pour analyser les stratégies système.
Element properties: Member Modules:
Overrideable Parameters:
Source Code: <UnitMonitorType ID="Microsoft.SQLServer.2014.AlwaysOn.SystemPolicyScriptMonitorType" RunAs="Microsoft.SQLServer.2014.AlwaysOn.MonitoringAccount" Accessibility="Internal">
<MonitorTypeStates>
<MonitorTypeState ID="HealthState"/>
<MonitorTypeState ID="ErrorState"/>
</MonitorTypeStates>
<Configuration>
<IncludeSchemaTypes>
<SchemaType>Windows!Microsoft.Windows.PowerShellSchema</SchemaType>
</IncludeSchemaTypes>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" minOccurs="1" name="Interval" type="xsd:integer"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" minOccurs="1" name="TimeoutSeconds" type="xsd:integer"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" minOccurs="1" name="PolicyName" type="xsd:string"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" minOccurs="1" name="InstanceName" type="xsd:string"/>
</Configuration>
<OverrideableParameters>
<OverrideableParameter ID="Interval" Selector="$Config/Interval$" ParameterType="int"/>
<OverrideableParameter ID="TimeoutSeconds" Selector="$Config/TimeoutSeconds$" ParameterType="int"/>
<OverrideableParameter ID="PolicyName" Selector="$Config/PolicyName$" ParameterType="string"/>
</OverrideableParameters>
<MonitorImplementation>
<MemberModules>
<DataSource ID="DSa" TypeID="System!System.CommandExecuterPropertyBagSource">
<IntervalSeconds>$Config/Interval$</IntervalSeconds>
<ApplicationName/>
<WorkingDirectory/>
<CommandLine>powershell.exe -NoLogo -NoProfile -Noninteractive "$ep = get-executionpolicy; if ($ep -gt 'RemoteSigned') {set-executionpolicy -Scope Process remotesigned} & '$$file/AvailabilityGroupMonitoring.ps1$$' '$Config/InstanceName$'</CommandLine>
<SecureInput/>
<TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>
<RequireOutput>true</RequireOutput>
<Files>
<File>
<Name>AvailabilityGroupMonitoring.ps1</Name>
<Contents><Script> param($InstanceName)
#TODO: Discuss event id
$SCRIPT_EVENT_ID = 4202
$DEBUG_MODULE = "AvailabilityGroupMonitoring.ps1"
#Event Severity values
$INFORMATION_EVENT_TYPE = 0
$ERROR_EVENT_TYPE = 1
$msgAssembiesError = "Unable to load SQLPS modules"
#
# Prepare MOM API and property bag object
#
$api = New-Object -comObject "MOM.ScriptAPI"
$bag = $api.CreatePropertyBag()
$arAssembies =
@(
"\PowerShell\Modules\SQLPS\Microsoft.AnalysisServices.PowerShell.Provider.dll",
"\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll",
"\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll")
function ImportSQLModules {
param ($arAssembies, [string]$include)
$paths =
@( (get-itemproperty -path "hklm:\Software\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup\" | Select SQLPath).SQLPath ,
(get-itemproperty -path "hklm:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup\" | Select SQLPath).SQLPath )
$flRet = $false
ForEach ($path in $paths){
if( $path.Contains($include) )
{
ForEach ($assemblyName in $arAssembies){
$filename = $path + $assemblyName
#debug $api.LogScriptEvent("check filename: ", $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $filename)
if((Test-Path $filename))
{
Import-Module $filename -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null
#debug $api.LogScriptEvent("filename: ", $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $filename)
$flRet = $true;
}
}
}
}
return $flRet
}
function BuildServerName([String] $pHostName, [String] $pInstanceName){
$namespace = "root\Microsoft\SqlServer\ComputerManagement12"
$class = "ServerNetworkProtocolProperty"
$serverName = $pHostName + "\" + $pInstanceName
$listenAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.PropertyName -eq "ListenOnAllIPs")}
if($listenAll.PropertyNumVal -eq 1)
{
$tcpipAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {
($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq "IPAll")-and($_.PropertyName -eq "TcpPort")}
if($tcpipAll.PropertyStrVal -eq '')
{
$tcpipAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {
($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq "IPAll")-and($_.PropertyName -eq "TcpDynamicPorts")}
}
if($tcpipAll.PropertyStrVal -ne '')
{
$serverName = $serverName + "," + $tcpipAll.PropertyStrVal
}
}
else
{
$ipAddressName = (Get-WmiObject -Namespace $namespace -Class $class | Where-Object {
($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -ne "")-and($_.PropertyName -eq "Enabled") -and ($_.PropertyNumVal -eq 1)}) | select -first 1 | select -ExpandProperty IPAddressName
if($ipAddressName -ne $null)
{
$tcp = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {
($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq $ipAddressName)-and( ($_.PropertyName -eq "TcpPort") -or ($_.PropertyName -eq "TcpDynamicPorts")) -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal
$ip = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {
($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq $ipAddressName)-and( $_.PropertyName -eq "IpAddress") -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal
if($ip -ne $null)
{
$serverName = $ip + "," + $tcp
}
else
{
$serverName = $serverName + "," + $tcp
}
}
}
return $serverName
}
function IsModuleLoaded {
param ([string]$include)
$modules = Get-Module
ForEach ($name in $modules){
if( $name.Name.Contains($include))
{
return $true
}
}
return $false
}
#The function returns service or "Unknown" state
#Input:
# server - compute name
# service - system service name
# InstanceName - sql server instance name
#Output:
# service state or "Unknown" state
function GetServiceState($server, $service, $InstanceName)
{
try {
if ($service -eq "MSSQL") {
$service = "MSSQL`${0}" -f $InstanceName
}
$namespace = "root/cimv2"
$obje = Get-WmiObject -Namespace $namespace -ComputerName $server -Class "win32_service" -ErrorAction SilentlyContinue | where {$_.name -like $service }
if ($obje -ne $null) {
return $obje.State
}
}
catch {
}
return "Unknown"
}
function GetSQLServiceName($InstanceName)
{
if ($InstanceName -eq "MSSQLSERVER")
{
return "MSSQLSERVER"
}
return "MSSQL`$" + $InstanceName
}
$msg = [string]::Empty
try
{
#
# if instance is default then: <machineName>
# if instance is named then: <machineName>\<instanceName>
#
$paths =
@( (get-itemproperty -path "hklm:\Software\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup\" | Select SQLPath).SQLPath ,
(get-itemproperty -path "hklm:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup\" | Select SQLPath).SQLPath )
#debug $api.LogScriptEvent("paths: ", $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $paths)
if ((ImportSQLModules $arAssembies "120\Tools") -eq $false)
{
Import-Module SQLPS -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null
if( ( IsModuleLoaded "SQLPS") -eq $false)
{
throw $msgAssembiesError
}
}
$log = @{}
$pathArray = $InstanceName -split "\\"
$ComputerName = ($pathArray[0] -split "\.")[0]
$InstanceName = "MSSQLSERVER"
if ($pathArray.Length -gt 1)
{
$InstanceName = $pathArray[1]
}
#if service is not in running state when exit without any error
$service = GetSQLServiceName $InstanceName
$state = GetServiceState $ComputerName $service $InstanceName
if(($state -ne "Running") -and ($state -ne "Unknown"))
{
$api.CreatePropertyBag()
return
}
$serverName = BuildServerName $ComputerName $InstanceName
$ServerObj = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)
$ServerObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.AvailabilityGroup], $true)
$ServerObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.AvailabilityReplica], $true)
$ServerObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.DatabaseReplicaState], $true)
$serverObj.AvailabilityGroups | foreach {
# Availability Group health
$availabilityGroup = $_
Test-SqlAvailabilityGroup -NoRefresh -ShowPolicyDetails -InputObject $availabilityGroup | Select Name, Result | foreach {
$agPropertyName = "{0}-{1}" -f $availabilityGroup.Name, $_.Name
$bag.AddValue($agPropertyName, $_.Result)
$log.Add($agPropertyName, $_.Result)
}
# Availability Replica health
$availabilityGroup.AvailabilityReplicas | foreach {
$replicaName = $_.Name
Test-SqlAvailabilityReplica -NoRefresh -ShowPolicyDetails -InputObject $_ | Select Name, Result | foreach {
$arPropertyName = "{0}-{1}-{2}" -f $availabilityGroup.Name, $replicaName, $_.Name
$bag.AddValue($arPropertyName, $_.Result)
$log.Add($arPropertyName, $_.Result)
}
}
# Database Replica State health
$availabilityGroup.DatabaseReplicaStates | foreach {
$drname = "{0}.{1}" -f $_.AvailabilityReplicaServerName, $_.AvailabilityDatabaseName
Test-SqlDatabaseReplicaState -NoRefresh -ShowPolicyDetails -InputObject $_ | Select Name, Result | foreach {
$drPropertyName = "{0}-{1}-{2}" -f $availabilityGroup.Name, $drName, $_.Name
$bag.AddValue($drPropertyName, $_.Result)
$log.Add($drPropertyName, $_.Result)
}
}
}
$log.GetEnumerator() | %{
$msg += "{0} = {1}{2}" -f $_.Name, $_.Value, [Environment]::NewLine
}
#debug $api.LogScriptEvent("Workflow succeeded! ", $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)
}
catch
{
$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE
$msg += "Error occurred during AlwaysOn monitoring.{0}Computer:{1} {0}Reason: {2} {0}Position:{3} {0}Offset:{4}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message, $_.InvocationInfo.ScriptLineNumber, $_.InvocationInfo.OffsetInLine
$msg += "{0}Detailed error output: {1}" -f [Environment]::NewLine, [String]::Join("{0}--------{0}" -f [Environment]::NewLine, $Error.ToArray())
$api.LogScriptEvent($header, $SCRIPT_EVENT_ID, $ERROR_EVENT_TYPE, $msg)
}
$api.Return($bag)
</Script> </Contents>
<Unicode>true</Unicode>
</File>
</Files>
</DataSource>
<ConditionDetection ID="HealthFilter" TypeID="System!System.ExpressionFilter">
<Expression>
<Or>
<Expression>
<Not>
<Expression>
<Exists>
<ValueExpression>
<XPathQuery Type="Boolean">Property[@Name="$Config/PolicyName$"]</XPathQuery>
</ValueExpression>
</Exists>
</Expression>
</Not>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="Boolean">Property[@Name="$Config/PolicyName$"]</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="Boolean">true</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</Or>
</Expression>
</ConditionDetection>
<ConditionDetection ID="ErrorFilter" TypeID="System!System.ExpressionFilter">
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="Boolean">Property[@Name="$Config/PolicyName$"]</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="Boolean">false</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</ConditionDetection>
</MemberModules>
<RegularDetections>
<RegularDetection MonitorTypeStateID="HealthState">
<Node ID="HealthFilter">
<Node ID="DSa"/>
</Node>
</RegularDetection>
<RegularDetection MonitorTypeStateID="ErrorState">
<Node ID="ErrorFilter">
<Node ID="DSa"/>
</Node>
</RegularDetection>
</RegularDetections>
</MonitorImplementation>
</UnitMonitorType>