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: