Inventory Report

Overview

Every audit, every upgrade plan and every support call starts with the same question: what is this server. Inventory is the report that answers it in one row.

Name, machine, edition, release, build, clustered or not, virtual or not. Nothing else.

Run once against one instance it is a fact sheet. Run from the Start Page against every registered instance, it stacks one row per server into a single grid, which is the version and patch inventory for the whole estate on one page.

The Inventory report
One row per instance. Run from the Start Page it becomes one row per server across the estate.

Where to find it

Two routes, and they produce different things.

Route What you get
Server tree → Instance Level Reports → Inventory One row, for the instance you right-clicked.
Start Page toolbar → Inventory One row per connected instance, stacked into a single grid, titled Inventory – All Instances.

The single instance page title reads Inventory for <server name>.

The All Instances button needs a registration code. Without one it says so rather than running. The single instance report on the Instance Level Reports menu has no such requirement.

The all-instances run skips any registered instance that is not answering, and it pauses briefly between servers so a large estate does not open every connection at once.

Requirements

  • No minimum version for most of the page. Everything except Machine Type comes from SERVERPROPERTY, which needs no special permission.
  • VIEW SERVER STATE for the Machine Type column, which reads sys.dm_os_sys_info. Without it that column is the only thing missing.

Reading the grid

Column What it is
Display Name SERVERPROPERTY('ServerName'), the name SQL Server believes it has.
Computer Name SERVERPROPERTY('MachineName'), the Windows machine name.
Instance Name The instance, again from ServerName.
SQL Server Edition Enterprise, Standard, Developer, Express and so on, with the trailing word Edition removed.
Product The release year: Microsoft SQL Server 2016, 2019, 2022.
SQL Server Version The full four part build number, red when the instance is behind.
Clustered Yes when the instance is a failover cluster instance.
Machine Type Virtual or Physical, or Unknown on SQL Server 2005 and 2008.
Asset State Always In Use.
Department Always blank.

Display Name against Computer Name

When those two disagree on a standalone instance, somebody renamed the machine and did not tell SQL Server. ServerName comes from @@SERVERNAME, which is set at install time and only changes if sp_dropserver and sp_addserver are run. A rename without that leaves jobs, linked servers, replication and log shipping all referring to a name that no longer exists.

The two are also expected to differ for good reasons: on a clustered instance the machine name is whichever node is running it right now, and on a named instance the display name carries the instance suffix.

The red build number

SQL Server Version turns red when the build is behind the latest one known for that release. The comparison is against a table of current builds carried with Database Health Monitor, and it compares the third part of the version, which is the build number that moves with each cumulative update.

Red is not an emergency. It means there are cumulative updates you have not applied, which is a patching conversation rather than an incident. A green build means you are current as of the version of Database Health Monitor you are running, so the answer ages as the product does.

The two columns that are not read from SQL Server

Asset State always reads In Use and Department is always blank. Neither is a fact about the instance, because SQL Server does not know either of them. They are there so the grid exports into an asset spreadsheet with the columns an asset spreadsheet expects, ready to be filled in by whoever owns that list.

The inventory grid
Display Name against Computer Name, and the build number that turns red when it is behind.

A note about newer releases

The Product column maps release numbers to years through a fixed list that ends at SQL Server 2022. On a newer release the row still appears with a correct build number and a correct edition, and Product reads Microsoft SQL Server unknown. The build comparison does not apply on those releases either, so the version is never coloured there.

This is the one place on the page where an answer degrades rather than being absent, and it is worth knowing before reading unknown as a fault on the instance.

How to read the report

  1. Read the edition first. Standard where you expected Enterprise changes what half the rest of this product will show you: no online index rebuilds, a memory cap, limited Availability Group features. Developer edition on something a user connects to is a licensing problem.
  2. Read Display Name against Computer Name. A mismatch on a standalone instance is worth ten minutes.
  3. Read the build. Red means behind. Compare it across the estate rather than against a number in isolation.
  4. Read Machine Type against the workload. Virtual is fine. Virtual and surprised is worth following up, because it changes the storage and CPU conversation.
  5. Take the All Instances grid into the patch plan. Sorted by build, it is the patch inventory.

Common patterns

Developer edition on a production server. Free, fully featured and not licensed for production. Found immediately here and almost nowhere else.

Express edition where somebody expected Standard. Explains a memory ceiling, a database size ceiling and a missing SQL Server Agent all at once. Job Schedules returning an empty page on the same instance is the confirming symptom.

Display Name and Computer Name differ on a standalone instance. A machine rename. Check jobs, linked servers and any replication before something else finds it.

Every instance in the estate red except two. A patching round that did not finish. The All Instances grid sorted by build shows exactly where it stopped.

Clustered = Yes with a Computer Name you did not expect. The instance has failed over to another node and stayed there. Not a fault, and worth knowing.

Where the data comes from

  • SERVERPROPERTY for the name, machine name, edition, product version and clustered flag.
  • sys.dm_os_sys_info.virtual_machine_type for Machine Type, on SQL Server 2008 R2 and newer.
  • The current build table carried with Database Health Monitor, for the red comparison.

The query checks whether virtual_machine_type exists before referring to it, and runs a different version of itself on SQL Server 2005 and 2008 where it does not. Referring to a column that does not exist is a compile time failure in SQL Server, not a run time one, so a single query with a version test inside it would fail on the older instances before any of it ran.

virtual_machine_type reports 1 for a hypervisor and 2 for other virtualisation. Only 1 is reported as Virtual, so an unusual container or emulation layer reads as Physical.

Nothing is stored by this page.

Report Why you would go there
Migration Planner What a replacement server needs, sized from this instance’s real workload.
Quick Scan Report The configuration review that goes with the fact sheet.
Configuration Values The sp_configure settings behind the edition.
Deprecated Features What breaks on the version you are moving to.
Availability Groups Where a clustered instance fits into the high availability picture.
Server Overview The live picture of the same instance.

Frequently asked questions

Why are Display Name and Instance Name the same? Because both come from SERVERPROPERTY('ServerName'). The columns are kept separate so the grid matches the shape of a typical asset inventory, where the two are not always the same thing.

Why does Asset State always say In Use? Because SQL Server has no idea. It is a placeholder column for the spreadsheet this grid gets pasted into.

Why is Machine Type Unknown? The instance is SQL Server 2005 or 2008, where sys.dm_os_sys_info has no virtual_machine_type column, or the connection lacks VIEW SERVER STATE.

Why is the version red? Because the build is behind the most recent one known for that release. It is a patching note, not an alert.

Why does Product say unknown? The release is newer than the list this report maps years from. The build number and edition are still correct.

Why is an instance missing from the All Instances grid? Because it did not answer when the page ran. That run skips instances that are not online rather than waiting on them.

Why does the All Instances button ask for a registration code? The estate wide pages are a registered feature. The single instance report on the Instance Level Reports menu is not.