Active Sessions Datasource 2008

Microsoft.SQLServer.2008.DBEngine.ActiveSessions.DataSource (DataSourceModuleType)

Datasource for collection SQL 2008 DBs active sessions

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2008.DBEngine.ActiveSessions.DataSource" Accessibility="Public">

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

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

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

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

  </Configuration>

  <OverrideableParameters>

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

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

    <OverrideableParameter ID="SyncTime" Selector="$Config/SyncTime$" ParameterType="string"/>

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

        <DataSource ID="Scheduler" TypeID="System!System.Scheduler">

          <Scheduler>

            <SimpleReccuringSchedule>

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

              <SyncTime>$Config/SyncTime$</SyncTime>

            </SimpleReccuringSchedule>

            <ExcludeDates/>

          </Scheduler>

        </DataSource>

        <ProbeAction ID="PS" TypeID="Windows!Microsoft.Windows.PowerShellPropertyBagProbe">

          <ScriptName>ActiveSessionsDataSource.ps1</ScriptName>

          <ScriptBody><Script># ActiveSessionsDataSource.ps1

#

param($computerName, $sqlInstanceName, $connectionString, $tcpPort, $SQL_WMI_NAMESPACE, $serviceName)



# $InstancePath is the 'Connection String' property of SQL DB Engine Class (Microsoft.SQLServer.DBEngine)

# $InstancePath supposed to be of three types:

# computername

# computername\MSSQLSERVER

# computername\MSSQLSERVER.db1

$DEBUG_MODE = 0

$DEBUG_MODULE = "ActiveSessionsDataSource.ps1"

$DEBUG_MSG = ""

$DEBUG_SA = $null

$DEBUG_PWD = $null



#TODO: Discuss event id

$SCRIPT_EVENT_ID = 4001



#Event Severity values

$INFORMATION_EVENT_TYPE = 0

$ERROR_EVENT_TYPE = 1 



function BuildConnectionString(

    [String] $pHostName, 

    [String] $pInstanceName, 

    [String] $databaseName, 

	[String] $SQL_NAMESPACE, 	

    [String] $connectionString = $null,

    [String] $user = $null, 

    [String] $password = $null) 

{    

    if (($connectionString.Length -ne 0)) {

        return $connectionString

    } else {

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

    	$serverName = ""

		$namespace = "root\Microsoft\SqlServer\" + $SQL_NAMESPACE

		$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

				}

			}

		} 

	

    	$builder["Data Source"] = $serverName

        $builder["Initial Catalog"] = $databaseName



        if (($user.Length -ne 0) -and ($password.Length -ne 0)) {

            $builder["User ID"] = $user

            $builder["Password"] = $password

    	}

    	else

    	{

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

    	}



    	return $builder.ConnectionString

    }

}



function SqlTestDestination($connection, $serverName, $instanceName) {

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

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

	$DataSet = New-Object System.Data.DataSet

	$SqlCmd.CommandText = "select SERVERPROPERTY('MachineName') as ServerName, @@servicename as InstanceName"

	$SqlCmd.Connection = $SqlConnection

	$SqlAdapter.SelectCommand = $SqlCmd

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

	$res = $DataSet.Tables | select -first 1

	if ($res -ne $null) {

		$queryServerName = ($res | Select  -ExpandProperty ServerName).ToUpperInvariant()

        $queryInstanceName = ($res | Select  -ExpandProperty InstanceName).ToUpperInvariant()

        $serverNameWithoutDomain = $serverName

        $dotPosition = $serverName.IndexOf(".")

        if ($dotPosition -gt -1) {

            $serverNameWithoutDomain = $serverName.Substring(0, $dotPosition)

        }

        if (($serverNameWithoutDomain.ToUpperInvariant() -eq $queryServerName) -and ($instanceName.ToUpperInvariant() -eq $queryInstanceName)) {

            return;

        }

	}

	throw "Connection target check failed: connected to " + $serverName + "\\" + $instanceName + ", but got " + $queryServerName + "\\" + $queryInstanceName + "." 

}



function BuildConnectionStringWithPort(

        [String] $connectionString, 

        [int] $port, 

        [String] $user = $null, 

        [String] $password = $null) {

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

	if ($port -eq 0){

		$builder["Data Source"] = $connectionString

	} else {

		$builder["Data Source"] = $connectionString + "," + $port

	}

    if (($user.Length -ne 0) -and ($password.Length -ne 0)) {

        $builder["User ID"] = $user

        $builder["Password"] = $password

    } else {

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

    }

	return $builder.ConnectionString

}



function ValidateConnectionString(

        [String] $connectionString, 

        [int] $port, 

		[String] $serverName, 

		[String] $instanceName, 

        [String] $user = $null, 

        [String] $password = $null) 

{

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

	try {

        $SqlConnection.ConnectionString = BuildConnectionStringWithPort $connectionString 0 $user $password

        $SqlConnection.Open()

		SqlTestDestination $SqlConnection $serverName $instanceName

        $SqlConnection.Close()

        return $SqlConnection.ConnectionString

	} catch {

		try	{

			if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {

				$SqlConnection.Close()

			}

			$SqlConnection.ConnectionString = BuildConnectionStringWithPort $connectionString $port $user $password

			$SqlConnection.Open()

			SqlTestDestination $SqlConnection $serverName $instanceName

			$SqlConnection.Close()

			return $SqlConnection.ConnectionString

		} catch {

			if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {

				$SqlConnection.Close()

			}

		}

	}

    return $null

}



function SqlQueryTables($connectionString, $query) {			

	

	$res = $null;

		

	$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

	

	#if an error takes place during execution when the function throws exception

    try {

		$SqlConnection.ConnectionString = $connectionString	 

        $SqlConnection.Open()

		$SqlCmd.CommandText = $query

		$SqlCmd.Connection = $SqlConnection

		$SqlAdapter.SelectCommand = $SqlCmd

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

		$res = $DataSet.Tables

        $SqlConnection.Close()

	}

	catch {

        if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {

		    $SqlConnection.Close()

        }

        throw $_.Exception

	}



	return $res

}



#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 Main {

	param(		

		$computerName, 

		$sqlInstanceName,

		$connectionString, 

		$tcpPort,

        $SQL_WMI_NAMESPACE, 

		$serviceName

	)



	# 

	# Prepare MOM API and property bag object

	#

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



	#if service is not in running state when exit without any error

	$state = GetServiceState $computerName $serviceName $sqlInstanceName

	if(($state -ne "Running") -and ($state -ne "Unknown"))

	{

		return

	}



	$sqlConnectionString = $connectionString

    [int]$sqlTcpPort = $tcpPort

    

    $sqlConnectionString = ValidateConnectionString $sqlConnectionString $sqlTcpPort $computerName $sqlInstanceName $DEBUG_SA $DEBUG_PWD



	try

	{

		$msg  = [string]::Empty

		

		$query = "SELECT DB_NAME(dbid) as DBName FROM sys.sysdatabases;

			

									SELECT 

									DB_NAME(dbid) as DBName, 

									COUNT(dbid) as NSessions    

									FROM sys.sysprocesses

									WHERE dbid &gt; 0

									GROUP BY dbid;"

		

		$connectionString = BuildConnectionString $computerName $sqlInstanceName 'master' $SQL_WMI_NAMESPACE $sqlConnectionString



		$res = SqlQueryTables $connectionString $query

			

		$dbConections = New-Object 'System.Collections.Generic.Dictionary[String,Double]'

		

		$res[0] | foreach {			

			$dbConections[$_.DBName] = [double]0				

		}										

		 

		$dbCount = 0;

		$res[1] | foreach {			

			$dbConections[$_.DBName] = [double]$_.NSessions								

			$dbCount++;

		}

		

		$dbConections.GetEnumerator() | foreach {

			$bag = $api.CreatePropertyBag()              	

			$bag.AddValue("Name", $_.Key)          	

			$bag.AddValue("Value", $_.Value)

			$bag

		}

		$msg = "Active sessions were got for {0} DBs on {1}" -f $dbCount, $InstancePath		

		

		if($DEBUG_MODE -eq 1) {

			$api.LogScriptEvent($DEBUG_MODULE, $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 occured during Active connection data source executing.{0}Computer: {1} {0}Reason: {2} {0}Position:{3} {0}Offset:{4}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message, $_.InvocationInfo.ScriptLineNumber, $_.InvocationInfo.OffsetInLine

		$msg += "{0}Detailed error output: {1}" -f [Environment]::NewLine, [String]::Join("{0}--------{0}" -f [Environment]::NewLine, $Error.ToArray())

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

	}

} 



Main $computerName $sqlInstanceName $connectionString $tcpPort $SQL_WMI_NAMESPACE $serviceName</Script></ScriptBody>

          <SnapIns/>

          <Parameters>

            <Parameter>

              <Name>computerName</Name>

              <Value>$Config/ComputerName$</Value>

            </Parameter>

            <Parameter>

              <Name>sqlInstanceName</Name>

              <Value>$Config/SqlInstanceName$</Value>

            </Parameter>

            <Parameter>

              <Name>connectionString</Name>

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

            </Parameter>

            <Parameter>

              <Name>tcpPort</Name>

              <Value>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/TcpPort$</Value>

            </Parameter>

            <Parameter>

              <Name>SQL_WMI_NAMESPACE</Name>

              <Value>ComputerManagement10</Value>

            </Parameter>

            <Parameter>

              <Name>serviceName</Name>

              <Value>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$</Value>

            </Parameter>

          </Parameters>

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

          <StrictErrorHandling>true</StrictErrorHandling>

        </ProbeAction>

      </MemberModules>

      <Composition>

        <Node ID="PS">

          <Node ID="Scheduler"/>

        </Node>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.PropertyBagData</OutputType>

</DataSourceModuleType>