Dave Mason shows how to retrieve WMI information (perhaps the “wrong” way):
In another post I explored a way to run WMI queries from tsql (sort of). Let’s do it again. The script below enables xp_cmdshell, runs WMIC.exe (capturing the output to a temp table), shreds the XML, returns a result set of columns and rows, and disables xp_cmdshell. If you’re adventurous and daring, run the script in a test environment.
With Powershell, as Dave mentioned, this really isn’t the best way to do the job, but there are times when you aren’t able to use Powershell.