Microsoft Azure SQL Database Network Utilization Property Bag Data Provider

Microsoft.SqlServer.Azure.Module.DatabaseNetworkMonitoringDataSource (DataSourceModuleType)

This module collects Microsoft Azure SQL Database network utilization metrics into Property Bag object.

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SqlServer.Azure.Module.DatabaseNetworkMonitoringDataSource" RunAs="Microsoft.SqlServer.Azure.Profile.Workflow" 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" minOccurs="0" maxOccurs="1" name="SyncTime" type="xsd:string"/>

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

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

  </Configuration>

  <OverrideableParameters>

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

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

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

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

        <DataSource ID="DS" TypeID="Microsoft.SqlServer.Azure.Module.BaseScriptedPropertyBagDataSource">

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

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

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

          <WorkflowName>DatabaseNetworkMonitoring</WorkflowName>

          <Script>Param ($ServerName, $Username, $Password)



function Main {

    Set-Variable 'DatabaseName' 'master'

    WithRetry {$connection = (Create-SqlConnection $ServerName 'master' $Username $Password)} {

        $propertyBag = $ScriptApi.CreatePropertyBag()



        With ($command = $connection.CreateCommand()) {

            $command.CommandText = $Query

            $connection.Open()



            With (,($reader = $command.ExecuteReader())) {

                while ($reader.Read()) {

                    $dbName = $reader.get_Item('DatabaseName')

                    $type = $reader.get_Item('Class') + $reader.get_Item('Direction')



                    $propertyBag.AddValue($dbName + ' ' + $type, $reader.get_Item('TransferredKB'))

                }

            }

        }



        $propertyBag

    }

}





                  $Query = @"

SET NOCOUNT ON;

SELECT

    database_name AS [DatabaseName],

    class AS [Class],

    direction AS [Direction],

    quantity AS [TransferredKB]

FROM sys.Bandwidth_usage bw

    INNER JOIN (SELECT [database_name] AS [db], MAX([time]) AS [maxtime]

                FROM sys.Bandwidth_usage 

                WHERE DATEDIFF(n, [time], GETDATE()) &lt;= 120 

                GROUP BY [database_name]) t

        ON bw.[database_name] = t.[db] AND bw.[time] = t.[maxtime]

"@

                </Script>

          <Parameters>

            <Parameter>

              <Name>ServerName</Name>

              <Value>$Config/ServerName$</Value>

            </Parameter>

            <Parameter>

              <Name>Username</Name>

              <Value>$RunAs[Name="Microsoft.SqlServer.Azure.Profile.Sql"]/UserName$</Value>

            </Parameter>

            <Parameter>

              <Name>Password</Name>

              <Value>$RunAs[Name="Microsoft.SqlServer.Azure.Profile.Sql"]/Password$</Value>

            </Parameter>

          </Parameters>

        </DataSource>

      </MemberModules>

      <Composition>

        <Node ID="DS"/>

      </Composition>

    </Composite>

  </ModuleImplementation>

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

</DataSourceModuleType>