Two State System Policy Monitor Type

Microsoft.SQLServer.2014.AlwaysOn.SystemPolicyScriptMonitorType (UnitMonitorType)

Two state script monitor type with Powershell script Datasource. Used to monitor System Policies.

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} &amp; '$$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: &lt;machineName&gt;

	# if instance is named then: &lt;machineName&gt;\&lt;instanceName&gt;

	#



	$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

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