Embedded Files
Post date: Dec 23, 2013 2:23:05 PM
The Windows Management Instrumentation (WMI) API exposes a wealth of information about PCs, including remote PCs and Servers. It is intended as a means to “automate administrative tasks on remote computers.” Here is a very short list of things WMI can do.
Start a process on a remote computer.
Schedule a process to run at specific times on specific days.
Reboot a computer remotely.
Query the Windows event logs on a local or remote computer.
Get a list from local or remote computers of:
Applications
Hardware Components
Disk Drives, Capacities and Free space
Local and Domain Users
Printers and Printer Configurations
Network Settings
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
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/
Building Your WMI Modules
Building Your WMI Modules
The inspiration for this code comes from a fantastic Google Sites resource called Beyond Excel. The example shown there is a subroutine called WMI(), which passes all of your computer’s network information to the debugging area of the Excel programming environment.
Page updated
Google Sites
Report abuse