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

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

SCCM – Security updates Compliance Overview report

view https://anotherblabla.wordpress.com/2012/03/22/sccm-security-updates-compliance-overview-report-v2/ for some enhancements

Here’s some reports that I use to monitor the level of compliance for security updates and this by collection. Also it’s possibe to drill down and see which security updates is missing.

 

thanks to Garth Jones and Anoop for these informations, which has been very helpfull when I have created those reports

http://anoopmannur.wordpress.com/2011/10/12/configmgr-sccm-software-updatepatching-useful-report-on-client-health/

http://smsug.ca/blogs/garth_jones/archive/2009/02/25/patch-compliance-progression-report.aspx

SQLQueries

1- create a report called  Security Updates Detail.

Report Sql Query

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

declare @RscID int;

select @RscID=ResourceID from v_R_System where ((Name0 = @MachineName) and (Active0 = 1));

select

catinfo.CategoryInstanceName as Vendor,

catinfo2.CategoryInstanceName as UpdateClassification,

ui.BulletinID as BulletinID,

ui.ArticleID as ArticleID,

ui.Title as Title,

Targeted=(case when ctm.ResourceID is not null then ‘*’ else ” end),

Installed=(case when css.Status=3 then ‘*’ else ” end),

IsRequired=(case when css.Status=2 then ‘*’ else ” end),

ui.CI_UniqueID as UniqueUpdateID,

ui.InfoURL as InformationURL

from v_UpdateComplianceStatus css

join v_UpdateInfo ui on ui.CI_ID=css.CI_ID

join v_CICategories_All catall on catall.CI_ID=ui.CI_ID

join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName=’Company’

join v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID

join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName=’UpdateClassification’

left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID

left join (

select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a

join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID

group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID

where  css.ResourceID = @RscID and css.Status=2 and catinfo2.CategoryInstanceName like ‘Security Updates’

order by catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID

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

Prompt

Name = MachineName

Sql Query

begin

if (@__filterwildcard = ”)

select distinct Name0 from v_R_System WHERE isnull(Obsolete0,0)<>1 order by Name0

else

select distinct Name0 from v_R_System

WHERE Name0 like @__filterwildcard

and isnull(Obsolete0,0)<>1 order by Name0

end

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

2- Create a report called Software Updates Compliance Status by Collection and configure the link to the report created in step 1

Report SQL Query

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

SELECT

rs.Netbios_name0 as ‘PC Name’,

case

when (sum(case when UCS.status=2 then 1 else 0 end))>0 then (cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ‘ Security Updates Missing’)

else ‘Good Client’

end as ‘Updates Status’,

rs.User_Name0 as ‘Last Logged On User’,

asite.SMS_Assigned_Sites0 as ‘SMS Site’,

rs.Client_Version0 as ‘Client Version’,

ws.lasthwscan as ‘Last Hardware Scan’,

uss.LastScanPackageLocation as ‘Last Scan Location’,

uss.LastScanPackageVersion as ‘Last Scan Pakage’,

st.StateName as ‘Status’,

Convert(VarChar(10), OS.LastBootUpTime0, 101) ‘Last Boot Date’,

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

from v_ClientCollectionMembers ccm

join v_R_System rs on rs.ResourceID = ccm.ResourceID

left outer join v_UpdateComplianceStatus  UCS on UCS.ResourceID = ccm.ResourceID

join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID

join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName=’UpdateClassification’

left join v_RA_System_SMSAssignedSites asite on asite.ResourceID = ccm.ResourceID

left join v_gs_workstation_status ws on ws.resourceid=rs.resourceid

left join v_UpdateScanStatus uss on ws.resourceid = uss.ResourceID

left join v_StateNames st on st.TopicType = 501 and st.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rs.Client_Version0, ‘4.0’),

1)<‘4’) then 7 else isnull(uss.LastScanState, 0) end)

left join v_Gs_Operating_System OS on ws.resourceid = OS.ResourceID

where

ccm.CollectionID=@CollID

and catinfo2.CategoryInstanceName like ‘Security Updates’

Group By

rs.Netbios_name0,

rs.User_Name0 ,

asite.SMS_Assigned_Sites0 ,

rs.Client_Version0,

ws.lasthwscan,

uss.LastScanPackageLocation,

uss.LastScanPackageVersion ,

st.StateName ,

OS.LastBootUpTime0,

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

Prompt

Name = CollID

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

Software Update – Pending Reboot Notification Based on OS Language (Updated Script)

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

03-02-2012

Updated scripts;

Now the script will use the windows updates icon(detect which icon to use  for  WinXP, 2003, Win 7 and 2008) in the systray and allow users to reboot from a onclick function and Show has traytip each 15 mins. Moreover, It will also detect the OS language (in this case French and English)  and use it for the notification.

Also, if you’re using the hta script (you must uncomment the ligne,   ;ShellExecuteWait(@ScriptDir & “\PromptReboot.hta”) in the autoit script) , this will allow you to configure a maximum number times users can postpone the reboot, countdown for automatic reboot( changes must be done in the CloseWindow sub).

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

Recently, one of my client asked me to move their patch management process from WSUS to SCCM and that for taking advantage of the reporting, distribution of patches in a multisite environment, etc. But here’s some limitations that I’ve encountered.

First, I can’t either use Maintenance Windows or force reboot on the majority of the workstation. Simply because it’s an 24/7 production environment and the user must be able to determine the proper time for the reboot … even if this is not a good practice.

Then, the notifications should be Display in the language of the OS even if the agent is Sccm English.


So, here the solution I’ve came up with… maybe you will find it usefull if you’re facing a situation like this.

Basicly, I used Autoit and HTA Edit, to create a script which will detect if there is some pending reboot due to a software update deployment. And if this is the case, it will notify the user. A icon will be display in the systray and will notify the user each 10 minutes (if you want to change the value, change the sleep in the autoit Script) and the hta application will ask the user for a reboot.

1- Hide notification for collections containing all the target workstations by using the following script;

http://scug.dk/blogs/configurationmanager/archive/2010/02/19/hide-and-show-software-update-notifications.aspx

thanks to Kent Agerlund for this

2- Create a collection for device in pending reboot due to software update (you maybe need to exclude some collections, ex, srv collection or create an empty collection which is restricted to query the collection created in step 1)

http://joshuasmueller.wordpress.com/2010/05/04/reboot-pending/

or

http://myitforum.com/cs2/blogs/idany/archive/2008/12/04/collections-based-on-software-updates-deployment-status-in-configuration-manager.aspx

3- Then create your deployment template, which should be configure to supress the reboot and hide the notification to the user.

4- Use AutoIt to compile the following script, create a software deployment and advertise it to the appropriate collection with the rerun settings you needs.

  • Make sure to change the ICO file path
  • note, if you compile the script in 32-bit version and execute on a 64-bit position, you must copy the DLL to the directory the script, otherwise it can not find the icon

    If needed, install autoit from; http://www.autoitscript.com/site/autoit/downloads/

Download Script(RebootNotification)

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

; AutoIt Version: 3.0

; Language:       English and French

; Platform:       Win9x/NT/2000/XP/Win7

; Author:         Tom Watson

; Modify :                        SBro

; Script Function:

;   Check If there is a pending reboot since a updates deployment

;   Displays Systray Notification Based on OS language

;

#include <Constants.au3>

#NoTrayIcon

Opt(“TrayOnEventMode”, 1)

Opt(“TrayMenuMode”, 1) ; Default tray menu items (Script Paused/Exit) will not be shown.

AutoItSetOption ( “TrayIconHide” , 1 )

$aPList = ProcessList(@ScriptName)

For $i = 1 To $aPList[0][0]

If $aPList[$i][1] <> @AutoItPID Then Exit

Next

Select

Case StringInStr(“WIN_7,WIN_2008”, @OSVersion)

TraySetIcon(“wucltux.dll”, 1)

TraySetState(1)

Break(0)

Case StringInStr(“WIN_XP,WIN_2003”, @OSVersion)

TraySetIcon(“wucltui.dll”, 4)

TraySetState(1)

Break(0)

EndSelect

For $i= 1 to 5 ; Number of subkey SU who is actually Pending for Reboot

$var = RegEnumKey(“HKEY_LOCAL_MACHINE\SOFTWARE\microsoft\SMS\Mobile Client\Updates Management\Handler\UpdatesRebootStatus”, $i)

If @error <> 0 then ExitLoop

Select

Case StringInStr(“040c,0c0c”, @OSLang) ; French OS

TraySetToolTip (“VOUS DEVEZ REDÉMARRER VOTRE PC”)

TraySetOnEvent($TRAY_EVENT_PRIMARYDOWN, “MsgFrench”)

While 1 = 1

TrayTip(“Service Technique”, “Des mises à jours critiques et de sécurités ont été installées sur votre Pc. Sauvegardez vos données et redémarrer dès que possible.”, 12, 1)

Sleep (20000)

TrayTip(“”,””,0)

;ShellExecute(@ScriptDir & “\PromptReboot-fr.hta”)

Sleep(900000)

WEnd

Case StringInStr(“0409,0809,0c09,1009,1409,1809,1c09,2009,2409,2809,2c09,3009,3409”, @OSLang) ; US-EN OS

TraySetToolTip (“PLEASE RESTART YOUR SYSTEM”)

TraySetOnEvent($TRAY_EVENT_PRIMARYDOWN, “MsgEnglish”)

While 1 = 1

TrayTip(“Technical Support “,”Critical and security updates have been installed on your system. Please save your work and restart your system as soon as possible.”, 12, 1)

Sleep (20000)

TrayTip(“”,””,0)

;ShellExecuteWait(@ScriptDir & “\PromptReboot.hta”)

Sleep(900000)

WEnd

EndSelect

Next

Func MsgFrench()

Select

Case @TRAY_ID = $TRAY_EVENT_PRIMARYDOWN

$MsgBoxfr = MsgBox(260, “Question”, “Voulez-Vous Redémarrer Votre Ordinateur Maintenant”)

If $MsgBoxfr = 6 Then

Shutdown (6, “Software update iniated shutdown” )

EndIf

EndSelect

EndFunc

Func MsgEnglish()

Select

Case @TRAY_ID = $TRAY_EVENT_PRIMARYDOWN

$MsgBoxEn = MsgBox(260, “Question”, “Would You Like To Restart Your Computer Now”)

If $MsgBoxEn = 6 Then

Shutdown (6, “Software update iniated shutdown” )

EndIf

EndSelect

EndFunc

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

Also, here’s where i found the basic idea for the script;

http://myitforum.com/cs2/blogs/tom_watson/archive/2010/01/04/reboot-nag-using-autoit.aspx

5 – If needed, you can add the following HTA script to configure a maximum number times users can postpone the reboot, countdown for automatic reboot( changes must be done in the CloseWindow sub). Also, the hta script must be in the same directory as the autoit Script and note that the max postpone and countdown option as been disabled in the script.

Download Script (PromptReboot)

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

promptreboot-fr.hta

Use the same as the promptreboot.hta, but change the notification.