Author Archive: Jacques

SCCM Report / SQL Query – All active content distributions

SCCM has a built-in report showing all active content distributions.  This report is very useful to track content distributions still in progress.  I just added in a few extra fields to show me information about the package type, size of the source files and the percentage completed where possible.

I have included a commented out section at the top to declare a value for the Distribution Point name.  Just remove the comment characters from the top and the WHERE clause if you want to filter your report for a specific Distribution Point.

–DECLARE @DP nvarchar(50)
–SET @DP = <DP Server Name>

SELECT
pkg.PackageID,
pkg.Name AS ‘Package Name’,
CASE
WHEN pkg.PackageType = 0 THEN ‘Software Distribution Package’
WHEN pkg.PackageType = 3 THEN ‘Driver Package’
WHEN pkg.PackageType = 4 THEN ‘Task Sequence Package’
WHEN pkg.PackageType = 5 THEN ‘Software Update Package’
WHEN pkg.PackageType = 6 THEN ‘Device Setting Package’
WHEN pkg.PackageType = 7 THEN ‘Virtual Package’
WHEN pkg.PackageType = 8 THEN ‘Application’
WHEN pkg.PackageType = 257 THEN ‘Image Package’
WHEN pkg.PackageType = 258 THEN ‘Boot Image Package’
WHEN pkg.PackageType = 259 THEN ‘Operating System Install Package’
Else ‘Unknown’
End AS ‘Package Type’,
LEFT(SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) – CHARINDEX(‘\\’,
dp.ServerNALPath) – 3 ),CHARINDEX(‘.’,SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) –
CHARINDEX(‘\\’, dp.ServerNALPath) – 3 ))-1) AS ‘DP’,
dp.SiteCode,
dp.LastRefreshTime,
stat.SourceVersion,
‘SourceSize(MB)’ = prs.SourceSize/1024,
stat.LastCopied,
stat.SummaryDate,
(
SELECT TOP 1 msg.InsString3
FROM
v_StatMsgWithInsStrings msg
JOIN v_StatMsgModuleNames modNames ON msg.ModuleName = modNames.ModuleName
JOIN v_StatMsgAttributes attpkg ON msg.RecordID=attpkg.RecordID AND msg.Time=attpkg.AttributeTime
JOIN v_StatMsgAttributes attdp ON msg.RecordID=attdp.RecordID AND msg.Time=attdp.AttributeTime
WHERE
attpkg.AttributeValue =pkg.PackageID
AND msg.MessageID=’8204′
AND msg.InsString2 = SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) –
CHARINDEX(‘\\’, dp.ServerNALPath) – 3 )
ORDER BY msg.Time DESC) AS ‘% Completed’,
stat.InstallStatus
FROM
v_Package pkg
LEFT JOIN v_PackageStatusRootSummarizer prs ON pkg.PackageID = prs.PackageID
JOIN v_DistributionPoint dp ON pkg.PackageID=dp.PackageID
JOIN v_PackageStatusDistPointsSumm stat ON dp.ServerNALPath=stat.ServerNALPath and dp.PackageID=stat.PackageID
WHERE
stat.State!=0
–AND LEFT(SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) – CHARINDEX(‘\\’,
dp.ServerNALPath) – 3 ),CHARINDEX(‘.’,SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) –
CHARINDEX(‘\\’, dp.ServerNALPath) – 3 ))-1) = @DP
ORDER BY
pkg.Name, dp.SiteCode

SCCM Report / SQL Query – Days since last restart

When you have an SCCM environment where you deploy Software Updates, but a restart is not enforced on devices you may want to see which devices have not restarted in a certain amount of days.  This query will give you a list of the devices with a count of the amount of days.

In this query I have some extra fields for the full name of the user as well as the email address.  I use this information to contact these specific users by exporting the results to a spreadsheet and copying all the email addresses into Outlook.  If you would like to use these other fields you need to have your Active Directory User Discovery extended.

SELECT SYS.Name0 as [Computer Name], SYS.User_Name0 as [User Name], USR.Full_User_Name0 as [Full Name],
USR.mail0 as [Email Address], Convert(VarChar(10), os.LastBootUpTime0) [Last Restart Date],
OS.lastBootUpTime0 as [Last Restart], DATEDIFF(dd, LastBootUpTime0, GETDATE()) AS [Days Since Last Restart]
FROM v_R_System SYS
JOIN v_Gs_Operating_System OS on SYS.ResourceID = OS.ResourceID
LEFT JOIN v_R_User USR on SYS.User_Name0 = USR.User_Name0
WHERE SYS.Operating_System_Name_and0 like ‘%workstation%’
AND (DATEDIFF(dd, LastBootUpTime0, GETDATE())) >= 7
ORDER BY [Days Since Last Restart] DESC

SCCM Report / SQL Query – Troubleshooting Software Update scan errors

SCCM has a built-in report displaying all scan errors reported by the Software Updates component.  This is very useful if you want to determine if there is a common error for multiple devices i.e. group policy conflicts or update scan timeout.  This report is found under the “Software Updates – E Troubleshooting” category under the default reports.  The report I would like to discuss is the one called “Troubleshooting 1 – Scan errors”.  This report will typically show you an error code, Hex error code, error description and a count of the devices with the specific error.  However none of the error codes or the error descriptions are very user-friendly so you always end up searching the internet for the meaning of each error code.

To make this report easier for me to read I decided to include custom descriptive values for most of the error codes. I used values from several sources on the internet, but mostly from the Windows Update error code list as published by Microsoft.  The following query will work on SCCM 2012.  It is quite a lengthy query and there is text wrapping so make sure you select the full query.

select
ErrorStatusID = uss.LastStatusMessageID&0x0000FFFF,
Status = asi.MessageName,
ErrorCode = isnull(uss.LastErrorCode,0),
dbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode,
‘ErrorText’ =
CASE dbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0)))
WHEN ‘80240001’ THEN ‘WU_E_NO_SERVICE Windows Update Agent was unable to provide the service.’
WHEN ‘80240002’ THEN ‘WU_E_MAX_CAPACITY_REACHED The maximum capacity of the service was exceeded.’
WHEN ‘80240003’ THEN ‘WU_E_UNKNOWN_ID An ID cannot be found.’
WHEN ‘80240004’ THEN ‘WU_E_NOT_INITIALIZED The object could not be initialized.’
WHEN ‘80240005’ THEN ‘WU_E_RANGEOVERLAP The update handler requested a byte range overlapping a previously requested range.’
WHEN ‘80240006’ THEN ‘WU_E_TOOMANYRANGES The requested number of byte ranges exceeds the maximum number (2^31 – 1).’
WHEN ‘80240007’ THEN ‘WU_E_INVALIDINDEX The index to a collection was invalid.’
WHEN ‘80240008’ THEN ‘WU_E_ITEMNOTFOUND The key for the item queried could not be found.’
WHEN ‘80240009’ THEN ‘WU_E_OPERATIONINPROGRESS Another conflicting operation was in progress. Some operations such as installation cannot be performed twice simultaneously.’
WHEN ‘8024000A’ THEN ‘WU_E_COULDNOTCANCEL Cancellation of the operation was not allowed.’
WHEN ‘8024000B’ THEN ‘WU_E_CALL_CANCELLED Operation was cancelled.’
WHEN ‘8024000C’ THEN ‘WU_E_NOOP No operation was required.’
WHEN ‘8024000D’ THEN ‘WU_E_XML_MISSINGDATA Windows Update Agent could not find required information in the update”s XML data.’
WHEN ‘8024000E’ THEN ‘WU_E_XML_INVALID Windows Update Agent found invalid information in the update”s XML data.’
WHEN ‘8024000F’ THEN ‘WU_E_CYCLE_DETECTED Circular update relationships were detected in the metadata.’
WHEN ‘80240010’ THEN ‘WU_E_TOO_DEEP_RELATION Update relationships too deep to evaluate were evaluated.’
WHEN ‘80240011’ THEN ‘WU_E_INVALID_RELATIONSHIP An invalid update relationship was detected.’
WHEN ‘80240012’ THEN ‘WU_E_REG_VALUE_INVALID An invalid registry value was read.’
WHEN ‘80240013’ THEN ‘WU_E_DUPLICATE_ITEM Operation tried to add a duplicate item to a list.’
WHEN ‘80240016’ THEN ‘WU_E_INSTALL_NOT_ALLOWED Operation tried to install while another installation was in progress or the system was pending a mandatory restart.’
WHEN ‘80240017’ THEN ‘WU_E_NOT_APPLICABLE Operation was not performed because there are no applicable updates.’
WHEN ‘80240018’ THEN ‘WU_E_NO_USERTOKEN Operation failed because a required user token is missing.’
WHEN ‘80240019’ THEN ‘WU_E_EXCLUSIVE_INSTALL_CONFLICT An exclusive update cannot be installed with other updates at the same time.’
WHEN ‘8024001A’ THEN ‘WU_E_POLICY_NOT_SET A policy value was not set.’
WHEN ‘8024001B’ THEN ‘WU_E_SELFUPDATE_IN_PROGRESS The operation could not be performed because the Windows Update Agent is self-updating.’
WHEN ‘8024001D’ THEN ‘WU_E_INVALID_UPDATE An update contains invalid metadata.’
WHEN ‘8024001E’ THEN ‘WU_E_SERVICE_STOP Operation did not complete because the service or system was being shut down.’
WHEN ‘8024001F’ THEN ‘WU_E_NO_CONNECTION Operation did not complete because the network connection was unavailable.’
WHEN ‘80240020’ THEN ‘WU_E_NO_INTERACTIVE_USER Operation did not complete because there is no logged-on interactive user.’
WHEN ‘80240021’ THEN ‘WU_E_TIME_OUT Operation did not complete because it timed out.’
WHEN ‘80240022’ THEN ‘WU_E_ALL_UPDATES_FAILED Operation failed for all the updates.’
WHEN ‘80240023’ THEN ‘WU_E_EULAS_DECLINED The license terms for all updates were declined.’
WHEN ‘80240024’ THEN ‘WU_E_NO_UPDATE There are no updates.’
WHEN ‘80240025’ THEN ‘WU_E_USER_ACCESS_DISABLED Group Policy settings prevented access to Windows Update.’
WHEN ‘80240026’ THEN ‘WU_E_INVALID_UPDATE_TYPE The type of update is invalid.’
WHEN ‘80240027’ THEN ‘WU_E_URL_TOO_LONG The URL exceeded the maximum length.’
WHEN ‘80240028’ THEN ‘WU_E_UNINSTALL_NOT_ALLOWED The update could not be uninstalled because the request did not originate from a WSUS server.’
WHEN ‘80240029’ THEN ‘WU_E_INVALID_PRODUCT_LICENSE Search may have missed some updates before there is an unlicensed application on the system.’
WHEN ‘8024002A’ THEN ‘WU_E_MISSING_HANDLER A component required to detect applicable updates was missing.’
WHEN ‘8024002B’ THEN ‘WU_E_LEGACYSERVER An operation did not complete because it requires a newer version of server.’
WHEN ‘8024002C’ THEN ‘WU_E_BIN_SOURCE_ABSENT A delta-compressed update could not be installed because it required the source.’
WHEN ‘8024002D’ THEN ‘WU_E_SOURCE_ABSENT A full-file update could not be installed because it required the source.’
WHEN ‘8024002E’ THEN ‘WU_E_WU_DISABLED Access to an unmanaged server is not allowed.’
WHEN ‘8024002F’ THEN ‘WU_E_CALL_CANCELLED_BY_POLICY Operation did not complete because the DisableWindowsUpdateAccess policy was set.’
WHEN ‘80240030’ THEN ‘WU_E_INVALID_PROXY_SERVER The format of the proxy list was invalid.’
WHEN ‘80240031’ THEN ‘WU_E_INVALID_FILE The file is in the wrong format.’
WHEN ‘80240032’ THEN ‘WU_E_INVALID_CRITERIA The search criteria string was invalid.’
WHEN ‘80240033’ THEN ‘WU_E_EULA_UNAVAILABLE License terms could not be downloaded.’
WHEN ‘80240034’ THEN ‘WU_E_DOWNLOAD_FAILED Update failed to download.’
WHEN ‘80240035’ THEN ‘WU_E_UPDATE_NOT_PROCESSED The update was not processed.’
WHEN ‘80240036’ THEN ‘WU_E_INVALID_OPERATION The object”s current state did not allow the operation.’
WHEN ‘80240037’ THEN ‘WU_E_NOT_SUPPORTED The functionality for the operation is not supported.’
WHEN ‘80240038’ THEN ‘WU_E_WINHTTP_INVALID_FILE The downloaded file has an unexpected content type.’
WHEN ‘80240039’ THEN ‘WU_E_TOO_MANY_RESYNC Agent is asked by server to resync too many times.’
WHEN ‘80240040’ THEN ‘WU_E_NO_SERVER_CORE_SUPPORT WUA API method does not run on Server Core installation.’
WHEN ‘80240041’ THEN ‘WU_E_SYSPREP_IN_PROGRESS Service is not available while sysprep is running.’
WHEN ‘80240042’ THEN ‘WU_E_UNKNOWN_SERVICE The update service is no longer registered with AU.’
WHEN ‘80240FFF’ THEN ‘WU_E_UNEXPECTED An operation failed due to reasons not covered by another error code.’
WHEN ‘80241001’ THEN ‘WU_E_MSI_WRONG_VERSION Search may have missed some updates because the Windows Installer is less than version 3.1.’
WHEN ‘80241002’ THEN ‘WU_E_MSI_NOT_CONFIGURED Search may have missed some updates because the Windows Installer is not configured.’
WHEN ‘80241003’ THEN ‘WU_E_MSP_DISABLED Search may have missed some updates because policy has disabled Windows Installer patching.’
WHEN ‘80241004’ THEN ‘WU_E_MSI_WRONG_APP_CONTEXT An update could not be applied because the application is installed per-user.’
WHEN ‘80241FFF’ THEN ‘WU_E_MSP_UNEXPECTED Search may have missed some updates because there was a failure of the Windows Installer.’
WHEN ‘80244000’ THEN ‘WU_E_PT_SOAPCLIENT_BASE WU_E_PT_SOAPCLIENT_* error codes map to the SOAPCLIENT_ERROR enum of the ATL Server Library.’
WHEN ‘80244001’ THEN ‘WU_E_PT_SOAPCLIENT_INITIALIZE Same as SOAPCLIENT_INITIALIZE_ERROR – initialization of the SOAP client failed, possibly because of an MSXML installation failure.’
WHEN ‘80244002’ THEN ‘WU_E_PT_SOAPCLIENT_OUTOFMEMORY Same as SOAPCLIENT_OUTOFMEMORY – SOAP client failed because it ran out of memory.’
WHEN ‘80244003’ THEN ‘WU_E_PT_SOAPCLIENT_GENERATE Same as SOAPCLIENT_GENERATE_ERROR – SOAP client failed to generate the request.’
WHEN ‘80244004’ THEN ‘WU_E_PT_SOAPCLIENT_CONNECT Same as SOAPCLIENT_CONNECT_ERROR – SOAP client failed to connect to the server.’
WHEN ‘80244005’ THEN ‘WU_E_PT_SOAPCLIENT_SEND Same as SOAPCLIENT_SEND_ERROR – SOAP client failed to send a message for reasons of WU_E_WINHTTP_* error codes.’
WHEN ‘80244006’ THEN ‘WU_E_PT_SOAPCLIENT_SERVER Same as SOAPCLIENT_SERVER_ERROR – SOAP client failed because there was a server error.’
WHEN ‘80244007’ THEN ‘WU_E_PT_SOAPCLIENT_SOAPFAULT Same as SOAPCLIENT_SOAPFAULT – SOAP client failed because there was a SOAP fault for reasons of WU_E_PT_SOAP_* error codes.’
WHEN ‘80244008’ THEN ‘WU_E_PT_SOAPCLIENT_PARSEFAULT Same as SOAPCLIENT_PARSEFAULT_ERROR – SOAP client failed to parse a SOAP fault.’
WHEN ‘80244009’ THEN ‘WU_E_PT_SOAPCLIENT_READ Same as SOAPCLIENT_READ_ERROR – SOAP client failed while reading the response from the server.’
WHEN ‘8024400A’ THEN ‘WU_E_PT_SOAPCLIENT_PARSE Same as SOAPCLIENT_PARSE_ERROR – SOAP client failed to parse the response from the server.’
WHEN ‘8024400B’ THEN ‘WU_E_PT_SOAP_VERSION Same as SOAP_E_VERSION_MISMATCH – SOAP client found an unrecognizable namespace for the SOAP envelope.’
WHEN ‘8024400C’ THEN ‘WU_E_PT_SOAP_MUST_UNDERSTAND Same as SOAP_E_MUST_UNDERSTAND – SOAP client was unable to understand a header.’
WHEN ‘8024400D’ THEN ‘WU_E_PT_SOAP_CLIENT Same as SOAP_E_CLIENT – SOAP client found the message was malformed; fix before resending.’
WHEN ‘8024400E’ THEN ‘WU_E_PT_SOAP_SERVER Same as SOAP_E_SERVER – The SOAP message could not be processed due to a server error; resend later.’
WHEN ‘8024400F’ THEN ‘WU_E_PT_WMI_ERROR There was an unspecified Windows Management Instrumentation (WMI) error.’
WHEN ‘80244010’ THEN ‘WU_E_PT_EXCEEDED_MAX_SERVER_TRIPS The number of round trips to the server exceeded the maximum limit.’
WHEN ‘80244011’ THEN ‘WU_E_PT_SUS_SERVER_NOT_SET WUServer policy value is missing in the registry.’
WHEN ‘80244012’ THEN ‘WU_E_PT_DOUBLE_INITIALIZATION Initialization failed because the object was already initialized.’
WHEN ‘80244013’ THEN ‘WU_E_PT_INVALID_COMPUTER_NAME The computer name could not be determined.’
WHEN ‘80244014’ THEN ‘WU_E_PT_INVALID_COMPUTER_LSID Cannot determine computer LSID.’
WHEN ‘80244015’ THEN ‘WU_E_PT_REFRESH_CACHE_REQUIRED The reply from the server indicates that the server was changed or the cookie was invalid; refresh the state of the internal cache and retry.’
WHEN ‘80244016’ THEN ‘WU_E_PT_HTTP_STATUS_BAD_REQUEST Same as HTTP status 400 – the server could not process the request due to invalid syntax.’
WHEN ‘80244017’ THEN ‘WU_E_PT_HTTP_STATUS_DENIED Same as HTTP status 401 – the requested resource requires user authentication.’
WHEN ‘80244018’ THEN ‘WU_E_PT_HTTP_STATUS_FORBIDDEN Same as HTTP status 403 – server understood the request, but declined to fulfill it.’
WHEN ‘80244019’ THEN ‘WU_E_PT_HTTP_STATUS_NOT_FOUND Same as HTTP status 404 – the server cannot find the requested URI (Uniform Resource Identifier)’
WHEN ‘8024401A’ THEN ‘WU_E_PT_HTTP_STATUS_BAD_METHOD Same as HTTP status 405 – the HTTP method is not allowed.’
WHEN ‘8024401B’ THEN ‘WU_E_PT_HTTP_STATUS_PROXY_AUTH_REQ Same as HTTP status 407 – proxy authentication is required.’
WHEN ‘8024401C’ THEN ‘WU_E_PT_HTTP_STATUS_REQUEST_TIMEOUT Same as HTTP status 408 – the server timed out waiting for the request.’
WHEN ‘8024401D’ THEN ‘WU_E_PT_HTTP_STATUS_CONFLICT Same as HTTP status 409 – the request was not completed due to a conflict with the current state of the resource.’
WHEN ‘8024401E’ THEN ‘WU_E_PT_HTTP_STATUS_GONE Same as HTTP status 410 – requested resource is no longer available at the server.’
WHEN ‘8024401F’ THEN ‘WU_E_PT_HTTP_STATUS_SERVER_ERROR Same as HTTP status 500 – an error internal to the server prevented fulfilling the request.’
WHEN ‘80244020’ THEN ‘WU_E_PT_HTTP_STATUS_NOT_SUPPORTED Same as HTTP status 500 – server does not support the functionality required to fulfill the request.’
WHEN ‘80244021’ THEN ‘WU_E_PT_HTTP_STATUS_BAD_GATEWAY Same as HTTP status 502 – the server, while acting as a gateway or proxy, received an invalid response from the upstream server it accessed in attempting to fulfill the request.’
WHEN ‘80244022’ THEN ‘WU_E_PT_HTTP_STATUS_SERVICE_UNAVAIL Same as HTTP status 503 – the service is temporarily overloaded.’
WHEN ‘80244023’ THEN ‘WU_E_PT_HTTP_STATUS_GATEWAY_TIMEOUT Same as HTTP status 503 – the request was timed out waiting for a gateway.’
WHEN ‘80244024’ THEN ‘WU_E_PT_HTTP_STATUS_VERSION_NOT_SUP Same as HTTP status 505 – the server does not support the HTTP protocol version used for the request.’
WHEN ‘80244025’ THEN ‘WU_E_PT_FILE_LOCATIONS_CHANGED Operation failed due to a changed file location; refresh internal state and resend.’
WHEN ‘80244026’ THEN ‘WU_E_PT_REGISTRATION_NOT_SUPPORTED Operation failed because Windows Update Agent does not support registration with a non-WSUS server.’
WHEN ‘80244027’ THEN ‘WU_E_PT_NO_AUTH_PLUGINS_REQUESTED The server returned an empty authentication information list.’
WHEN ‘80244028’ THEN ‘WU_E_PT_NO_AUTH_COOKIES_CREATED Windows Update Agent was unable to create any valid authentication cookies.’
WHEN ‘80244029’ THEN ‘WU_E_PT_INVALID_CONFIG_PROP A configuration property value was wrong.’
WHEN ‘8024402A’ THEN ‘WU_E_PT_CONFIG_PROP_MISSING A configuration property value was missing.’
WHEN ‘8024402B’ THEN ‘WU_E_PT_HTTP_STATUS_NOT_MAPPED The HTTP request could not be completed and the reason did not correspond to any of the WU_E_PT_HTTP_* error codes.’
WHEN ‘8024402C’ THEN ‘WU_E_PT_WINHTTP_NAME_NOT_RESOLVED Same as ERROR_WINHTTP_NAME_NOT_RESOLVED – the proxy server or target server name cannot be resolved.’
WHEN ‘8024502D’ THEN ‘WU_E_PT_SAME_REDIR_ID Windows Update Agent failed to download a redirector cabinet file with a new redirectorId value from the server during the recovery.’
WHEN ‘8024502E’ THEN ‘WU_E_PT_NO_MANAGED_RECOVER A redirector recovery action did not complete because the server is managed.’
WHEN ‘8024402F’ THEN ‘WU_E_PT_ECP_SUCCEEDED_WITH_ERRORS External cab file processing completed with some errors.’
WHEN ‘80244030’ THEN ‘WU_E_PT_ECP_INIT_FAILED The external cab processor initialization did not complete.’
WHEN ‘80244031’ THEN ‘WU_E_PT_ECP_INVALID_FILE_FORMAT The format of a metadata file was invalid.’
WHEN ‘80244032’ THEN ‘WU_E_PT_ECP_INVALID_METADATA External cab processor found invalid metadata.’
WHEN ‘80244033’ THEN ‘WU_E_PT_ECP_FAILURE_TO_EXTRACT_DIGEST The file digest could not be extracted from an external cab file.’
WHEN ‘80244034’ THEN ‘WU_E_PT_ECP_FAILURE_TO_DECOMPRESS_CAB_FILE An external cab file could not be decompressed.’
WHEN ‘80244035’ THEN ‘WU_E_PT_ECP_FILE_LOCATION_ERROR External cab processor was unable to get file locations.’
WHEN ‘80244FFF’ THEN ‘WU_E_PT_UNEXPECTED A communication error not covered by another WU_E_PT_* error code.’
WHEN ‘80242000’ THEN ‘WU_E_UH_REMOTEUNAVAILABLE A request for a remote update handler could not be completed because no remote process is available.’
WHEN ‘80242001’ THEN ‘WU_E_UH_LOCALONLY A request for a remote update handler could not be completed because the handler is local only.’
WHEN ‘80242002’ THEN ‘WU_E_UH_UNKNOWNHANDLER A request for an update handler could not be completed because the handler could not be recognized.’
WHEN ‘80242003’ THEN ‘WU_E_UH_REMOTEALREADYACTIVE A remote update handler could not be created because one already exists.’
WHEN ‘80242004’ THEN ‘WU_E_UH_DOESNOTSUPPORTACTION A request for the handler to install (uninstall) an update could not be completed because the update does not support install (uninstall).’
WHEN ‘80242005’ THEN ‘WU_E_UH_WRONGHANDLER An operation did not complete because the wrong handler was specified.’
WHEN ‘80242006’ THEN ‘WU_E_UH_INVALIDMETADATA A handler operation could not be completed because the update contains invalid metadata.’
WHEN ‘80242007’ THEN ‘WU_E_UH_INSTALLERHUNG An operation could not be completed because the installer exceeded the time limit.’
WHEN ‘80242008’ THEN ‘WU_E_UH_OPERATIONCANCELLED An operation being done by the update handler was cancelled.’
WHEN ‘80242009’ THEN ‘WU_E_UH_BADHANDLERXML An operation could not be completed because the handler-specific metadata is invalid.’
WHEN ‘8024200A’ THEN ‘WU_E_UH_CANREQUIREINPUT A request to the handler to install an update could not be completed because the update requires user input.’
WHEN ‘8024200B’ THEN ‘WU_E_UH_INSTALLERFAILURE The installer failed to install (uninstall) one or more updates.’
WHEN ‘8024200C’ THEN ‘WU_E_UH_FALLBACKTOSELFCONTAINED The update handler should download self-contained content rather than delta-compressed content for the update.’
WHEN ‘8024200D’ THEN ‘WU_E_UH_NEEDANOTHERDOWNLOAD The update handler did not install the update because it needs to be downloaded again.’
WHEN ‘8024200E’ THEN ‘WU_E_UH_NOTIFYFAILURE The update handler failed to send notification of the status of the install (uninstall) operation.’
WHEN ‘8024200F’ THEN ‘WU_E_UH_INCONSISTENT_FILE_NAMES The file names contained in the update metadata and in the update package are inconsistent.’
WHEN ‘80242010’ THEN ‘WU_E_UH_FALLBACKERROR The update handler failed to fall back to the self-contained content.’
WHEN ‘80242011’ THEN ‘WU_E_UH_TOOMANYDOWNLOADREQUESTS The update handler has exceeded the maximum number of download requests.’
WHEN ‘80242012’ THEN ‘WU_E_UH_UNEXPECTEDCBSRESPONSE The update handler has received an unexpected response from CBS.’
WHEN ‘80242013’ THEN ‘WU_E_UH_BADCBSPACKAGEID The update metadata contains an invalid CBS package identifier.’
WHEN ‘80242014’ THEN ‘WU_E_UH_POSTREBOOTSTILLPENDING The post-reboot operation for the update is still in progress.’
WHEN ‘80242015’ THEN ‘WU_E_UH_POSTREBOOTRESULTUNKNOWN The result of the post-reboot operation for the update could not be determined.’
WHEN ‘80242016’ THEN ‘WU_E_UH_POSTREBOOTUNEXPECTEDSTATE The state of the update after its post-reboot operation has completed is unexpected.’
WHEN ‘80242017’ THEN ‘WU_E_UH_NEW_SERVICING_STACK_REQUIRED The OS servicing stack must be updated before this update is downloaded or installed.’
WHEN ‘80242FFF’ THEN ‘WU_E_UH_UNEXPECTED An update handler error not covered by another WU_E_UH_* code.’
WHEN ‘C80003F3’ THEN ‘hrOutOfMemory: The computer is out of memory. Generally reported when WSUS try to initialize its datastore.’
WHEN ‘C800042D’ THEN ‘hrVersionStoreOutOfMemory: Generally reported when the WUA is unable to update %WINDIR%\SoftwareDistribution folder.’
WHEN ‘80040692’ THEN ‘ConfigMgr Custom Error: Group Policy conflict. Check domain GPOs applying to this machine.’
WHEN ‘80040693’ THEN ‘ConfigMgr Custom Error: WUA version is lower than expected. Upgrade WUA.’
WHEN ‘80040708’ THEN ‘ConfigMgr Custom Error: Software Updates Install not required.’
WHEN ‘80040709’ THEN ‘ConfigMgr Custom Error: Failed to resume the monitoring of the process.’
WHEN ‘8004070A’ THEN ‘ConfigMgr Custom Error: Invalid command line.’
WHEN ‘8004070B’ THEN ‘ConfigMgr Custom Error: Failed to create process.’
WHEN ‘8004070C’ THEN ‘ConfigMgr Custom Error: Software update execution timeout.’
WHEN ‘8004070D’ THEN ‘ConfigMgr Custom Error: Software update failed when attempted.’
WHEN ‘8004070E’ THEN ‘ConfigMgr Custom Error: Empty command line specified.’
WHEN ‘8004070F’ THEN ‘ConfigMgr Custom Error: Invalid updates installer path.’
WHEN ‘80040710’ THEN ‘ConfigMgr Custom Error: Failed to compare process creation time.’
WHEN ‘80040711’ THEN ‘ConfigMgr Custom Error: Software updates deployment not active yet; for example, start time is in the future.’
WHEN ‘80040712’ THEN ‘ConfigMgr Custom Error: A system restart is required to complete the installation.’
WHEN ‘80040713’ THEN ‘ConfigMgr Custom Error: Software updates detection results not received yet.’
WHEN ‘80040714’ THEN ‘ConfigMgr Custom Error: User based install not allowed as system restart is pending.’
WHEN ‘80040715’ THEN ‘ConfigMgr Custom Error: No applicable updates specified in user install request.’
WHEN ‘80040154’ THEN ‘ConfigMgr Custom Error: Class not registered. Try repairing the ConfigMgr client.’
WHEN ‘80040668’ THEN ‘ConfigMgr Custom Error: Software update still detected as actionable after apply.’
WHEN ‘80040600’ THEN ‘ConfigMgr Custom Error: Scan Tool Policy not found.’
WHEN ‘80040602’ THEN ‘ConfigMgr Custom Error: Out of cache space.’
WHEN ‘80040603’ THEN ‘ConfigMgr Custom Error: The ScanTool Policy has been removed, this prevents completion of Scan Operations. (E_SCANTOOL_NOTFOUND_INJOBQUEUE)’
WHEN ‘80040604’ THEN ‘ConfigMgr Custom Error: Scan Tool has been Removed. (E_FAIL_SCAN_TOOL_REMOVED)’
WHEN ‘80040605’ THEN ‘ConfigMgr Custom Error: Scan Tool Policy not found. (E_FAIL_OFFLINE_SCAN_HISTORY_NOT_FOUND)’
WHEN ‘80040608’ THEN ‘ConfigMgr Custom Error: Out of cache space.’
WHEN ‘80008201’ THEN ‘ConfigMgr Custom Error: Out of cache space.’
WHEN ‘80008202’ THEN ‘ConfigMgr Custom Error: Cache size is smaller than requested content”s size.’
WHEN ‘80004005’ THEN ‘ConfigMgr Custom Error: Access denied. Possible corrupt local policy.’
WHEN ‘80040694’ THEN ‘ConfigMgr Custom Error: Failed to add update source for WUAgent.’
WHEN ‘8007000E’ THEN ‘Win32 Error: Not enough storage is available to complete this operation.’
WHEN ‘800705B4’ THEN ‘Win32 Error: The operation returned because the timeout period expired.’
WHEN ‘80070050’ THEN ‘Win32 Error: The file already exists.’
WHEN ‘80070005’ THEN ‘Win32 Error: Access Denied.’
WHEN ‘8007041D’ THEN ‘Win32 Error: The service did not respond to the start or control request in a timely fashion.’
WHEN ‘80004002’ THEN ‘Win32 Error: No such interface supported.’
WHEN ‘80072EE2’ THEN ‘ERROR_INTERNET_TIMEOUT: The request has timed out. Server too busy. Try again later.’
WHEN ‘80072EEC’ THEN ‘ERROR_INTERNET_SHUTDOWN: WinINet support is being shut down or unloaded.’
WHEN ‘80072EFE’ THEN ‘ERROR_INTERNET_TIMEOUT: Internet connection interruption.’
WHEN ‘80072F76’ THEN ‘ERROR_INTERNET_TIMEOUT: Internet connection interruption.’
WHEN ‘80072F84’ THEN ‘ERROR_INTERNET_SERVER_UNREACHABLE: The Web site or server indicated is unreachable.’
WHEN ‘80072F7D’ THEN ‘ERROR_INTERNET_SECURITY_CHANNEL_ERROR: The application experienced an internal error loading the SSL libraries.’
WHEN ‘80072F89’ THEN ‘ERROR_INTERNET_SEC_INVALID_CERT: SSL certificate is invalid.’
WHEN ‘80072F8A’ THEN ‘ERROR_INTERNET_SEC_CERT_REVOKED: SSL certificate was revoked.’
WHEN ‘80072F19’ THEN ‘ERROR_INTERNET_SEC_CERT_REV_FAILED: Certificate revocation check failed.’
WHEN ‘80072F17’ THEN ‘ERROR_INTERNET_SEC_CERT_ERRORS: The SSL certificate contains errors.’
WHEN ‘80072F05’ THEN ‘ERROR_INTERNET_SEC_CERT_DATE_INVALID: SSL certificate date that was received from the server is bad. The certificate is expired.’
WHEN ‘80072F06’ THEN ‘ERROR_INTERNET_SEC_CERT_CN_INVALID: SSL certificate common name (host name field) is incorrect—for example, if you entered www.server.com and the common name on the certificate says www.different.com.’
ELSE ‘Unknown Error’
END,
NumberOfComputers = count(*),
UpdateSourceID = @UpdateSourceID,
CollectionID = @CollID
from fn_rbac_SoftwareUpdateSource(@UserSIDs) sus
join fn_rbac_UpdateScanStatus(@UserSIDs) uss
on sus.UpdateSource_ID=uss.UpdateSource_ID
and uss.LastScanState in (5,6)
and uss.LastStatusMessageID is not null
join fn_rbac_ClientCollectionMembers(@UserSIDs) ccm on ccm.ResourceID=uss.ResourceID and ccm.CollectionID=@CollID
left join fn_rbac_AdvertisementStatusInformation(@UserSIDs) asi on uss.LastStatusMessageID&0x0000FFFF=asi.MessageID
left join fn_rbac_AdvertisementStatusInformation(@UserSIDs) statusinfo on statusinfo.MessageID=nullif(uss.LastStatusMessageID&0x0000FFFF, 0)
where sus.UpdateSource_UniqueID=@UpdateSourceID and sus.IsExpired=0
group by uss.LastStatusMessageID&0x0000FFFF, isnull(uss.LastErrorCode,0), asi.MessageName
order by 6 desc, 1, 3

If you would like to run this from your SQL Server Management Studio and need values for your variables you can use the following at the top of your query.

declare @UpdateSourceID varchar (50); set @UpdateSourceID = ‘YOUR_UPDATESOURCEID’
declare @CollID varchar (8); set @CollID = ‘SMS00001’
Declare @UserSIDs As Varchar(25); Set @UserSIDs = ‘Disabled’

PowerShell – Ping all SCCM servers

Part of daily environment health checks is to verify that all SCCM infrastructure servers are reachable on the network.  Usually you can verify this by viewing the Monitoring tab in the SCCM console, but in cases where you have multiple standalone distribution point devices you may want to simply ping them to check their availability.  This PowerShell script will help you to do this task without having to write a batch file to ping each device.

# Script to ping all SCCM servers

$ServerName = “SERVERNAME1”, “SERVERNAME2”, “SERVERNAME3”, “SERVERNAME4”

foreach ($Server in $ServerName) {
if (test-Connection -ComputerName $Server -Count 2 -Quiet ) {
write-Host “$Server is online ” -ForegroundColor Green
} else
{ Write-Warning “$Server is offline ”
}
}

 

SCCM Collection Query – Duplicate host names

SCCM sometimes creates duplicate host name records.  When this happens you will see 2 or more records in your SCCM console showing the same host name or computer name, but with different GUID’s and different resource id’s.  In most cases one record will show as being an active client while the other shows it is not a client.  It is usually a safe bet to remove the non-client record, but be sure to check both records for the creation date as that may help to decide which one is the correct one to keep.

This collection query will show you the duplicate host name records.

select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,R.ResourceDomainORWorkgroup,R.Client from SMS_R_System as r   full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId   full join SMS_R_System as s2 on s2.Name = s1.Name   where s1.Name = s2.Name and s1.ResourceId != s2.ResourceId

I use this collection as a maintenance collection so that I can check for duplicate host names daily.  If you don’t remove the duplicate SCCM does not know which record should receive policies.  Once a single instance remains you will see that SCCM downloads policies to it.

SCCM Report / SQL Query – SCCM Maintenance Tasks

SCCM has a number of predefined maintenance tasks that runs either daily or weekly depending on how you configured them.  This is a SQL query that will display the status of these tasks.  Note that this query will show the status for all tasks for the past 7 days which will allow you to see the weekly tasks as well.

SELECT
TaskName,
LastStartTime,
LastCompletionTime,
CASE WHEN CompletionStatus = ‘1’ THEN ‘Task failed’ ELSE ‘Task successful’ END AS ‘Status’
FROM
dbo.SQLTaskStatus
WHERE
(NOT (LastStartTime LIKE CONVERT(DATETIME, ‘1980-01-01 00:00:00’, 102)))
AND LastCompletionTime < (getdate() -7)

If you want to show how long each task took to complete you can run the following basic query.

SELECT TaskName, TaskType, LastStartTime, LastCompletionTime, CompletionStatus, RunNow, FLOOR(DATEDIFF(ss, LastStartTime, LastCompletionTime) / 3600) AS Hours,
FLOOR(DATEDIFF(ss, LastStartTime, LastCompletionTime) / 60) – FLOOR(DATEDIFF(ss, LastStartTime, LastCompletionTime) / 3600) * 60 AS Minutes,
FLOOR(DATEDIFF(ss, LastStartTime, LastCompletionTime)) – FLOOR(DATEDIFF(ss, LastStartTime, LastCompletionTime) / 60) * 60 AS TotalSeconds
FROM dbo.SQLTaskStatus

As part of a daily health check I created an SCCM report from the query that will give me a view of the success of the tasks to keep my SCCM environment healthy.  These queries will work on both SCCM 2007 and SCCM 2012

PowerShell – Manage SCCM Inbox counts

A good practice in SCCM is to check your Inbox folder file counts daily for any backlog that may point to a potential component problem.  This task is easily achieved through a simple PowerShell command.  The SMS_Inbox_Monitor information is found in WMI.  If you would like to get an Inbox file count you can run the following PowerShell command.

Get-WmiObject -Class Win32_PerfFormattedData_SMSINBOXMONITOR_SMSInbox -ComputerName SERVERNAME1, SERVERNAME2, SERVERNAME3 | Select-Object -Property PSComputerName, Name, FileCurrentCount

This will work for both SCCM 2007 and SCCM 2012.  I like to save this information to my SQL database so that I can keep track of the weekly counts.  Here is a sample script how to import this information into your database.

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
# Import SCCM Inbox Counts
#
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

 

$events = Get-WmiObject -Class Win32_PerfFormattedData_SMSINBOXMONITOR_SMSInbox -ComputerName SERVERNAME1, SERVERNAME2, SERVERNAME3 | Select-Object -Property PSComputerName, Name, FileCurrentCount
$connectionString = “Data Source=SCCM_SQL_SERVER;Integrated Security=true;Initial Catalog=SCCM_Support;”
$bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $connectionString
$bulkCopy.DestinationTableName = “SCCM_Inbox_Count”
$dt = New-Object “System.Data.DataTable”

# build the datatable
$cols = $events | get-member -MemberType NoteProperty | select -Expand Name
foreach ($col in $cols) {$null = $dt.Columns.Add($col)}

foreach ($event in $events)
{
$row = $dt.NewRow()
foreach ($col in $cols) { $row.Item($col) = $event.$col }
$dt.Rows.Add($row)
}

# Write to the database!
$bulkCopy.WriteToServer($dt)

SCCM Report / SQL Query – Show packages with no deployments or task sequences linked

General maintenance and housekeeping of your SCCM 2012 environment is important to keep your environment healthy.  Packages and applications can fill up drive space quickly if they are not managed and maintained.  This query for SCCM 2012 will display a list of packages that is not being deployed and are not linked to any task sequences.  Generally this means they are not being used and may be retired.

SELECT pkg.Name as [Package Name], pkg.Version, pkg.PackageID,
CASE pkg.PackageType
WHEN 0 THEN ‘Software Distribution Package’
WHEN 3 THEN ‘Driver Package’
WHEN 4 THEN ‘Task Sequence Package’
WHEN 5 THEN ‘Software Update Package’
WHEN 6 THEN ‘Device Settings Package’
WHEN 7 THEN ‘Virtual Package’
WHEN 257 THEN ‘Image Package’
WHEN 258 THEN ‘Boot Image Package’
WHEN 259 THEN ‘OS Install Package’
END AS [Package Type], adv.AdvertisementID, tsp.Name as [TS Name]
FROM v_Package pkg
LEFT JOIN v_Advertisement adv on pkg.PackageID=adv.PackageID
LEFT JOIN v_TaskSequencePackageReferences tsr on pkg.PackageID=tsr.RefPackageID
LEFT JOIN v_TaskSequencePackage tsp on tsr.PackageID=tsp.PackageID
WHERE adv.AdvertisementID is null
AND tsp.Name is null
ORDER BY pkg.Name

This query will return information for all packages regardless of their package type, but you will notice that I added values for each package type so that you can differentiate between them.  Package type 5 for Software Updates never have deployments and task sequences associated to them so you can filter these out in your query.  Remember that in Software Updates you will deploy a Software Update Group to your devices and not a package.  It is however included in this query for reference.

SCCM Report / SQL query – WSUS Server sync status

SCCM 2012 has a menu item in the console under the Monitoring tab that will display the Software Update Point Synchronization Status.  Use of the SQL query below to display the same information in a reporting format.

SELECT SiteCode, WSUSServerName, WSUSSourceServer, SyncCatalogVersion, LastSuccessfulSyncTime,
CASE [LastSyncState]
WHEN 6700 THEN ‘WSUS Sync Manager Error’
WHEN 6701 THEN ‘WSUS Synchronization Started’
WHEN 6702 THEN ‘WSUS Synchronization Done’
WHEN 6703 THEN ‘WSUS Synchronization Failed’
WHEN 6704 THEN ‘WSUS Synchronization In Progress Phase Synchronizing WSUS Server’
WHEN 6705 THEN ‘WSUS Synchronization In Progress Phase Synchronizing SMS Database’
WHEN 6706 THEN ‘WSUS Synchronization In Progress Phase Synchronizing Internet facing WSUS Server’
WHEN 6707 THEN ‘Content of WSUS Server is out of sync with upstream server’
WHEN 6709 THEN ‘SMS Legacy Update Synchronization started’
WHEN 6710 THEN ‘SMS Legacy Update Synchronization done’
WHEN 6711 THEN ‘SMS Legacy Update Synchronization failed’
END AS ‘Last Sync State’, LastSyncStateTime
FROM vSMS_SUPSyncStatus

The following query will work for SCCM 2007 systems.

SELECT uss.SiteCode, si.ServerName, si.SiteName,
uss.ContentVersion, uss.SyncTime
FROM update_syncstatus uss, v_Site si
WHERE uss.SiteCode=si.SiteCode
AND uss.contentversion < 999999
ORDER BY SyncTime