Access SQL - WMI Namespaces from a Remote Machine

Question

I play with PowerShell and SQL WMI events, and I wonder if I can make this material remotely from the administrator’s laptop:

I would like to query the namespace "root \ Microsoft \ SqlServer \ ServerEvents \ MSSQLSERVER":

On SQLServer directly:

get-wmiobject -list -namespace "root \ Microsoft \ SqlServer" → Works!

get-wmiobject -list -namespace "root \ Microsoft \ SqlServer \ ServerEvents \ MSSQLSERVER" → It works!

In my adminminachine:

get-wmiobject -list -namespace "root \ Microsoft \ SqlServer" → Works!

get-wmiobject -list -namespace "root \ Microsoft \ SqlServer \ ServerEvents \ MSSQLSERVER" → Error: Invalid namespace.

Is there any trick for this? Do I need to install sth additionally? I'm still on SQL 2005.

Thank!

Answer 1

Use the username name parameter.

get-wmiobject -computername Z002 -list -namespace "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"

Also remember that MSSQLSERVER is only available if the server has a default instance. If the server uses a named instance, you need to specify the instance name instead of MSSQLSERVER.

Answer 2

Published - 05/05/2012: 08:25:20

Hello everybody,

We developed a free WMI CLR assembly for executing WMI queries in SQL.

eg. This will return the mount point and disk 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)

Take a look at http://micatio.com/sqlwmi.aspx