Benutzerrichtlinien-Ermittlungsdatenquelle
Microsoft.SQLServer.2014.AlwaysOn.UserPolicyDataSource (DataSourceModuleType)
Element properties: Member Modules:
Overrideable Parameters:
Source Code: <DataSourceModuleType ID="Microsoft.SQLServer.2014.AlwaysOn.UserPolicyDataSource" Accessibility="Internal">
<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} & '$$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 = 4202
#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\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
}
#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.2014.AlwaysOn.Policy']/PolicyID$", $UserPolicyObject.PolicyID)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/PolicyName$", $UserPolicyObject.PolicyName)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/PolicyCategory$", $UserPolicyObject.PolicyCategory)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/Description$", $UserPolicyObject.Description)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/Condition$", $UserPolicyObject.Condition)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/Facet$", $UserPolicyObject.Facet)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/HelpLink$", $UserPolicyObject.HelpLink)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.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\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 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\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
}
}
$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.2014.AlwaysOn.AvailabilityGroupErrorUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupHealth']/UniqueId$", $replicaId.ToString("B"))
}
"*warning*"
{
$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupWarningUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupHealth']/UniqueId$", $replicaId.ToString("B"))
}
}
}
}
"AvailabilityReplica"
{
switch -wildcard ($currentUserPolicy.PolicyCategory)
{
"*error*"
{
$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplicaErrorUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/UniqueId$", $replicaId.ToString("B"))
}
"*warning*"
{
$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplicaWarningUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.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.2014.AlwaysOn.DatabaseReplicaErrorUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplica']/Name$", $drname)
}
"*warning*"
{
$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplicaWarningUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.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} Module: {1}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE
$msg += "Error occurred during User Policy discovery.{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($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>