Excel Automation

The Ultimate Server Audit: Deep-Dive Inventory to Excel (VBScript) | Lazy Admin Blog

Posted on March 22, 2026 Updated on April 15, 2026

If you are facing a massive compliance audit or a data center migration, “basic” info isn’t enough. You need to know exactly what is under the hood: What roles are active? How much disk space is actually left? What random software was installed three years ago?

This VBScript is a one-stop-shop. It checks network connectivity and then scrapes WMI and the Registry to build a massive, multi-column Excel report.

What this Script Collects:

Hardware: Manufacturer, Model, CPU Type, and RAM (converted to GB).
OS Details: Version, Caption, and the exact Installation Date.
Storage: Total Size vs. Free Space (with a Red-Alert highlight if space is < 20%).
Network: DHCP status, IP, Subnet, and Gateway.
Software & Roles: Every Windows Server Role/Feature and every application listed in the Registry’s Uninstall key (including version and install date).

Preparation

Directory: Create C:\Temp on your local machine.
Input: Create a file named ServerList.txt in C:\Temp with your server names (one per line).
Excel: Ensure Microsoft Excel is installed.

The Script: Server_Inventory.vbs

' Save as Server_Inventory.vbs in C:\Temp
' lazyadminblog.com - Ultimate Inventory Script
On Error Resume Next 
dtmDate = Date
strMonth = Month(Date)
strDay = Day(Date)
strYear = Right(Year(Date),2)
strFileName = "C:\Temp\ServerInventory_" & strMonth & "-" & strDay & "-" & strYear & ".xls"
Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 
objExcel.Workbooks.Add 
Set fso1 = CreateObject("Scripting.FileSystemObject") 
Set pcfile = fso1.OpenTextFile("C:\Temp\ServerList.txt",1) 
Wscript.Echo "Audit in progress... Please wait!"
'--- Setup Header Row ---
Sub SetupHeader(col, text)
    objExcel.Cells(1, col).Value = text 
    objExcel.Cells(1, col).Font.Colorindex = 2
    objExcel.Cells(1, col).Font.Bold = True 
    objExcel.Cells(1, col).Interior.ColorIndex = 23 
    objExcel.Cells(1, col).Alignment = -4108 ' Center
End Sub
SetupHeader 1, "Computer Name"
SetupHeader 2, "Manufacturer"
SetupHeader 3, "Model"
SetupHeader 4, "RAM (GB)"
SetupHeader 5, "Operating System"
SetupHeader 6, "Installed Date"
SetupHeader 7, "Processor"
SetupHeader 8, "Drive"
SetupHeader 9, "Drive Size (GB)"
SetupHeader 10, "Free Space (GB)"
SetupHeader 11, "Adapter Description"
SetupHeader 12, "DHCP Enabled"
SetupHeader 13, "IP Address"
SetupHeader 14, "Subnet"
SetupHeader 15, "Gateway"
SetupHeader 16, "Roles & Features"
SetupHeader 17, "Installed Software"
SetupHeader 18, "Install Date"
SetupHeader 19, "Version"
SetupHeader 20, "Size"
y = 2 
Do While Not pcfile.AtEndOfStream 
    computerName = pcfile.ReadLine 
    Err.Clear 
    Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & computerName & "\root\cimv2") 
    
    If Err.Number = 0 Then 
        ' Fetch Queries
        Set colSettings = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem") 
        Set colOSSettings = objWMIService.ExecQuery("SELECT * FROM Win32_OperatingSystem")
        Set colProcSettings = objWMIService.ExecQuery("SELECT * FROM Win32_Processor")
        Set colDiskSettings = objWMIService.ExecQuery("Select * from Win32_LogicalDisk Where DriveType=3")
        Set colAdapters = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
        
        For Each objComputer In colSettings 
            strManufacturer = objComputer.Manufacturer 
            strModel = objComputer.Model 
            strRAM = FormatNumber((objComputer.TotalPhysicalMemory / (1024^3)), 2)
        
            For Each objOS In colOSSettings
                strOS = objOS.Caption
                OSinstDate = CDate(Mid(objOS.InstallDate,1,4)+"/"+Mid(objOS.InstallDate,5,2)+"/"+Mid(objOS.InstallDate,7,2))
        
                For Each objProc In colProcSettings
                    strProc = objProc.Name
                    
                    ' Populate Static Info
                    objExcel.Cells(y, 1).Value = computerName 
                    objExcel.Cells(y, 2).Value = strManufacturer 
                    objExcel.Cells(y, 3).Value = strModel 
                    objExcel.Cells(y, 4).Value = strRAM 
                    objExcel.Cells(y, 5).Value = strOS 
                    objExcel.Cells(y, 6).Value = OSinstDate
                    objExcel.Cells(y, 7).Value = strProc 
                    ' Drive Logic
                    a = y
                    For Each objDisk In colDiskSettings
                        objExcel.Cells(a, 8).Value = objDisk.DeviceID
                        sz = objDisk.Size / (1024^3)
                        fr = objDisk.FreeSpace / (1024^3)
                        objExcel.Cells(a, 9).Value = FormatNumber(sz, 2)
                        objExcel.Cells(a, 10).Value = FormatNumber(fr, 2)
                        If fr < (sz * 0.2) Then objExcel.Cells(a, 10).Interior.ColorIndex = 3 ' Low Space Alert
                        a = a + 1
                    Next  
                    ' Network Logic
                    b = y
                    For Each objAdapter In colAdapters
                        objExcel.Cells(b, 11).Value = objAdapter.Description
                        objExcel.Cells(b, 12).Value = objAdapter.DHCPEnabled
                        If Not IsNull(objAdapter.IPAddress) Then objExcel.Cells(b, 13).Value = objAdapter.IPAddress(0)
                        If Not IsNull(objAdapter.IPSubnet) Then objExcel.Cells(b, 14).Value = objAdapter.IPSubnet(0)
                        If Not IsNull(objAdapter.DefaultIPGateway) Then objExcel.Cells(b, 15).Value = objAdapter.DefaultIPGateway(0)
                        b = b + 1
                    Next
                    ' Roles & Features
                    x = y
                    Set colRoleFeatures = objWMIService.ExecQuery("Select * from Win32_ServerFeature")
                    If colRoleFeatures.Count > 0 Then
                        For Each objRole In colRoleFeatures
                            objExcel.Cells(x, 16).Value = objRole.Name
                            x = x + 1
                        Next
                    Else
                        objExcel.Cells(x, 16).Value = "None Found"
                    End If
                    ' Software Registry Scan
                    s = y
                    Const HKLM = &H80000002
                    strKey = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\"
                    Set objReg = GetObject("winmgmts://" & computerName & "/root/default:StdRegProv")
                    objReg.EnumKey HKLM, strKey, arrSubkeys
                    For Each strSubkey In arrSubkeys
                        objReg.GetStringValue HKLM, strKey & strSubkey, "DisplayName", strVal1
                        If strVal1 <> "" Then
                            objExcel.Cells(s, 17).Value = strVal1
                            objReg.GetStringValue HKLM, strKey & strSubkey, "InstallDate", strVal2
                            objExcel.Cells(s, 18).Value = strVal2
                            objReg.GetDWORDValue HKLM, strKey & strSubkey, "VersionMajor", vMaj
                            objReg.GetDWORDValue HKLM, strKey & strSubkey, "VersionMinor", vMin
                            objExcel.Cells(s, 19).Value = vMaj & "." & vMin
                            s = s + 1
                        End If
                    Next
                    ' Advance Row to next available empty spot
                    y = a
                    If b > y Then y = b
                    If x > y Then y = x
                    If s > y Then y = s
                    y = y + 1 ' Buffer line
                Next
            Next
        Next
    Else 
        objExcel.Cells(y, 1).Value = computerName 
        objExcel.Cells(y, 2).Value = "OFFLINE" 
        objExcel.Cells(y, 2).Interior.ColorIndex = 3
        y = y + 1 
    End If 
Loop 
' Final Formatting
For col = 1 To 20: objExcel.Columns(col).AutoFit: Next
objExcel.ActiveWorkbook.SaveAs strFileName
Wscript.Echo "Complete! Report saved to " & strFileName

Why it’s a Game Changer

The “Red Flag” Feature: It automatically highlights any disk with less than 20% free space in Red. This instantly tells you which servers need urgent cleanup.
Software Archeology: Most scripts skip software lists because they are messy. This script pulls directly from the Uninstall registry keys, capturing even the apps that don’t show up in standard WMI queries.
Intelligent Row Management: Because software, roles, and disks all have different counts, the script calculates the “max row” used for each server and jumps to the next clear space for the next machine.

This entry was posted in Active Directory, Scripts and tagged Capacity Planning, Excel Automation, Infrastructure Management, IT Audit, Server Inventory, Software Asset Management, SysAdmin Tools, VBScript, Windows Registry, WMI.

The Bulk-Replace Macro & Decoding the SID Matrix | Lazy Admin Blog

Posted on March 13, 2026 Updated on March 23, 2026

Why manually edit 1,000 rows when a 10-line script can do it for you?

As an admin, you’re constantly dealing with data. Sometimes it’s a list of server names in Excel that need updating, and other times it’s a cryptic string of numbers in a security log. Today, we’re tackling both.

1. Excel Bulk-Replace: The “Set and Forget” Macro

We’ve all been there: You have a list of old server names and a list of new ones. Running Ctrl+H fifty times is not the “Lazy Admin” way. Instead, use this VBA macro to map an entire range of changes in one go.

How to use it:

Open your Excel sheet and hit Alt + F11 to open the VBA Editor.
Go to Insert > Module and paste the code below.
Hit F5 to run.
Select the Original Range: The data you want to change.
Select the Replace Range: A two-column list where Column A is the “Find” and Column B is the “Replace.”

Sub MultiFindNReplace()
' The Lazy Admin's Bulk Tool
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "LazyAdminReplace"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range to search in:", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Mapping Range (Col A: Old, Col B: New):", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
    InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole
Next
Application.ScreenUpdating = True
End Sub

2. Decoding the SID: Who is ‘S-1-5-21…’?

When you see a SID (Security Identifier) in a log, it’s not just a random string. It’s a structured ID that tells you exactly where that user came from.

The Anatomy of a SID:

S: Identifies this as a SID.
1: The revision number (still at revision 1).
5: The Identifier Authority. ‘5’ means NT Authority (Standard Windows accounts).
21: Specifies that the following sub-authorities identify a Domain or Local Machine.
1000+: The RID (Relative Identifier). Any user-created object starts at 1000. 500 is always the built-in Administrator.

Quick Lookup Commands:

Need to find the name behind a SID right now? Use these:

Command Prompt (WMIC):

VBScript

wmic useraccount where sid='S-1-5-21-xxxx' get name

PowerShell (AD Module):

VBScript

Get-ADGroup -Identity S-1-5-32-544

(This specific one is the local Administrators group!)

🛡️ Lazy Admin Verdict:

Keep a “Mapping Table” in a separate Excel tab for all your bulk naming changes. Use the macro to apply them to your master inventory. For SIDs, memorize the “5-21” part—it’s the most common string you’ll see in enterprise environments.

This entry was posted in Excel, Scripts and tagged Decode SID, Excel Automation, Excel VBA Bulk Replace, MultiFindNReplace Macro, NT Authority 21, PowerShell Get-ADGroup SID, RID 1000, SysAdmin Excel Tips, Windows SID Structure, WMIC Get User by SID.