PowerShell command executer datasource

Microsoft.SQLServer.2014.AlwaysOn.CommandExecuterDataSource (DataSourceModuleType)

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2014.AlwaysOn.CommandExecuterDataSource" Accessibility="Internal">

  <Configuration>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="IntervalSeconds" type="xsd:integer"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="TimeoutSeconds" type="xsd:integer"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="IntervalSeconds" Selector="$Config/IntervalSeconds$" ParameterType="int"/>

    <OverrideableParameter ID="TimeoutSeconds" Selector="$Config/TimeoutSeconds$" ParameterType="int"/>

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

        <DataSource ID="DS" TypeID="System!System.CommandExecuterDiscoveryDataSource">

          <IntervalSeconds>$Config/IntervalSeconds$</IntervalSeconds>

          <ApplicationName/>

          <WorkingDirectory/>

          <CommandLine>powershell.exe -NoLogo -NoProfile -Noninteractive "$ep = get-executionpolicy; if ($ep -gt 'RemoteSigned') {set-executionpolicy -Scope Process remotesigned} &amp; '$$file/GeneralAlwaysOnDiscovery.ps1$$' '$MPElement$' '$Target/Id$' '$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/PrincipalName$' '$Target/Property[Type="Microsoft.SQLServer.2014.AlwaysOn.AlwaysOnSeed"]/InstanceName$'</CommandLine>

          <SecureInput/>

          <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

          <RequireOutput>true</RequireOutput>

          <Files>

            <File>

              <Name>GeneralAlwaysOnDiscovery.ps1</Name>

              <Contents><Script>param($MapElement, $TargetID, $ComputerName, $InstanceName) 



#TODO: Discuss event id

$SCRIPT_EVENT_ID = 4202 

$DEBUG_MODULE = "GeneralAlwaysOnDiscovery.ps1"



#Event Severity values

$ERROR_EVENT_TYPE = 1 

$INFORMATION_EVENT_TYPE = 0



$msgAssembiesError = "Unable to load SQLPS modules"



$arAssembies = 

            @(

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

}



#

# Get FQDN of machine in current domain as we assume that all replicas in the same domain

# If IP address is provided the host name is resolved via DNS 

#

function Get-FQDN {

	param ([string]$name = $(throw "Name is required parameter."))

	

	# return FQDN

	return [System.Net.Dns]::GetHostByName($name).HostName

}





function IsSeedCorrect {

	param ( 

		[string]$instanceName = $(throw "Instance name is required!")

	)

	

	$name = "HADR_Enabled"

	$path = "HKLM:\software\Microsoft\Microsoft SQL Server\MSSQL12.{0}\MSSQLServer\HADR" -f $instanceName

	$hadr = Get-ItemProperty -Path $path -Name $name -ErrorAction SilentlyContinue

	#TODO: check hadr value	(-qe 1)

		

	if (!$?) { throw $Error[0] }

}



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

}



#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

}



function Main {

	param(

		[string]$MapElement, 

		[string]$TargetID, 

		$ComputerName,

		[string]$InstanceName = $(throw "InstanceName is required but not provided.") 

	)



	$ErrorActionPreference = "stop"



	#

	# Prepare MOM API and discovery data object

	#

	$api = New-Object -comObject "MOM.ScriptAPI"



	$discoveryData = $api.CreateDiscoveryData(0, $MapElement, $TargetID)



	try	{



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



	    if ((ImportSQLModules $arAssembies "120\Tools") -eq $false)

        {

            Import-Module SQLPS -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null

            if( ( IsModuleLoaded "SQLPS") -eq $false)

            {

                throw $msgAssembiesError

            }

        }



		$LocalName = ($ComputerName -split '\.')[0]

		

		#

		# Check Seed is correct 

		#

		try	{

			$seedIsCorrent = IsSeedCorrect $InstanceName

		}

		catch {

			$discoveryData.IsSnapshot = $false

		}		



		#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"))

		{

			$discoveryData.IsSnapshot = $false

			$api.Return($discoveryData)	

			return

		}

  

        $serverName = BuildServerName $LocalName $InstanceName

		

		#

		# Get cluster name

		#

		$ClusterName = [string]::Empty

		cd SQLSERVER:\SQL\$LocalName\

		dir | foreach {

			if (($_.DisplayName -eq $InstanceName) -or 

                (($_.DisplayName -eq "DEFAULT") -and ($InstanceName -eq "MSSQLSERVER")) ) {

				$ClusterName = $_.ClusterName

			}

		}

				

		$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)



        $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.AvailabilityGroup], $true)

        $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.AvailabilityReplica], $true)



		$server.AvailabilityGroups | foreach {		

			$availabilityGroup = $_

		

			$automatedBackupReference = ([Microsoft.SqlServer.Management.Smo.AvailabilityGroupAutomatedBackupPreference]$availabilityGroup.AutomatedBackupPreference).ToString()

			$failureConditionLevel = ([Microsoft.SqlServer.Management.Smo.AvailabilityGroupFailureConditionLevel]$availabilityGroup.FailureConditionLevel).ToString()



			$ag = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroup']$")

			$ag.AddProperty("$MPElement[Name='System!System.Entity']/DisplayName$", $availabilityGroup.Name)

			$ag.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroup']/UniqueId$", $availabilityGroup.UniqueId.ToString("B")) 

			$ag.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroup']/PrimaryReplicaServerName$", $availabilityGroup.PrimaryReplicaServerName)

			$ag.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroup']/ClusterName$", $ClusterName)

			$ag.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroup']/AutomatedBackupPreference$", $automatedBackupReference)

			$ag.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroup']/FailureConditionLevel$", $failureConditionLevel)

			$ag.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroup']/HealthCheckTimeout$", $availabilityGroup.HealthCheckTimeout)

			$discoveryData.AddInstance($ag)

			

            $availabilityReplicas = $availabilityGroup.AvailabilityReplicas

			

			#

			# Create and fill all replicas in group and relationships

			#

			$IsPrimaryNode = ([regex]::Replace($availabilityGroup.PrimaryReplicaServerName, "\\.+", [string]::Empty) -eq $LocalName)



			#

			# 1. If current machine hosts primary replica of availability group then all discovery data for 

			# 	 all objects (AG, AR, DR) are collected from this machine

			# 2. If current mechine hosts non-primary replica (secondary, resolving, unknown, etc.) of AG then

			#	 discovery data is collected for AG, own AR and own DR 

			#

			$availabilityReplicas | where { $IsPrimaryNode -or ($LocalName -eq [regex]::Replace($_.Name, "\\.+", [string]::Empty)) } | foreach {

				$replicaName = $_.Name

				$replicaId = $_.UniqueId



				#

				# Get the name of computer that should host replica from EndpointUrl

				#

				$HostedComputer = Get-FQDN([regex]::Replace($_.Name, "\\.+", [string]::Empty))				

				#

				# Get the target instance name from replica name

				#

				$PerformanceObjectName = "SQLServer"

				if ($replicaName -ilike "*\*") {

					$PerformanceObjectName = ('MSSQL${0}' -f ([regex]::Replace($InstanceName, ".+\\", [string]::Empty)))

				}

				

                $role = ([Microsoft.SqlServer.Management.Smo.AvailabilityReplicaRole]$_.Role).ToString()

				$failoverMode = ([Microsoft.SqlServer.Management.Smo.AvailabilityReplicaFailoverMode]$_.FailoverMode).ToString()

				$availabilityMode = ([ Microsoft.SqlServer.Management.Smo.AvailabilityReplicaAvailabilityMode]$_.AvailabilityMode).ToString()

				$connectionState = ([Microsoft.SqlServer.Management.Smo.AvailabilityReplicaConnectionState]$_.ConnectionState).ToString()

				$connectionModeInPrimaryRole = ([Microsoft.SqlServer.Management.Smo.AvailabilityReplicaConnectionModeInPrimaryRole]$_.ConnectionModeInPrimaryRole).ToString()

				$connectionModeInSecondaryRole = ([Microsoft.SqlServer.Management.Smo.AvailabilityReplicaConnectionModeInSecondaryRole]$_.ConnectionModeInSecondaryRole).ToString()

				$operationalState = ([Microsoft.SqlServer.Management.Smo.AvailabilityReplicaOperationalState]$_.OperationalState).ToString()



				$ar = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']$")

				$ar.AddProperty("$MPElement[Name='System!System.Entity']/DisplayName$", $replicaName)

				$ar.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $HostedComputer)

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/UniqueId$", $replicaId.ToString("B"))

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/Role$", $role)

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/BackupPriority$", $_.BackupPriority)

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/FailoverMode$", $failoverMode)

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/AvailabilityMode$", $availabilityMode)

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/ConnectionState$", $connectionState)

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/ConnectionModeInPrimaryRole$", $connectionModeInPrimaryRole)

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/ConnectionModeInSecondaryRole$", $connectionModeInSecondaryRole)

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/EndpointUrl$", $_.EndpointUrl)

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/OperationalState$", $operationalState)					

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/AvailabilityGroupUniqueId$", $availabilityGroup.UniqueId.ToString("B"))

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/AvailabilityGroupName$", $availabilityGroup.Name)

				$ar.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/PerformanceObjectName$", $PerformanceObjectName)

				$discoveryData.AddInstance($ar)



				$agContainsArRelationship = $discoveryData.CreateRelationshipInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupContainsAvailabilityReplica']$")

				$agContainsArRelationship.Source = $ag

				$agContainsArRelationship.Target = $ar

				$discoveryData.AddInstance($agContainsArRelationship)

				

				$aghDisplayName = "{0}.{1}" -f $availabilityGroup.Name, $replicaName

				

				$agh = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupHealth']$")

				$agh.AddProperty("$MPElement[Name='System!System.Entity']/DisplayName$", $aghDisplayName)			

				$agh.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $HostedComputer)

				$agh.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupHealth']/UniqueId$", $replicaId.ToString("B"))

				$agh.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupHealth']/InstanceName$", $replicaName)

				$agh.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupHealth']/AvailabilityGroupName$", $availabilityGroup.Name)

				$discoveryData.AddInstance($agh)

				

				$agHealthRelation = $discoveryData.CreateRelationshipInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupReferencesAvailabilityGroupHealth']$")

				$agHealthRelation.Source = $ag

				$agHealthRelation.Target = $agh

				$discoveryData.AddInstance($agHealthRelation)						

			}

		}						

	}

	catch {

		$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE

		$message = "Error occurred during AlwaysOn discovery.{0}Reason: {1}" -f [Environment]::NewLine, $_.Exception.Message

		$api.LogScriptEvent($header, $SCRIPT_EVENT_ID, $ERROR_EVENT_TYPE, $message)

		$discoveryData.IsSnapshot = $false

	}

	

	$api.Return($discoveryData)

}

Main $MapElement $TargetID $ComputerName $InstanceName</Script></Contents>

              <Unicode>true</Unicode>

            </File>

          </Files>

        </DataSource>

      </MemberModules>

      <Composition>

        <Node ID="DS"/>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.Discovery.Data</OutputType>

</DataSourceModuleType>