AKN631497

Monitor_AKN631497 (UnitMonitor)

The number of availability databases per computer exceeds 100

Knowledge Base article:

Element properties:

Source Code:

<UnitMonitor ID="Monitor_AKN631497" Comment="SupportTopic=TBD;VersionNumber=1.0.0.0;" Accessibility="Public" Enabled="true" Target="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="High" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessage312ba20f4dad4ad0b12a4f59cadf9001">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Success" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>AKN631497.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>ConnectString</Name>

        <Value>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.DBEngine"]/ConnectionString$</Value>

      </Parameter>

      <Parameter>

        <Name>StrProductVersionInput</Name>

        <Value>$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/ProductVersion$</Value>

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($ConnectString,$StrProductVersionInput)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

$scriptargs | add-member NoteProperty "ConnectString" $ConnectString

$scriptargs | add-member NoteProperty "StrProductVersionInput" $StrProductVersionInput



# Set up the output

$global:scriptoutput = new-object psobject

$scriptoutput | add-member NoteProperty "HasIssue" $false

#----------------------------------------------------- 

# MAIN CODE SECTION

#----------------------------------------------------- 



# Environment

$scriptenv = New-Object psobject

$scriptenv | Add-Member ScriptProperty "ConnectionString" {"Server=" + $scriptargs.ConnectString + "; Database = master; Integrated Security=true"}

$scriptenv | Add-Member NoteProperty "RuntimeError" $false

$scriptenv | Add-Member NoteProperty "HasIssue" $false

$scriptenv | Add-Member ScriptProperty "IsSQLServer2012Env" {[version]($scriptargs.StrProductVersionInput) -ge [version]"11.00.0000"}





function Get-NumOfAvailabilityDB

{	

	if(-not [string]::IsNullOrEmpty($scriptargs.ConnectString))

	{

		# Create SqlConnection object and define connection string

		$oConnection = New-Object System.Data.SqlClient.SqlConnection

		$oConnection.ConnectionString = $scriptenv.ConnectionString

		$oConnection.Open()

		

		# Create SqlCommand object, define command text, and set the connection

		$oSqlCommand = New-Object System.Data.SqlClient.SqlCommand

		$oSqlCommand.Connection = $oConnection

		

		# Get number of availability databases per computer in this SQL Server instance

		$oSqlCommand.CommandText = "SELECT COUNT(*) as counts FROM sys.databases WHERE group_database_id IS NOT NULL"

		$reader = $oSqlCommand.ExecuteReader()		

		while($reader.Read())

		{

			[int]$countNum=0			

			if(([int]::tryparse($reader["counts"], [ref]$countNum)) -and ($countNum -gt 100))

			{

				$scriptenv.HasIssue = $true

			}

		}		

		

		# Close reader object

		$reader.Close()



		# Close SqlConnection object

		$oConnection.Close()

	}

}



# Main function



function AdvisorRule($scriptargs, $scriptoutput)

{

	trap [Exception] {

		$scriptenv.RuntimeError = $true		

      	continue; 

   	}



	# Initialize parameters

	$scriptoutput.HasIssue = $false

	# Set parameter values

	

	# Help functions	

	

	# Detection Logic

	if($scriptenv.IsSQLServer2012Env)

	{			

		Get-NumOfAvailabilityDB									

		if($scriptenv.HasIssue -and $scriptenv.RuntimeError -eq $false)

		{

			$scriptoutput.HasIssue = $scriptenv.HasIssue

		}				

	}

}





AdvisorRule $scriptargs $scriptoutput



# set the output

$mom = new-object -comobject "MOM.ScriptAPI"

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.HasIssue -ne $null)

{

	$bag.AddValue("HasIssue", $scriptoutput.HasIssue)

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86397</Schedule>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="Boolean">true</Value>

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Boolean">true</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>