SCCM – Add a description to the error code in sccm reports

In some of the reports in sccm the result will display a error code for troubleshooting purpose, but often they dont provide any description about this error code.  So here’s a example on how you can get more detail on these errors. Note that in this example I will use a report on Software update – client health report.

1- You must download the following file, convert it to csv, replace all the comma by two single quote, import the file as a flat file in your sccm database, then assign the appropriate permission on the newly created table.  (reference; http://ittv.net/VideoPlayer/TabId/57/VideoId/1574/SCCM-2007-Client-Health-Monitoring.aspx)

– Download Custom ConfigMgr Error Code;

https://skydrive.live.com/?cid=80514c55d60387d4&id=80514C55D60387D4%211522

2- Create a reports call Software Update client Health Reports;

*****************************************************************

SQL Query (make sure to change ConfigMgr_Error_Descriptions to reflect the newly created table)

Select Distinct

sys.Netbios_name0,

CASE WHEN SYS.Active0 = 1 THEN ‘Yes’ ELSE ‘No’ END as ‘Active’,

DATEDIFF(DAY, LastScanTime, GetDate()) as ‘Last SUP Scan(DAYS)’,

DateDiff(D, OS.LastBootUpTime0, GetDate()) ‘Last Boot (Days)’,

DATEADD(mi,(SYS.lastLogontimeStamp0  / 600000000) – 157258080,0)     AS ‘Lastogon TS’,

CASE WHEN LastErrorCode = 0 THEN ‘Successfull’ ELSE     CAST(LastErrorCode as varchar(32)) END as ‘Error Code’,

CASE WHEN CU.Description LIKE ” THEN ‘Unknown’

WHEN CU.Description is NULL THEN ‘*’

ELSE CAST (CU.Description as Varchar (64)) END as ‘Description’,

CASE WHEN CU.[Symbolic Name] is Null Then ‘*’ ELSE CAST (CU.[Symbolic Name] as varchar(32)) END as ‘Symbolic Name’,

LastScanPackageLocation as ‘Last Scan Location’,

LastScanPackageVersion as ‘Last Scan Pakage’,

LastWUAVersion as WUAVersion,

st.StateName as ‘Status’

from v_updateScanStatus up

join v_r_system sys on sys.resourceid = up.resourceid

left join ConfigMgr_Error_Descriptions CU on up.LastErrorCode=cu.errorcode

left join v_Gs_Operating_System OS on sys.resourceid = os.ResourceID

left join v_StateNames st on st.TopicType = 501

and st.StateID = (case when (isnull(LastScanState, 0)=0

and Left(isnull(sys.Client_Version0, ‘4.0’),1)<‘4’)

then 7 else isnull(LastScanState, 0) end)

left join v_ClientCollectionMembers ccm on sys.ResourceID = ccm.ResourceID

WHERE ccm.collectionID = @CollID

Prompt

Name = CollID

SQL Query

begin

if (@__filterwildcard = ”)

select CollectionID as CollectionID, Name as CollectionName from v_Collection order by Name

else

select CollectionID as CollectionID, Name as CollectionName from v_Collection

WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard

order by Name

end

*****************************************************************

Advertisements

2 thoughts on “SCCM – Add a description to the error code in sccm reports

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s