Exposing System Secrets with WMI

With VBA, Excel can access WMI and become a powerful tool for PC Technicians, Network Administrators, and System Auditors

Want a glimpse?

Here is a small routine to list your PC's network adapter configuration. Load it into a module. Run it. Now consider this is ONLY looking at the Network Adapter. WMI can do far more than just that (scary).

Sub WMI()

Dim oWMISrvEx As Object 'SWbemServicesEx

Dim oWMIObjSet As Object 'SWbemServicesObjectSet

Dim oWMIObjEx As Object 'SWbemObjectEx

Dim oWMIProp As Object 'SWbemProperty

Dim sWQL As String 'WQL Statement

Dim n As Long 'Generic Counter

sWQL = "Select * From Win32_NetworkAdapterConfiguration"

Set oWMISrvEx = GetObject("winmgmts:root/CIMV2")

Set oWMIObjSet = oWMISrvEx.ExecQuery(sWQL)

For Each oWMIObjEx In oWMIObjSet

' Put a STOP here then View > Locals Window to see all properties

If Not IsNull(oWMIObjEx.IPAddress) Then

Debug.Print "IP:"; oWMIObjEx.IPAddress(0)

Debug.Print "Host name:"; oWMIObjEx.DNSHostName

For Each oWMIProp In oWMIObjEx.Properties_

If IsArray(oWMIProp.Value) Then

For n = LBound(oWMIProp.Value) To UBound(oWMIProp.Value)

Debug.Print oWMIProp.Name & "(" & n & ")", oWMIProp.Value(n)

Next

Else

Debug.Print oWMIProp.Name, oWMIProp.Value

End If

Next

End If

Next

End Sub

Want to see more?

Here is a workbook that facilitates exploring several hundred classes containing information about our CPU, disk drives, memory, operating system, applications, users, and much, much more. And it can query remote PCs and servers too, assuming you have the credentials.

WMI Query.xlsm: https://www.dropbox.com/s/908851y0esoqtge/WMI%20Query.xlsm?dl=1

Uses for WMI Query.xlsm

• Inventory all PCs in a network including all hardware and software using:

  • Win32_SystemEnclosure - PC's Manufacturer and Serial Number

  • Win32_LogicalDisk - Disks with capacities and free space.

  • Win32_Processor - CPU Specs

  • Win32_PhysicalMemoryArray - RAM/Installed Memory size

  • Win32_VideoController - Graphics adapter and settings

  • Win32_OnBoardDevice - Motherboard devices

  • Win32_OperatingSystem - Which version of Windows with Serial Number

  • WIn32_Printer - Installed Printers

  • Win32_Product - Installed Software

• WIn32_Account - List all User Accounts on a PC or Domain

• Win32_ComputerSystem - See who is currently using a remote PC (also Win32_LoggedOnUser)

• Win32_BaseService - List services running (or stopped) on any PC along with the service's path and file name.

• And hundreds more!

Read the references below to learn more about what this API can provide.

Introductions and Primers:

Introduction to Scripting: http://technet.microsoft.com/en-us/scriptcenter/dd940112.aspx

Sesame Script: http://technet.microsoft.com/en-us/library/ee176991.aspx

WMI Introduction: http://msdn.microsoft.com/en-us/library/aa394582(v=vs.85).aspx

ADSI Scripting Primer: http://technet.microsoft.com/library/ee156524.aspx

Scripting Guys: http://blogs.technet.com/b/heyscriptingguy/archive/2005/06/28/how-can-i-list-all-the-attributes-used-by-the-computer-class-in-active-directory.aspx

References:

WMI Scripting Primer: http://technet.microsoft.com/library/ee156560.aspx

WMI Introduction: http://msdn.microsoft.com/en-us/library/aa394582(v=vs.85).aspx

WMI Reference: http://msdn.microsoft.com/en-us/library/aa394572(v=vs.85).aspx

WMI Win32 Classes: http://msdn.microsoft.com/en-us/library/aa394084(v=vs.85).aspx

WMI Scripting API Objects: http://msdn.microsoft.com/en-us/library/aa393259(v=vs.85).aspx

WMI ExecQuery method: http://msdn.microsoft.com/en-us/library/aa393866(v=vs.85).aspx

WMI NetworkAdapterConfiguration Class Properties http://msdn.microsoft.com/en-us/library/aa394217(v=vs.85).aspx

Script Examples:

Dan Elgaard's examples: http://www.excelgaard.dk/Lib/USERINFO/

WMI Script Examples: http://msdn.microsoft.com/en-us/library/aa394585(v=vs.85).aspx

WMI Query by Example: http://www.codeproject.com/Articles/46390/WMI-Query-Language-by-Example

Technet Gallery: http://gallery.technet.microsoft.com/scriptcenter/b03c3611-36eb-4979-baf1-432e53c43f93

Active Directory: http://www.billrowell.com/2009/09/10/properties-in-a-active-directory-computer-object/

NOTE! See what others are saying about this.

From: http://www.makeuseof.com/tag/see-pc-information-using-simple-excel-vba-script/