Fonte de Dados de Descoberta da Política do Usuário

Microsoft.SQLServer.2012.AlwaysOn.UserPolicyDataSource (DataSourceModuleType)

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2012.AlwaysOn.UserPolicyDataSource" Accessibility="Public">

  <Configuration>

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

    <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/GeneralAlwaysOnUserPolicyDiscovery.ps1$$' '$MPElement$' '$Target/Id$' '$Config/InstanceName$'</CommandLine>

          <SecureInput/>

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

          <RequireOutput>true</RequireOutput>

          <Files>

            <File>

              <Name>GeneralAlwaysOnUserPolicyDiscovery.ps1</Name>

              <Contents><Script># DiscoverSQLUserPolicy.ps1

# Group User Policy Discovery Script

#

param($MapElement, $TargetID, $InstancePath)



# $InstancePath supposed to be of three types:

# computername

# computername\MSSQLSERVER

# computername\MSSQLSERVER.db1



#TODO: Discuss event id

$SCRIPT_EVENT_ID = 4101 



#Event Severity values

$INFORMATION_EVENT_TYPE = 0

$ERROR_EVENT_TYPE = 1 



$DBNull = [System.DBNull]::Value 



$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\110\Tools\ClientSetup\" | Select SQLPath).SQLPath ,

		(get-itemproperty -path "hklm:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\110\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

}



#debug $api.LogScriptEvent("Policy Discovery Script ", $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, "started policy discovery script")



function FillUserPolicyInstance

{

	param ($UserPolicyObject = $(throw "Name is required parameter."), $UserPolicyInstance = $(throw "Name is required parameter."))

	

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

	$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.Policy']/PolicyID$", $UserPolicyObject.PolicyID)

	$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.Policy']/PolicyName$", $UserPolicyObject.PolicyName)

	$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.Policy']/PolicyCategory$", $UserPolicyObject.PolicyCategory)

	$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.Policy']/Description$", $UserPolicyObject.Description)

	$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.Policy']/Condition$", $UserPolicyObject.Condition)

	$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.Policy']/Facet$", $UserPolicyObject.Facet)

	$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.Policy']/HelpLink$", $UserPolicyObject.HelpLink)

	$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.Policy']/IsEnabled$", $UserPolicyObject.IsEnabled)

	

	return $UserPolicyInstance

}



#

# 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 BuildServerName([String] $pHostName, [String] $pInstanceName){

    $namespace = "root\Microsoft\SqlServer\ComputerManagement11"

    $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 BuildConnectionString([String] $serverName, [String] $databaseName) {  

	$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder

	$builder["Data Source"] = $serverName

    $builder["Initial Catalog"] = $databaseName

	$builder["Integrated Security"] = 'SSPI'

	return $builder.ConnectionString

}



#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(

		$MapElement, 

		$TargetID, 

		$InstancePath

	)





	# 

	# Prepare MOM API and property bag object

	#

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

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



	try

	{

		$paths = 

			@( (get-itemproperty -path "hklm:\Software\Microsoft\Microsoft SQL Server\110\Tools\ClientSetup\" | Select SQLPath).SQLPath ,

			(get-itemproperty -path "hklm:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\110\Tools\ClientSetup\" | Select SQLPath).SQLPath )



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

		{

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

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

			{

				throw $msgAssembiesError

			}

		}



		$msg  = [string]::Empty

		

		$pathArray = $InstancePath -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"))

		{

			$discoveryData.IsSnapshot = $false

			$api.Return($discoveryData)	

			return

		}

				

		$UserPolicies = @{}



		$msg = "User Policies: {0}" -f [Environment]::NewLine

		

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

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

		$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

		$DataSet = New-Object System.Data.DataSet

		

        $serverName = BuildServerName $ComputerName $InstanceName

		$SqlConnection.ConnectionString = BuildConnectionString $serverName 'msdb'

		 

		$SqlCmd.CommandText = " SELECT p.policy_id, 

	                    	           p.name AS policy_name,

	                          	       p.[description],

	                              	   p.help_text,

	                             	   p.help_link,

									   p.is_enabled,

	                            	   c.name AS condition_name,

	                            	   c.facet,

	                            	   pc.name AS category_name,

	                            	   cf.obj_name

                         	  	FROM syspolicy_policies p

                         		JOIN syspolicy_conditions c ON c.condition_id = p.condition_id

                         		JOIN syspolicy_policy_categories pc ON pc.policy_category_id = p.policy_category_id

                         		JOIN syspolicy_object_sets os ON os.object_set_id = p.object_set_id

                         		JOIN syspolicy_target_sets ts ON ts.object_set_id = os.object_set_id

                         		JOIN syspolicy_target_set_levels tsl ON tsl.target_set_id = ts.target_set_id

                         		LEFT OUTER JOIN syspolicy_conditions cf ON cf.condition_id = tsl.condition_id

                         		WHERE p.is_system = 0 AND tsl.level_name = c.facet"

		$SqlCmd.Connection = $SqlConnection

		$SqlAdapter.SelectCommand = $SqlCmd

		$SqlAdapter.Fill($DataSet)|out-null

		 

		$DataSet.Tables[0] | foreach {

			$UserPolicy = New-Object PSObject

			$UserPolicy | Add-Member -memberType NoteProperty -name "DisplayName" -value $_.policy_name

			$UserPolicy | Add-Member -memberType NoteProperty -name "PolicyID" -value $_.policy_id

			$UserPolicy | Add-Member -memberType NoteProperty -name "PolicyName" -value $_.policy_name

			$UserPolicy | Add-Member -memberType NoteProperty -name "PolicyCategory" -value $_.category_name

			$UserPolicy | Add-Member -memberType NoteProperty -name "Description" -value $_.description

			$UserPolicy | Add-Member -memberType NoteProperty -name "Condition" -value $_.condition_name

			$UserPolicy | Add-Member -memberType NoteProperty -name "Facet" -value $_.facet

			$UserPolicy | Add-Member -memberType NoteProperty -name "HelpLink" -value $_.help_link

			$UserPolicy | Add-Member -memberType NoteProperty -name "ObjName" -value $_.obj_name

			$UserPolicy | Add-Member -memberType NoteProperty -name "IsEnabled" -value $_.is_enabled

			

			$UserPolicies[$UserPolicy.PolicyID] = $UserPolicy

		}

		$SqlConnection.Close()





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

		$serverObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.AvailabilityReplica], 'UniqueId', 'EndpointUrl')

 		$serverObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.DatabaseReplicaState], 'AvailabilityReplicaId', 'AvailabilityReplicaServerName', 'AvailabilityDatabaseName')

		

		$serverObj.AvailabilityGroups | foreach {

			$avaliabilityGroupName = $_.Name

			$availabilityReplicas = $_.AvailabilityReplicas

			$availabilityDatabases = $_.DatabaseReplicaStates



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

				$replicaId = $_.UniqueId

				$replicaName = $_.Name



				#

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

				#

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



				$UserPolicies.GetEnumerator() | ForEach-Object {

					$currentUserPolicy = $_.Value

					$newPolicy = $null

					

					switch ($currentUserPolicy.Facet)

					{

						"AvailabilityGroup"

						{

							$createPolicy = $true

							if (($currentUserPolicy.ObjName -ne $null) -and (!$DBNull.Equals($currentUserPolicy.ObjName)))

							{

								$createPolicy = $currentUserPolicy.ObjName.ToLower() -eq $avaliabilityGroupName.ToLower()

							}



							if ($createPolicy)

							{

								switch -wildcard ($currentUserPolicy.PolicyCategory)

								{

									"*error*"

									{

										$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.AvailabilityGroupErrorUserPolicy']$")

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

									}

									"*warning*"

									{

										$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.AvailabilityGroupWarningUserPolicy']$")

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

									}

									

								}

							}

						}

						"AvailabilityReplica"

						{

							switch -wildcard ($currentUserPolicy.PolicyCategory)

							{

								"*error*"

								{

									$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.AvailabilityReplicaErrorUserPolicy']$")

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

								}

								"*warning*"

								{

									$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.AvailabilityReplicaWarningUserPolicy']$")

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

								}

							}

						}

					}

					

					if ($newPolicy -ne $null)

					{

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

						$newPolicy = FillUserPolicyInstance $currentUserPolicy $newPolicy

						$discoveryData.AddInstance($newPolicy)



						$msg += "Name = {0}; Facet = {1}; Category = {2}{3}" -f $currentUserPolicy.PolicyName, $currentUserPolicy.Facet, $currentUserPolicy.PolicyCategory, [Environment]::NewLine

					}	

					

				}



				$availabilityDatabases | where { $_.AvailabilityReplicaId -eq  $replicaId } | foreach {

				

					$drname = "{0}.{1}" -f $_.AvailabilityReplicaServerName, $_.AvailabilityDatabaseName

					$UserPolicies.GetEnumerator() | ForEach-Object {

						$currentUserPolicy = $_.Value

						$newPolicy = $null

						

						switch ($currentUserPolicy.Facet)

						{

							"DatabaseReplicaState"

							{

								switch -wildcard ($currentUserPolicy.PolicyCategory)

								{

									"*error*"

									{

										$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.DatabaseReplicaErrorUserPolicy']$")

										$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.DatabaseReplica']/Name$", $drname)

									}

									"*warning*"

									{

										$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.DatabaseReplicaWarningUserPolicy']$")

										$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2012.AlwaysOn.DatabaseReplica']/Name$", $drname)

									}

								}

							}

						}

						

						if ($newPolicy -ne $null)

						{

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

							$newPolicy = FillUserPolicyInstance $currentUserPolicy $newPolicy

							$discoveryData.AddInstance($newPolicy)



							$msg += "Name = {0}; Facet = {1}; Category = {2}{3}" -f $currentUserPolicy.PolicyName, $currentUserPolicy.Facet, $currentUserPolicy.PolicyCategory, [Environment]::NewLine

						}	

						

					}

				}

				

				

			}

		}

		

		#debug $api.LogScriptEvent("Workflow succeeded! ", $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)

	}

	catch

	{

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

		$msg += "Error occured during User Policy discovery.{0}Computer:{1} {0}Reason: {2}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message

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

		$discoveryData.IsSnapshot = $false

	}

	$api.Return($discoveryData)

} 



Main $MapElement $TargetID $InstancePath

</Script></Contents>

              <Unicode>true</Unicode>

            </File>

          </Files>

        </DataSource>

      </MemberModules>

      <Composition>

        <Node ID="DS"/>

      </Composition>

    </Composite>

  </ModuleImplementation>

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

</DataSourceModuleType>