Create a Database of Error Codes and Descriptions for Windows and ConfigMgr

In a recent post, I described different ways to translate error codes for Windows and Configuration Manager into their friendly descriptions.  In this post, I will show you how to create a SQL database of known error codes and descriptions that you can join to in your SQL queries, to help simplify your troubleshooting, and I will also give some example queries you can use with Configuration Manager.

Windows and system error codes are standard and are published by Microsoft on MSDN, but there is no published resource of error codes for Configuration Manager 2012 onwards that I know of.  To have a database of all these codes is quite useful as they are not stored either in WMI or in the ConfigMgr database – only the error codes themselves are stored.  These codes are translated to their descriptions by the ConfigMgr console and the ConfigMgr SSRS Reports probably utilizing dll files.

I extracted a list of 11,839 error codes and descriptions using the SrsResource.dll, as described in the previous post, and exported them into a csv file.  Using the PowerShell function below, I converted each error code to give the hex and decimal codes for each.  In Configuration Manager, the log files and reports tend to use the hexadecimal value or the ‘signed integer’ decimal value for the error code, however WMI stores the codes as ‘unsigned integers’ (always positive or zero), therefore I have included all three for easy referencing.

function Convert-Number {
[CmdletBinding()]
    param
        (
        [Parameter(Mandatory=$True)]
            $Number,
        [Parameter(Mandatory=$True,ParameterSetName='Binary')]
            [switch]$ToBinary,
        [Parameter(Mandatory=$True,ParameterSetName='Hex')]
            [switch]$ToHexadecimal,
        [Parameter(Mandatory=$True,ParameterSetName='Signed')]
            [switch]$ToSignedInteger,
        [Parameter(Mandatory=$True,ParameterSetName='Unsigned')]
            [switch]$ToUnSignedInteger
        )

$binary = [Convert]::ToString($Number,2)

if ($ToBinary)
    {
        $binary
    }

if ($ToHexadecimal)
    {
        $hex = "0x" + [Convert]::ToString($Number,16)
        $hex
    }

if ($ToSignedInteger)
    {
        $int32 = [Convert]::ToInt32($binary,2)
        $int32
    }
if ($ToUnSignedInteger)
    {
        $Uint64 = [Convert]::ToUInt64($binary,2)
        $Uint64
    }
}

Using this function, you can convert between binary, hexadecimal, signed and unsigned integers:

To import those codes into a SQL database, first download the attached XLSX file which contains all the codes, and save it in CSV format.  The error descriptions have had any line breaks removed so that they will import correctly.

ErrorCodes_Final.xlsx

Now run the following T-SQL code against your SQL instance.  It will create a new database called ‘ErrorCodes’ and import all the entries from the CSV into a new table called ‘WindowsErrorCodes’.  Change the path to the CSV file as needed.

I’m using the same SQL instance as my Configuration Manager database so I can easily reference the two.

Create Database ErrorCodes
Go
USE ErrorCodes;
CREATE TABLE WindowsErrorCodes (
Hexadecimal VARCHAR(10) NOT NULL,
SignedInteger BIGINT NOT NULL,
UnSignedInteger BIGINT NOT NULL,
ErrorDescription NVARCHAR(MAX)
);

BULK
INSERT WindowsErrorCodes
FROM '<mycomputer>\C$\temp\ErrorCodes_Final.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = 'n'
)
GO

Now let’s run a quick query to find a Configuration Manager error description:

If I want to query for application deployment errors, similar to the PowerShell script in my last post, then I can use the following query entering the AssignmentID of the application deployment, which you can find from the ConfigMgr Console in the additional columns.  I will join the app deployment errors by their error code to my new database to return the error descriptions for each.  Join the ErrorCode field from the ConfigMgr database views with the SignedInteger field from the error code database.

select  app.ApplicationName, ass.CollectionName,
sys.Name0 as 'Computer Name',
det.ResourceID, det.CIVersion, det.ErrorCode, det.Errortype,
err.Hexadecimal, err.ErrorDescription
from v_CIErrorDetails det
inner join V_R_System sys on det.ResourceID = sys.ResourceID
inner join v_CIAssignmentToCI ci on det.CI_ID = ci.CI_ID
inner join v_CIAssignment ass on ci.AssignmentID = ass.AssignmentID
inner join v_ApplicationAssignment app on ci.AssignmentID = app.AssignmentID
left join ErrorCodes.dbo.WindowsErrorCodes err on det.ErrorCode = err.SignedInteger
where ci.AssignmentID = 16777540
order by sys.Name0

Results:

Cool 🙂

I can also get summary data categorized by the error code, for that deployment, again using the AssignmentID:

Select
sum.CollectionName,
sum.Description,
err.DTCI,
err.StatusType,
err.EnforcementState,
err.ErrorCode,
code.Hexadecimal,
code.ErrorDescription,
err.Total
from vAppDeploymentErrorStatus err
inner join v_CIAssignment ass on err.AssignmentUniqueID = ass.Assignment_UniqueID
inner join vAppDTDeploymentSummary sum on err.DTCI = sum.DTCI
left join ErrorCodes.dbo.WindowsErrorCodes code on err.ErrorCode = code.SignedInteger
where err.assignmentID = 16777540
and sum.assignmentID = err.AssignmentID
and err.ErrorCode <> 0
order by Description, Total desc

Results:

Both of these queries together roughly equate to what you can see in the ConfigMgr Console > Deployments node:

Since we now don’t depend on the Console or the SSRS reports to translate the error descriptions for us, we can go ahead and more easily create custom reports or SQL queries or PowerShell scripts to report this information for us 🙂