Data On Demand SQL Server connections probe (windows)

Community.DataOnDemand.SQL.2017.Probe.SQLServerConnections.WindowsAuth (ProbeActionModuleType)

Runs a DB query to list all connections and the database the connection is accessing, using Windows authentication.

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<ProbeActionModuleType ID="Community.DataOnDemand.SQL.2017.Probe.SQLServerConnections.WindowsAuth" Accessibility="Public" Batching="false" PassThrough="false" RunAs="MSSQLCore!Microsoft.SQLServer.Core.RunAs.Discovery">

  <Configuration>

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

  </Configuration>

  <OverrideableParameters>

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

  </OverrideableParameters>

  <ModuleImplementation Isolation="Any">

    <Composite>

      <MemberModules>

        <ProbeAction ID="PassThru" TypeID="System!System.PassThroughProbe"/>

        <ProbeAction ID="OledbProbe" TypeID="System!System.OleDbProbe">

          <ConnectionString>Provider=SQLOLEDB;Server=$Config/DatabaseServer$;Database=master;Integrated Security=SSPI</ConnectionString>

          <Query>

                  select

                    client_net_address,client_tcp_port,local_net_address,local_tcp_port,DB.name as database_name

                  from

                    sys.dm_exec_connections as DEC

                    inner join sys.dm_exec_sessions as DES on DES.session_id = DEC.most_recent_session_id

                    left outer join sys.databases as DB on DB.database_id = DES.database_id

                  where

                    net_transport = 'TCP'

                  order by

                    client_net_address,client_tcp_port

                </Query>

          <GetValue>true</GetValue>

          <IncludeOriginalItem>false</IncludeOriginalItem>

          <OneRowPerItem>false</OneRowPerItem>

        </ProbeAction>

      </MemberModules>

      <Composition>

        <Node ID="OledbProbe">

          <Node ID="PassThru"/>

        </Node>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.OleDbData</OutputType>

  <TriggerOnly>true</TriggerOnly>

</ProbeActionModuleType>