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:

CaptureTo 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:

Capture3

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:

capture2

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:

capture5

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

capture4Since 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 🙂

 

Translating Error Codes for Windows and Configuration Manager

As a Windows and Configuration Manager administrator, I often come across error codes that need translating into their more friendly descriptions.  In Configuration Manager, sometimes these codes are translated for you in the log files, reports and the ConfigMgr console, but sometimes they are not.  Sometimes they will be in decimal format, and sometimes hexadecimal.  For Windows error codes, there are a number of methods to return the friendly descriptions, for example the “net helpmsg”:

Capture

But it can only handle decimal codes:

Capture1

In PowerShell, there is the .Net namespace ComponentModel.Win32Exception, which can handle both decimal and hex:

Capture3

Common Windows error codes are also documented in MSDN:

https://msdn.microsoft.com/en-us/library/windows/desktop/ms681381(v=vs.85).aspx
https://msdn.microsoft.com/en-us/library/cc231199.aspx

However, for error codes that are specific to Configuration Manager, you can use the handy CMTRACE utility in the Configuration Manager toolkit, which has an error lookup.  This returns error descriptions for both Windows and Configuration Manager, supports decimal and hex, and supports error codes from more sources too, including WMI and Windows Update Agent:

Capture2

Capture4

Capture5

But if you are scripting and want to translate an error code, how can you do that?  Well there is a handy little dll file called SrsResources.dll that comes with the installation of the Configuration Manager Console, and can be found here: %ProgramFiles(x86)%\Microsoft Configuration Manager\AdminConsole\bin\SrsResources.dll.  Using this dll, we can translate error codes for Windows, Configuration Manager, WMI etc, and even translate status message IDs.  It will call other dll files when it needs to, to find the error string.

Using PowerShell, we can create the following simple function which will use the SrsResources.dll to translate a decimal or hex error code for us:


function Get-CMErrorMessage {
[CmdletBinding()]
    param
        (
        [Parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
            [int64]$ErrorCode
        )

[void][System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\SrsResources.dll")
[SrsResources.Localization]::GetErrorMessage($ErrorCode,"en-US")
}

Capture6

To take it further, we can export a list of error codes, for example here we will use the same function to enumerate all decimal codes between 0 and 50, and also output the equivalent hex codes:


$errorcodes = @()
$i = -1
Do
    {
        $i ++
        $description = Get-CMErrorMessage -ErrorCode $i
        if ($description -notlike "Unknown Error*")
            {
                $hex = '{0:x}' -f $i
                $errorcode = New-Object psobject
                Add-Member -InputObject $errorcode -MemberType NoteProperty -Name DecimalErrorCode -Value $i
                Add-Member -InputObject $errorcode -MemberType NoteProperty -Name HexErrorCode -Value ("0x" + $hex)
                Add-Member -InputObject $errorcode -MemberType NoteProperty -Name ErrorDescription -Value $description
                $errorcodes += $errorcode
            }

    }
Until ($i -eq 50)
$errorcodes | ft -AutoSize

Capture7Pretty cool 🙂  Using this SrsResources.dll creates a log file in your %TEMP% directory called SCCMReporting.log, and this log quickly increases in size, so if you use it a lot check the size of this log file from time to time.  The logging can be useful for identifying which dll was used to find the error string.

To convert between decimal and hexadecimal and vice-versa, we can use this simple function. With PowerShell, you can convert to decimal natively in the console just by entering the hexadecimal code,  but using this function allows us to convert both ways, and is more useful for scripts.


function Convert-ErrorCode {
[CmdletBinding()]
    param
        (
        [Parameter(Mandatory=$True,ParameterSetName='Decimal')]
            [int64]$DecimalErrorCode,
        [Parameter(Mandatory=$True,ParameterSetName='Hex')]
            $HexErrorCode
        )
if ($DecimalErrorCode)
    {
        $hex = '{0:x}' -f $DecimalErrorCode
        $hex = "0x" + $hex
        $hex
    }

if ($HexErrorCode)
    {
        $DecErrorCode = $HexErrorCode.ToString()
        $DecErrorCode
    }
}

Capture8Finally, wrapping all this together, here is a script that uses both functions we have created earlier, and will return all the machines that are in an error state for a ConfigMgr application deployment, with the error code and description.  Because we filter using the current application revision, this actually returns more accurate results than the ConfigMgr console > Deployments node, as that data will include previous application revisions where no data is available for the current revision, which produces misleading results.

First, we query WMI on the ConfigMgr site server for the list of applications and choose the one we want:

Capture

Then we query for the deployments and deployment types for that application, and choose the one we want.  The numbers of errors is returned, but as previously mentioned, this may not be completely accurate at this stage.

Capture2Then we return the results translating the error codes into their descriptions.

Capture3

Cool 🙂

Note that WMI stores the error codes as unsigned integers, but the ConfigMgr console displays errors as signed integers, so we do a conversion and include both in our results.

In the next blog, I describe how to create a SQL database of these error codes for easy referencing in SQL queries: Create a database of error codes and descriptions for Windows and Configmgr

Here’s the complete script:


<#

.SYNOPSIS
    Returns the error code and error descriptions for all computers in an error state for an application deployment

.DESCRIPTION
    This script asks you to choose a ConfigrMgr application, then choose a deployment / deployment type for that application, then returns all the computers that are in an error state for that
    deployment, with the error code and error description.
    Requires to be run on a computer with the ConfigMgr console installed, and the path to the SrsResources.dll needs to be specified in the "Get-CMErrorMessage" function.  You may also
    need to change the localization in this function to your region, eg "en-US".

.PARAMETER SiteServer
    The name of the ConfigMgr Site server

.PARAMETER SiteCode
    The ConfigMgr Site Code

.NOTES
    Script name: Get-CMAppDeploymentErrors.ps1
    Author:      Trevor Jones
    Contact:     @trevor_smsagent
    DateCreated: 2015-06-17
    Link:        https://smsagent.wordpress.com

#>

[CmdletBinding(SupportsShouldProcess=$True)]
    param
        (
        [Parameter(Mandatory=$False)]
            [string]$SiteServer="sccmserver-01",
        [Parameter(Mandatory=$False)]
            [string]$SiteCode="ABC"
        )

function Get-CMErrorMessage {
[CmdletBinding()]
    param
        (
        [Parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
            [int64]$ErrorCode
        )

[void][System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\SrsResources.dll")
[SrsResources.Localization]::GetErrorMessage($ErrorCode,"en-US")
}

function Convert-ErrorCode {
[CmdletBinding()]
    param
        (
        [Parameter(Mandatory=$True,ParameterSetName='Decimal')]
            [int64]$DecimalErrorCode,
        [Parameter(Mandatory=$True,ParameterSetName='Hex')]
            $HexErrorCode
        )
if ($DecimalErrorCode)
    {
        $hex = '{0:x}' -f $DecimalErrorCode
        $hex = "0x" + $hex
        $hex
    }

if ($HexErrorCode)
    {
        $DecErrorCode = $HexErrorCode.ToString()
        $DecErrorCode
    }
}

# Get Application
$App = Get-WmiObject -ComputerName $SiteServer -Namespace ROOT\sms\Site_$SiteCode -Class SMS_ApplicationLatest |
    Sort LocalizedDisplayName |
    Select LocalizedDisplayName,SDMPackageVersion,ModelName |
    Out-GridView -Title "Choose an Application" -OutputMode Single

# Get Deployment Types and Deployments for Application
$DT = Get-WmiObject -ComputerName $SiteServer -Namespace ROOT\sms\Site_$SiteCode -query "Select * from SMS_AppDTDeploymentSummary where AppModelName = '$($App.ModelName)'" |
    Select Description,CollectionName,CollectionID,NumberErrors,AssignmentID |
    Out-GridView -Title "Choose a Deployment / Deployment Type" -OutputMode Single

# Get Errors
$Errors = Get-WmiObject -ComputerName $SiteServer -Namespace ROOT\sms\Site_$SiteCode -query "Select * from SMS_AppDeploymentErrorAssetDetails where AssignmentID = '$($DT.AssignmentID)' and DTName = '$($DT.Description)' and Revision = '$($App.SDMPackageVersion)' and Errorcode <> 0" |
    Sort Machinename |
    Select MachineName,Username,Starttime,Errorcode

if ($Errors -ne $null)
{
    # Create new object with error descriptions in
    $AllErrors = @()
    foreach ($item in $Errors)
        {
            $errordescription = Get-CMErrorMessage -ErrorCode $item.Errorcode
            $hex = Convert-ErrorCode -DecimalErrorCode $item.Errorcode
            $int = [int]$hex
            $obj = New-Object psobject
            Add-Member -InputObject $obj -MemberType NoteProperty -Name ComputerName -Value $item.MachineName
            Add-Member -InputObject $obj -MemberType NoteProperty -Name UserName -Value $item.Username
            Add-Member -InputObject $obj -MemberType NoteProperty -Name StartTime -Value $([management.managementDateTimeConverter]::ToDateTime($item.Starttime))
            Add-Member -InputObject $obj -MemberType NoteProperty -Name UnsignedIntErrorCode -Value $item.Errorcode
            Add-Member -InputObject $obj -MemberType NoteProperty -Name SignedIntErrorCode -Value $int
            Add-Member -InputObject $obj -MemberType NoteProperty -Name HexErrorCode -Value $hex
            Add-Member -InputObject $obj -MemberType NoteProperty -Name ErrorDescription -Value $errordescription
            $AllErrors += $obj
        }
    # Return results
    write-host "Application: $($App.LocalizedDisplayName)"
    write-host "DeploymentType: $($DT.Description)"
    write-host "TargetedCollection: $($DT.CollectionName)"
    $AllErrors | ft -AutoSize
}
Else {Write-host "No results returned."}