Is This Instance Patched? Reading ProductUpdateLevel and Build

ProductUpdateLevel tells you the cumulative update an instance runs, but it is only one clue. To answer “is this instance patched?”, read it together with the full build number.

The question your auditor asks

Someone from security sends a short email: “Is the production server patched?” You could say “I think CU8.” You could also open a query window and know. I prefer knowing.

SERVERPROPERTY returns the pieces of the answer. Each one is a different view of the same running engine. I also keep the collection time, because “patched” is true only as of a moment.

SELECT SYSUTCDATETIME() AS CollectedAtUtc,
       CONVERT(nvarchar(128), SERVERPROPERTY('ProductVersion')) AS ProductVersion,
       CONVERT(nvarchar(128), SERVERPROPERTY('ProductLevel')) AS ProductLevel,
       CONVERT(nvarchar(128), SERVERPROPERTY('ProductUpdateLevel')) AS ProductUpdateLevel,
       CONVERT(nvarchar(128), SERVERPROPERTY('ProductUpdateReference')) AS ProductUpdateReference,
       CONVERT(nvarchar(128), SERVERPROPERTY('ProductBuildType')) AS ProductBuildType;

SELECT LEFT(@@VERSION, CHARINDEX(CHAR(10), @@VERSION + CHAR(10)) - 1) AS VersionBanner;

On my test server, ProductVersion is 17.0.4085.5 and ProductLevel is RTM. ProductUpdateLevel is CU8. ProductUpdateReference holds a KB number, and ProductBuildType says GDR. The banner on the second grid repeats it all in one line: RTM-CU8-GDR.

Your values will differ. The shape is what matters. The build number is the exact identity. The update label and the KB reference are friendly names for it.

A typo gives NULL, not an error

One small trap in SERVERPROPERTY. Ask for a property name that does not exist and you get NULL, with no error. If your inventory script has a typo, the column just fills with NULLs.

SELECT SERVERPROPERTY('ProductUpdateLevelMisspelled') AS UnknownProperty;

The result is NULL. So when a real server shows NULL, ask whether the property exists on that version before you decide the server has no update.

Never compare builds as text

Now the comparison with your approved baseline. The tempting way is to compare the strings. It looks fine until a build number gets shorter, such as 999 against 4085. Text compares one character at a time, and “9” is bigger than “4”.

SELECT CASE WHEN N'17.0.999.1' > N'17.0.4085.5'
            THEN 'text says newer' ELSE 'text says older' END AS TextCompare,
       CASE WHEN CAST(PARSENAME(N'17.0.999.1', 2) AS int) > CAST(PARSENAME(N'17.0.4085.5', 2) AS int)
            THEN 'numbers say newer' ELSE 'numbers say older' END AS NumberCompare;

The text says build 999 is newer. The numbers say it is older. Only the numbers are right. PARSENAME splits the dotted version into parts, and part 2 is the build.

Compare the running build to your baseline

Here is a small check you can adapt. The approved build below is just an example I picked. Replace it with the number your team approved for this major version.

DECLARE @Running  nvarchar(128) = CONVERT(nvarchar(128), SERVERPROPERTY('ProductVersion'));
DECLARE @Approved nvarchar(128) = N'17.0.4000.0';

SELECT @Running AS RunningBuild, @Approved AS ApprovedBuild,
       CASE WHEN r.Major <> a.Major THEN 'Different major version'
            WHEN r.Build > a.Build OR (r.Build = a.Build AND r.Revision >= a.Revision)
                 THEN 'At or above baseline'
            ELSE 'Behind baseline' END AS Verdict
FROM (SELECT CAST(PARSENAME(@Running, 4) AS int) AS Major,
             CAST(PARSENAME(@Running, 2) AS int) AS Build,
             CAST(PARSENAME(@Running, 1) AS int) AS Revision) AS r
CROSS JOIN (SELECT CAST(PARSENAME(@Approved, 4) AS int) AS Major,
                   CAST(PARSENAME(@Approved, 2) AS int) AS Build,
                   CAST(PARSENAME(@Approved, 1) AS int) AS Revision) AS a;

On my server the verdict reads “At or above baseline”, because 4085 is higher than 4000. On an older server you would see “Behind baseline”. That is the answer your auditor wants, with a timestamp beside it.

Where this check can mislead you

A bigger build number is not always a better patch. Servicing branches can number differently, so match the build against the baseline list for your branch. Collect from every instance and replica in scope. Mark any server you could not reach as missing evidence.

Recollect after maintenance and the restart. An installer log says the setup ran. Only the running engine tells you what is actually loaded.

Next time the email arrives, answer with a build number and a time.

An update label is not a patch verdict, it is one clue beside the build.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.