Introduction

Investigating how to obtain windows management instrumentation information (WMI) data from within SQL server?
Stuck using PowerShell for your current solution and want something more integrated within Microsoft SQL Server?

Well you've come to the right site, Microsoft created SQLCLR to cover such a requirement.
SQLCLR is a safe component  and really misunderstood within the SQL community, compared to running adhoc code powershell, OLE objects or VBScript code its best suited for such a task, even some built in functions in SQL can run faster if rewritten using SQLCLR.

 

Download

Latest Version 1.01 - Aug 2012
New Features: Improved error handling and logging.
Installation (SQL 2005/2008/2008 R2) 32 bit	click here to download
Installation (SQL 2005/2008/2008 R2) 64 bit	click here to download
Version 1.0 - Pre Aug 2012
Installation (SQL 2005/2008/2008 R2) 32 bit	click here to download
Installation (SQL 2005/2008/2008 R2) 64 bit	click here to download

 

List Namespaces on Remote Machine

     
            DECLARE @XmlData Xml

            --Obtain Registered NameSpaces 
            select @XmlData=dbo.GetWMI('\\SQL2008WIN2008\root',
						   NULL,   --UserName, leave NULL to use current
						   NULL,   --Password, leave NULL to use current 
						   'select * from __namespace'  --WMI Object
							)

            SELECT tbl.A.value('(Name)[1]','VARCHAR(400)') as Caption
            FROM   @XmlData.nodes('/WMI/Data') tbl(A)

     

Using WMI to replace xp_fixeddrive

 xp_fixeddrives will fail to return any information regarding mount points however the WMI win32_volume class can help.

          DECLARE @XmlData Xml
                --Obtain Windows  Services 
                select @XmlData=dbo.GetWMI('\\SQL2008WIN2008\root\cimv2',  --Machine and WMI class
						                   NULL,   --UserName, leave NULL to use current
						                   NULL,   --Password, leave NULL to use current 
						                   'select * from win32_volume'  --WMI Class
							                )

                SELECT 
                tbl.A.value('(DeviceID)[1]','VARCHAR(100)') as DeviceID,
                tbl.A.value('(Name)[1]','VARCHAR(200)') as Name,
                tbl.A.value('(DriveType)[1]','int') as DriveType,
                ISNULL(tbl.A.value('(DriveLetter)[1]','VARCHAR(10)'),'MountPoint') as DriveLetter,
                tbl.A.value('(FreeSpace)[1]','bigint')/1024/1024 as FreeSpaceMbytes
                FROM   @XmlData.nodes('/WMI/Data') tbl(A)
            

Will return the following:

DeviceID	Name	DriveType	DriveLetter	FreeSpaceMbytes
\\\\?\\Volume{c5f6ee1f-085a-11e1-b3d4-806e6f6e6963}\\	C:\\	3	C:	47473
\\\\?\\Volume{b3f146bc-8dfe-11e1-b4c7-00155d021819}\\	E:\\	3	E:	877
\\\\?\\Volume{b3f146ca-8dfe-11e1-b4c7-00155d021819}\\	E:\\Data\\Data2GB\\	3	MountPoint	1959

Performance Data via WMI

  DECLARE @XmlData Xml
 DECLARE @Machine varchar(100)
 SET @Machine='\\DEVPC\root\cimv2'
                --Obtain Windows  Services 
                select @XmlData=dbo.GetWMI(@Machine,  --Machine and WMI class
						                   NULL,   --UserName, leave NULL to use current
						                   NULL,   --Password, leave NULL to use current 
						                   'SELECT * FROM Win32_PerfFormattedData_MSSQLSERVER_SQLServerMemoryManager'  --WMI Class
							                )

                SELECT 
                tbl.A.value('(ConnectionMemoryKB)[1]','BIGINT') as ConnectionMemoryKB,
                tbl.A.value('(MaximumWorkspaceMemoryKB)[1]','BIGINT') as MaximumWorkspaceMemoryKB,
                tbl.A.value('(OptimizerMemoryKB)[1]','BIGINT') as OptimizerMemoryKB,
				tbl.A.value('(TargetServerMemoryKB)[1]','BIGINT') as TargetServerMemoryKB,
				tbl.A.value('(TotalServerMemoryKB)[1]','BIGINT') as TotalServerMemoryKB
                FROM   @XmlData.nodes('/WMI/Data') tbl(A)