Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 WMI queries in SQL

Author  Topic 

rapitts
Starting Member

1 Post

Posted - 2012-05-05 : 08:25:20
Hi All,

We've developed a free WMI CLR assembly to execute WMI queries in SQL.

e.g. This will return mount point and drive space

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)

Have a look at [url]http://micatio.com/sqlwmi.aspx[/url]
   

- Advertisement -