WMI Assembly for Microsoft SQL Server

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

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:

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)