A generic WMI perfmon CLR procedure

--Enable CLR

exec sp_configure 'clr enabled', 1

reconfigure with override

 

--Enable Trustworthy computing

--This allows us to create unsupported assemblies

alter database DemoDB

set trustworthyon

 

use master

go

GRANT unsafe ASSEMBLY TO [WIN2K3R2EE\Administrator]

ALTER AUTHORIZATION on database::demodb to [WIN2K3R2EE\Administrator]

 

--This assembly allows us to use WMI in CLR functions and procs.

use DemoDB

go

CREATE ASSEMBLY [System.Management]

AUTHORIZATION [dbo]

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'

WITH PERMISSION_SET = UNSAFE

 Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports System.Management

Imports Microsoft.SqlServer.Server

 

 

Partial Public Class StoredProcedures

 _

Public Shared Sub pnetWMI(ByVal sComputer As String, ByVal sWQL As String _ 
, ByVal sCounter As String)

Try

 

 

 Dim searcher As New ManagementObjectSearcher( _

 "\\" & sComputer & "\root\CIMV2", _

                sWQL)

 For Each queryObj As ManagementObject In searcher.Get()

 

 Dim record As New SqlDataRecord( _

 New SqlMetaData(sCounter, SqlDbType.VarChar, 100))

 

                SqlContext.Pipe.SendResultsStart(record)

 

                record.SetString(0, queryObj(sCounter))

                SqlContext.Pipe.SendResultsRow(record)

 Next

            SqlContext.Pipe.SendResultsEnd()

Catch ex As Exception

 Dim sp As SqlPipe = SqlContext.Pipe()

            sp.Send(ex.Message)

End Try

End Sub

End Class