Bulk Insert WMIC output to table w/ xp_cmdshell

,

This is an example of retrieving WMIC results using the xp_cmdshell proc, and inserting the results into a table.

Specifically, this script retrieves disk info from the LogicalDisk class, but WMIC provides thousands of other properties. WMIC does require it be ran from a Windows 2003 server, but can query other Windows 2000 servers when executed from a W2003 server.

NOTE: If WMIC hasn't been run on the server before, it'll do a compile on its first run. I recommend doing a manual execution of WMIC first before using this script, so you don't insert an extraneous output into the temp table

This example lists the available alias' on the system (e.g. WMIC alias list brief)

WMIC Reference:
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/wmic_overview.mspx?mfr=true

/*
	Description:
	This is an example of retrieving WMIC results using the xp_cmdshell proc, 
	and inserting the results into a table.

	Specifically, this script retrieves disk info from the LogicalDisk class, 
	but WMIC provides thousands of other properties.  WMIC does require it be 
	ran from a Windows 2003 server, but can query other Windows 2000 servers 
	when executed from a W2003 server.

	NOTE: If WMIC hasn't been run on the server before, it'll do a compile on 
	its first run.  I recommend doing a manual execution of WMIC first before 
	using this script, so you don't insert an extraneous output into the temp 
	table

	This example lists the available alias' on the system  
		(e.g. WMIC alias list brief)

	WMIC Reference:
	http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/wmic_overview.mspx?mfr=true


	Author: Thuan Trang (7/2/2006)
*/

set nocount on

-- ** Create temporary tables
if object_id('tempdb.dbo.#tbl_cmdoutput') is not null
	drop table #tbl_cmdoutput

create table #tbl_cmdoutput(
	cmdout varchar(8000)
)

/*
	This table must match column output of the WMIC command below.

	Note UNICODE output of WMIC results
*/
if object_id('tempdb.dbo.#tbl_diskprops') is not null
	drop table #tbl_diskprops

create table #tbl_diskprops(
	node nvarchar(128),
	deviceid nvarchar(128),
	freespace nvarchar(128),
	size nvarchar(128)
)

-- ** Variable declarations

declare @wmic_tmpfile varchar(1000)
declare @cmd varchar(8000)
declare @sql varchar(8000)

/*
	This step isn't a strict req't for the goal of this script, but the purpose 
	is to locate a temporary file for the WMIC output, and my preference was not 
	to  hard code and disk letters into the filename
*/

-- ** Expand the %temp% environment variable
insert #tbl_cmdoutput exec master.dbo.xp_cmdshell 'echo "%temp%\temp___.csv"'

set @wmic_tmpfile = (
		select top 1 cmdout from #tbl_cmdoutput 
		where cmdout is not null
	)

set @cmd = 'wmic logicaldisk get deviceid,freespace,size /format:csv > ' 
	+ @wmic_tmpfile

/*
	A little dynamic SQL is required here because of a variable was used for the 
	BULK INSERT statement
*/
set @sql = 'bulk insert #tbl_diskprops from ' + @wmic_tmpfile + 
	'with (
		datafiletype = ''widechar'',
		firstrow = 2,
		fieldterminator = '','',
		rowterminator = ''\n'',
		keepnulls
	)'

-- ** Execute the WMIC command and temporarily store output to the CSV file
exec master.dbo.xp_cmdshell @cmd, no_output

-- ** Execute BULK INSERT, retrieving the contents of the file into the temp 
-- ** table
exec(@sql)

-- ** Cleanup the temporary file
set @cmd = 'del /Q ' + @wmic_tmpfile
exec master.dbo.xp_cmdshell @cmd, no_output

select * from #tbl_diskprops
set nocount off

Rate

2 (1)

Share

Share

Rate

2 (1)