2015-12-14

WMI Queries From Within SQL Server...Sort Of

Have you ever had the need to run a WMI query from within the context of a SQL Server connection? It would be really cool to run this WMI query as if it were an actual T-SQL query and get a corresponding result set:

SELECT * FROM Win32_Process


As far as I know, there is no way to do this directly from T-SQL. To run WMI queries, you need to leverage the WMI infrastructure, which is typically done from a .Net Framework application, a script language, etc. However, you can access WMI indirectly from T-SQL. There are probably numerous ways to do this, in fact. In this post we will examine the use of WMIC.exe, a command line tool for WMI.


Before we get to far, a few words about xp_cmdshell. I am neither advocating nor decrying the enabling and usage of an extended stored procedure. This post is merely an exercise to show what is possible for a particular use case. Is it ok to enable xp_cmdshell? Decide for yourself. If it's not already enabled, enable xp_cmdshell. We'll run WMIC.exe as follows, and capture the output to a temp table.


CREATE TABLE #WMIC (
    ID INT IDENTITY PRIMARY KEY,
    CmdOutput VARCHAR(1000)
)

INSERT INTO #WMIC (CmdOutput)
EXECUTE master..xp_cmdshell 'WMIC.exe process get description,executablepath,handle,caption'

SELECT * FROM #WMIC

This sort of works. We requested four fields from Win32_Process (aka "process" in the WMIC command line), but the output of xp_cmdshell gives us all four fields in one column of the temp table. If you reduced the WMIC call to just a single field, the above method may be sufficient. Otherwise, this is problematic--without doing some unwieldy parsing, the data isn't of much use. An option to facilitate the parsing is to have WMIC produce a CSV output. However, if there are commas within the field data, this will hamper your parsing efforts. (Also, note that the first row is a header row of field names and the last row is white space. You can suppress the header row with a parameter to WMIC, but the last row of white space remains.) Here is the CSV output option:

EXECUTE master..xp_cmdshell 'WMIC.exe process get description,executablepath,handle,caption /FORMAT:CSV'


XML - A Better Option

Although I'm not particularly fond of working with XML data, I've found it's quite effective (and appropriate) in this situation. WMIC has an option to output to raw XML. Run this command to get a sense of what the "XML data" looks like:

EXECUTE master..xp_cmdshell 'WMIC.exe process get description,executablepath,handle,caption /FORMAT:RAWXML'

Now we'll take that "XML data" and cast it to strongly typed, actual XML data, and shred it:

CREATE TABLE #WMIC (
    ID INT IDENTITY PRIMARY KEY,
    CmdOutput VARCHAR(1000)
)

INSERT INTO #WMIC (CmdOutput)
EXECUTE master..xp_cmdshell 'WMIC.exe process get description,executablepath,handle,caption /FORMAT:RAWXML'

DECLARE @Cmd VARCHAR(MAX) = ''
DECLARE @Xml XML

SELECT @Cmd = @Cmd + COALESCE(w.CmdOutput, '') 
FROM #WMIC w
ORDER BY w.ID

SET @Xml = CAST(@Cmd AS XML)

SELECT
    Description = Process.value('(PROPERTY[@NAME="Description"]/VALUE)[1]', 'VARCHAR(100)'),
    ExecutablePath = Process.value('(PROPERTY[@NAME="ExecutablePath"]/VALUE)[1]', 'VARCHAR(512)'),
    Handle = Process.value('(PROPERTY[@NAME="Handle"]/VALUE)[1]', 'INT'),
    Caption = Process.value('(PROPERTY[@NAME="Caption"]/VALUE)[1]', 'VARCHAR(100)')
FROM @Xml.nodes('/COMMAND/RESULTS/CIM/INSTANCE') AS WmiTbl(Process)

DROP TABLE #WMIC


We get back strongly typed data in our result set, with one column for each field of the Win32_Process class. There's no phantom row of field names, nor are there additional rows of whitespace.


NOTES:
Don't forget to disable xp_cmdshell when you're finished!
Tested on SQL Server 2012 SP2 running on Windows 7.
Also tested on SQL Server 2008 R2 SP3 running on Windows 2008 R2.
Additional considerations are needed for WMI queries on remote servers. See MSDN documentation.


SHARE