<?xml version="1.0" standalone="yes"?>

<NetVigil>
  <monitor type="wmi">

    <!-- $Id: //depot/RELEASE/Traverse_5.0/etc/typedef/95_wmi_ms_sqlsvr.xml#1 $ -->

    <onLoad>
      <verify type="value" 
        object="\Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSDatabases:Frequency_PerfTime:Name=_Total"
        pattern="\d+" 
        action="accept"/>
    </onLoad>

    <!-- statistics -->
    
    <probeConfig>
      <subType name="sqlsvr_stats_autoparam" enabled="false">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSSQLStatistics</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="AutoParamAttemptsPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Auto Param (Total)"
          displayUnit="attempts/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_stats_autoparam" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSSQLStatistics</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="FailedAutoParamsPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Auto Param (Failed)"
          displayUnit="attempts/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_stats_autoparam" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSSQLStatistics</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="SafeAutoParamsPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Auto Param (Safe)"
          displayUnit="attempts/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_stats_autoparam" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSSQLStatistics</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="UnsafeAutoParamsPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Auto Param (Unsafe)"
          displayUnit="attempts/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_stats_batchreq" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSSQLStatistics</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="BatchRequestsPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Batch Requests"
          displayUnit="req/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_stats_compile" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSSQLStatistics</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="SQLCompilationsPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Compilations"
          displayUnit="oper/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_stats_compile" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSSQLStatistics</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="SQLReCompilationsPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Re-Compilations"
          displayUnit="oper/s"
          directive="rate"/>
      </subType>
    </probeConfig>


    <!-- general statistics -->
    
    <probeConfig>
      <subType name="sqlsvr_stats_users" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSGeneralStatistics</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="UserConnections"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Users Connected"
          displayUnit="users"
          directive="none"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_stats_users" enabled="false">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSGeneralStatistics</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="LoginsPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Users Logging In"
          displayUnit="users/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_stats_users" enabled="false">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSGeneralStatistics</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="LogoutsPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Users Logging Out"
          displayUnit="users/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <!-- databases -->
    
    <probeConfig>
      <subType name="sqlsvr_db_txncount" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSDatabases</prefix>
          <poll name="keyField" property="Name"/>
          <poll name="objectName" property="Name"/>
          <poll name="input" property="ActiveTransactions"/>
        </wmiObject>
        <filter name="objectName"
          pattern="^_Total$"
          action="reject"/>
        <postProcess
          testName="SQL Server: DB ([[$objectName]]) Active Transactions"
          displayUnit="txn"
          directive="none"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_db_size" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSDatabases</prefix>
          <poll name="keyField" property="Name"/>
          <poll name="objectName" property="Name"/>
          <poll name="input" property="DataFilesSizeKB"/>
        </wmiObject>
        <filter name="objectName"
          pattern="^_Total$"
          action="reject"/>
        <postProcess
          testName="SQL Server: DB ([[$objectName]]) File Size"
          displayUnit="kb"
          warningThreshold="[[ $input + ($input * 0.15) ]]"
          criticalThreshold="[[ $input + ($input * 0.50) ]]"
          directive="none"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_db_txnrate" enabled="false">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSDatabases</prefix>
          <poll name="keyField" property="Name"/>
          <poll name="objectName" property="Name"/>
          <poll name="input" property="TransactionsPersec"/>
        </wmiObject>
        <filter name="objectName"
          pattern="^_Total$"
          action="reject"/>
        <postProcess
          testName="SQL Server: DB ([[$objectName]]) Transaction Rate"
          displayUnit="txn/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_stats_cachehit" enabled="false">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSDatabases</prefix>
          <poll name="keyField" property="Name"/>
          <poll name="objectName" property="Name"/>
          <poll name="ratioBase" property="LogCacheHitRatio_Base"/>
          <poll name="input" property="LogCacheHitRatio"/>
        </wmiObject>
        <filter name="objectName"
          pattern="^_Total$"
          action="reject"/>
        <filter name="ratioBase"
          pattern="(^$|^0$)"
          action="reject"/>
        <postProcess
          testName="SQL Server: DB ([[$objectName]]) Log Cache Hit Ratio"
          displayUnit="%"
          resultMultiplier="[[100/$ratioBase]]"
          directive="none"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_db_logspace" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSDatabases</prefix>
          <poll name="keyField" property="Name"/>
          <poll name="objectName" property="Name"/>
          <poll name="input" property="PercentLogUsed"/>
        </wmiObject>
        <filter name="objectName"
          pattern="^_Total$"
          action="reject"/>
        <postProcess
          testName="SQL Server: DB ([[$objectName]]) Log Space Util"
          displayUnit="%"
          directive="none"/>
      </subType>
    </probeConfig>


    <!-- buffer manager -->

    <probeConfig>
      <subType name="sqlsvr_stats_cachehit" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSBufferManager</prefix>
          <poll name="keyField" property="@"/>
          <poll name="ratioBase" property="Buffercachehitratio_Base"/>
          <poll name="input" property="Buffercachehitratio"/>
        </wmiObject>
        <filter name="objectName"
          pattern="^_Total$"
          action="reject"/>
        <filter name="ratioBase"
          pattern="(^$|^0$)"
          action="reject"/>
        <postProcess
          testName="SQL Server: Buffer Cache Hit Ratio"
          displayUnit="%"
          resultMultiplier="[[100/$ratioBase]]"
          directive="none"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_page_txnrate" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSBufferManager</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="PageReadsPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Page Reads"
          displayUnit="txn/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_page_txnrate" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSBufferManager</prefix>
          <poll name="keyField" property="@"/>
          <poll name="input" property="PageWritesPersec"/>
        </wmiObject>
        <postProcess
          testName="SQL Server: Page Writes"
          displayUnit="txn/s"
          directive="rate"/>
      </subType>
    </probeConfig>

    <!-- cache manager -->

    <probeConfig>
      <subType name="sqlsvr_stats_cachehit" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSCacheManager</prefix>
          <poll name="keyField" property="Name"/>
          <poll name="objectName" property="Name"/>
          <poll name="ratioBase" property="CacheHitRatio_Base"/>
          <poll name="input" property="CacheHitRatio"/>
        </wmiObject>
        <filter name="objectName"
          pattern="^_Total$"
          action="reject"/>
        <filter name="ratioBase"
          pattern="(^$|^0$)"
          action="reject"/>
        <postProcess
          testName="SQL Server: [[$objectName]] Cache Hit Ratio"
          displayUnit="%"
          resultMultiplier="[[100/$ratioBase]]"
          directive="none"/>
      </subType>
    </probeConfig>

    <!-- locks -->

    <probeConfig>
      <subType name="sqlsvr_locks_waittime" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSLocks</prefix>
          <poll name="keyField" property="Name"/>
          <poll name="objectName" property="Name"/>
          <poll name="input" property="AverageWaitTimems"/>
        </wmiObject>
        <filter name="objectName"
          pattern="^_Total$"
          action="reject"/>
        <postProcess
          testName="SQL Server: [[$objectName]] Lock Average Wait"
          displayUnit="ms"
          directive="none"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_locks_reqrate" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSLocks</prefix>
          <poll name="keyField" property="Name"/>
          <poll name="objectName" property="Name"/>
          <poll name="input" property="AverageWaitTimems"/>
        </wmiObject>
        <filter name="objectName"
          pattern="^_Total$"
          action="reject"/>
        <postProcess
          testName="SQL Server: [[$objectName]] Lock Requests"
          displayUnit="locks/sec"
          directive="rate"/>
      </subType>
    </probeConfig>

    <probeConfig>
      <subType name="sqlsvr_locks_reqrate" enabled="true">
        <wmiObject>
          <prefix>Win32_PerfRawData_MSSQLSQLEXPRESS_MSSQLSQLEXPRESSLocks</prefix>
          <poll name="keyField" property="Name"/>
          <poll name="objectName" property="Name"/>
          <poll name="input" property="NumberofDeadlocksPersec"/>
        </wmiObject>
        <filter name="objectName"
          pattern="^_Total$"
          action="reject"/>
        <postProcess
          testName="SQL Server: [[$objectName]] Lock Deadlocks"
          displayUnit="locks/sec"
          directive="rate"/>
      </subType>
    </probeConfig>

  </monitor>

  <applicationProfile>
    <name>Microsoft SQL Server Database</name>
    <description>
      Availability and performance metrics from Microsoft SQL Server
    </description>
    <testList type="wmi">
      <subType>sqlsvr_stats_autoparam</subType>
      <subType>sqlsvr_stats_batchreq</subType>
      <subType>sqlsvr_stats_compile</subType>
      <subType>sqlsvr_stats_users</subType>
      <subType>sqlsvr_db_txncount</subType>
      <subType>sqlsvr_db_size</subType>
      <subType>sqlsvr_db_txnrate</subType>
      <subType>sqlsvr_stats_cachehit</subType>
      <subType>sqlsvr_db_logspace</subType>
      <subType>sqlsvr_stats_cachehit</subType>
      <subType>sqlsvr_page_txnrate</subType>
      <subType>sqlsvr_stats_cachehit</subType>
      <subType>sqlsvr_locks_waittime</subType>
      <subType>sqlsvr_locks_reqrate</subType>
    </testList>
  </applicationProfile>

</NetVigil>
