
Applies to SQL Server 2019, 2022 and 2025. The script runs in Windows PowerShell 5.1 or PowerShell 7.
A server inventory is the list every other report hangs off: which servers exist, what Windows and SQL Server they run, how big they are and when they last restarted. If you can’t produce it in five minutes, every audit, licensing review and patching plan starts with a week of spreadsheet archaeology.
Mine takes a snapshot of every server once a day and keeps the history, so it also tells you what changed and when.
What the report shows
- Windows. Operating system name, version and the last boot time.
- Hardware. Manufacturer and model, which is the quickest way to tell a VM from a physical box, plus logical CPUs and memory.
- SQL Server. Instance name, edition, version, service pack level, cumulative update level and its KB number, and server collation.
- Configuration. Whether it’s clustered, whether Always On is enabled, and the max server memory setting.
- When SQL Server last started, which is not always the same as when Windows did.
Why it matters
Almost every question a manager asks about the database estate is an inventory question. How many servers are on SQL Server 2016? Which ones are physical? How much memory have we bought? Which ones haven’t been rebooted since the last patch cycle?
Keeping one row per day, rather than overwriting, means the history answers “when did this change?” too. If a CU went on a server in the middle of the night, or someone halved the memory on a VM, it’s in the table.
It also tests access. If the service account can’t connect to a server, the row says FAILED with the reason. That’s often the first sign a server has been rebuilt, renamed or moved behind a new firewall.
The script
It reads the Windows facts through CIM and the SQL Server facts from SERVERPROPERTY and sys.dm_os_sys_info, then writes one row to dbo.ServerInventory. The table is created on the first run.
# Invoke-ServerInventory.ps1
# One row per server per run: Windows version and last boot, hardware,
# SQL Server edition, version and patch level, CPUs and memory.
# Schedule it daily.
# ---- Settings ---------------------------------------------------------------
$ServerListPath = 'C:\DBA\Servers.txt'
$InventoryServer = 'SQLMON01'
$InventoryDatabase = 'DBA'
# -----------------------------------------------------------------------------
. "$PSScriptRoot\DbaReportHelpers.ps1"
$SqlQuery = @"
SELECT
CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)) AS InstanceName,
CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128)) AS SQLEdition,
CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(32)) AS SQLVersion,
CAST(SERVERPROPERTY('ProductLevel') AS NVARCHAR(32)) AS ProductLevel,
CAST(SERVERPROPERTY('ProductUpdateLevel') AS NVARCHAR(32)) AS ProductUpdateLevel,
CAST(SERVERPROPERTY('ProductUpdateReference') AS NVARCHAR(32)) AS ProductUpdateReference,
CAST(SERVERPROPERTY('Collation') AS NVARCHAR(128)) AS ServerCollation,
CAST(SERVERPROPERTY('IsClustered') AS BIT) AS IsClustered,
CAST(SERVERPROPERTY('IsHadrEnabled') AS BIT) AS IsHadrEnabled,
i.cpu_count AS LogicalCPUs,
CAST(i.physical_memory_kb / 1048576.0 AS DECIMAL(18,2)) AS RAM_GB,
(SELECT CAST(value_in_use AS INT) FROM sys.configurations
WHERE name = 'max server memory (MB)') AS MaxServerMemoryMB,
i.sqlserver_start_time AS SQLStartTime,
i.virtual_machine_type_desc AS VirtualMachineType
FROM sys.dm_os_sys_info AS i;
"@
$Servers = Get-ServerList -Path $ServerListPath
$Repo = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase
try {
Invoke-SqlNonQuery $Repo @"
IF OBJECT_ID('dbo.ServerInventory', 'U') IS NULL
CREATE TABLE dbo.ServerInventory
(
CaptureDate DATETIME2(0) NOT NULL
CONSTRAINT DF_ServerInventory_CaptureDate DEFAULT SYSDATETIME(),
ServerName NVARCHAR(128) NOT NULL,
InstanceName NVARCHAR(128) NULL,
OSName NVARCHAR(256) NULL,
OSVersion NVARCHAR(64) NULL,
LastBootTime DATETIME2(0) NULL,
Manufacturer NVARCHAR(128) NULL,
Model NVARCHAR(128) NULL,
SQLEdition NVARCHAR(128) NULL,
SQLVersion NVARCHAR(32) NULL,
ProductLevel NVARCHAR(32) NULL,
ProductUpdateLevel NVARCHAR(32) NULL,
ProductUpdateReference NVARCHAR(32) NULL,
ServerCollation NVARCHAR(128) NULL,
IsClustered BIT NULL,
IsHadrEnabled BIT NULL,
LogicalCPUs INT NULL,
RAM_GB DECIMAL(18,2) NULL,
MaxServerMemoryMB INT NULL,
SQLStartTime DATETIME2(0) NULL,
VirtualMachineType NVARCHAR(60) NULL,
CollectionStatus VARCHAR(20) NOT NULL,
ErrorMessage NVARCHAR(MAX) NULL
);
"@
foreach ($Server in $Servers) {
$Row = [ordered]@{ ServerName = $Server; CollectionStatus = 'SUCCESS' }
try {
# Windows side (needs WinRM, or swap in a DCOM CIM session)
$OS = Get-CimInstance -ClassName Win32_OperatingSystem -ComputerName (Get-ComputerName $Server) -OperationTimeoutSec 60 -ErrorAction Stop
$CS = Get-CimInstance -ClassName Win32_ComputerSystem -ComputerName (Get-ComputerName $Server) -OperationTimeoutSec 60 -ErrorAction Stop
$Row.OSName = $OS.Caption
$Row.OSVersion = $OS.Version
$Row.LastBootTime = $OS.LastBootUpTime
$Row.Manufacturer = $CS.Manufacturer
$Row.Model = $CS.Model
# SQL Server side
$Source = Open-SqlConnection -Server $Server
try {
$Sql = (Invoke-SqlQuery $Source $SqlQuery).Rows[0]
}
finally {
$Source.Close()
}
foreach ($Column in $Sql.Table.Columns.ColumnName) {
$Row[$Column] = $Sql[$Column]
}
Write-Host "$Server : $($Row.SQLEdition) $($Row.SQLVersion) $($Row.ProductUpdateLevel)"
}
catch {
$Row.CollectionStatus = 'FAILED'
$Row.ErrorMessage = $_.Exception.Message
Write-Warning "$Server : $($Row.ErrorMessage)"
}
Add-InventoryRow -Connection $Repo -Table 'dbo.ServerInventory' -Row $Row
}
}
finally {
$Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script)
# DbaReportHelpers.ps1
# Shared helpers for the auditing scripts. Save it next to them and dot-source it:
# . "$PSScriptRoot\DbaReportHelpers.ps1"
# Windows Authentication throughout: run the scripts as a service account that
# can reach every server. No passwords live in any of these files.
function Get-ServerList {
# One server per line. Blank lines and anything after # are ignored,
# and duplicates (in any case) are removed.
param([Parameter(Mandatory)][string]$Path)
Get-Content -Path $Path |
ForEach-Object { ($_ -split '#')[0].Trim() } |
Where-Object { $_ } |
Sort-Object -Unique
}
function Get-ComputerName {
# SERVER\INSTANCE -> SERVER, for the Windows calls (CIM, ping, remoting)
param([Parameter(Mandatory)][string]$Server)
($Server -split '\\')[0]
}
function Open-SqlConnection {
param(
[Parameter(Mandatory)][string]$Server,
[string]$Database = 'master'
)
$Conn = New-Object System.Data.SqlClient.SqlConnection
$Conn.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" +
"TrustServerCertificate=True;Connect Timeout=15;Application Name=DBA reports"
$Conn.Open()
$Conn
}
function Invoke-SqlQuery {
# Runs a query and returns a DataTable (the leading comma stops PowerShell unrolling it).
param($Connection, [string]$Sql, [hashtable]$Parameters = @{})
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 300
foreach ($Name in $Parameters.Keys) {
$null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
}
$Table = New-Object System.Data.DataTable
$null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Table)
, $Table
}
function Invoke-SqlNonQuery {
param($Connection, [string]$Sql)
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 300
$null = $Cmd.ExecuteNonQuery()
}
function Add-InventoryRow {
# Inserts one row from an ordered hashtable of column = value.
# Every value goes in as a parameter, never pasted into the SQL text.
param(
$Connection,
[Parameter(Mandatory)][string]$Table,
[Parameter(Mandatory)][System.Collections.IDictionary]$Row
)
$Columns = @($Row.Keys)
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = "INSERT INTO $Table (" + ($Columns -join ', ') + ') VALUES (' +
(($Columns | ForEach-Object { "@$_" }) -join ', ') + ');'
foreach ($Column in $Columns) {
$Value = $Row[$Column]
if ($null -eq $Value -or $Value -is [System.DBNull] -or
($Value -is [string] -and $Value.Trim() -eq '')) {
$Value = [System.DBNull]::Value
}
$null = $Cmd.Parameters.AddWithValue("@$Column", $Value)
}
$null = $Cmd.ExecuteNonQuery()
}
The report query
This picks the latest row for each server and adds a list of flags: failed collection, no data for two days, max server memory never set, no reboot for 45 days, and an instance name that doesn’t match the server name.
-- Latest inventory row per server, with the things I want to see flagged.
WITH Latest AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY CaptureDate DESC) AS RN
FROM dbo.ServerInventory
)
SELECT
ServerName,
InstanceName,
OSName,
SQLEdition,
SQLVersion,
ProductUpdateLevel,
LogicalCPUs,
RAM_GB,
MaxServerMemoryMB,
VirtualMachineType,
DATEDIFF(DAY, LastBootTime, SYSDATETIME()) AS DaysSinceBoot,
CONCAT_WS(', ',
CASE WHEN CollectionStatus = 'FAILED' THEN 'Collection failed: ' + LEFT(ErrorMessage, 100) END,
CASE WHEN CaptureDate < DATEADD(DAY, -2, SYSDATETIME()) THEN 'No data for 2 days' END,
CASE WHEN MaxServerMemoryMB = 2147483647 THEN 'Max server memory not set' END,
CASE WHEN DATEDIFF(DAY, LastBootTime, SYSDATETIME()) > 45 THEN 'Not rebooted (patched?) for 45+ days' END,
CASE WHEN InstanceName <> ServerName
AND InstanceName NOT LIKE ServerName + '\%' THEN 'Instance name differs from server name' END
) AS Flags,
CaptureDate
FROM Latest
WHERE RN = 1
ORDER BY ServerName;
Scheduling it
Once a day is plenty. I run it at 06:00 so the report is ready before anyone asks. The rows are small, so a year of history for a hundred servers is a few megabytes.
Watch out for
- Named instances. The server list holds what you connect to, so a named instance is
SERVER\INSTANCE. The Windows calls only need the computer name, andGet-ComputerNamein the helper file strips the instance part. Two instances on one server give two rows with the same Windows details, which is right for SQL Server and double-counts for hardware. - Aliases and listeners. If the list holds a DNS alias or an AG listener, the instance name won’t match. The flag is a prompt to check, not an error.
- ProductUpdateLevel is NULL on an RTM build or a GDR-only build. That’s correct: it means no CU is installed.
- Max server memory of 2147483647 means it’s never been set. SQL Server will take nearly all the memory and leave Windows short.
- The server list is the inventory’s weak point. A server that isn’t on it doesn’t exist as far as these reports are concerned. Reconcile it against Active Directory or your CMDB every quarter.
Where it fits
This is part of my Auditing section. The licensing, availability, disk space, database, backup and SSIS reports all use the same server list and DBA database, and the patching page uses this data heavily. The setup chapters of the SQL Server DBA Handbook cover what good configuration looks like.
The other Auditing reports: Licensing | Availability: ping and response time | Disk space alerts | Database inventory | Backups | SSIS errors
Clive Roberts DBA
One thought on “SQL Server Inventory Report”
Comments are closed.