SCCM – Usefull Software update reports

Here are two reports for software updates. The first one, to obtain a count on the number of critical updates and security missing by PC and the other to get the details of those missing updates by classification.

1- create a new report called Missing Software Updates Details with the following Sql Query and Prompt;

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

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),

Deadline=cdl.Deadline,

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=@Status and catinfo2.CategoryInstanceName = @updateclass

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

Prompt, MachineName;

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

Prompt, UpdateClass;

begin

if (@__filterwildcard = ”)

select distinct CategoryInstanceName as UpdateClassification from v_CategoryInfo where CategoryTypeName=’UpdateClassification’ order by CategoryInstanceName

else

select distinct CategoryInstanceName as UpdateClassification from v_CategoryInfo where CategoryTypeName=’UpdateClassification’and CategoryInstanceName like @__filterwildcard

order by CategoryInstanceName

end

Prompt, Status;

begin

if (@__filterwildcard = ”)

select distinct Status from v_UpdateComplianceStatus order by Status

else

select distinct Status from v_UpdateComplianceStatus

WHERE Status like @__filterwildcard

order by Status

end

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

2- Create another reports named Missing Software Updates Count, with the following SQL query and Prompt;

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

SQL Query;

declare @RscID int;

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

Select ‘ Total of Missing Security And Critical Updates’ as ‘Security And Critical Updates Missing’

select

sys.Netbios_name0 as ‘PC Name’, catinfo2.CategoryInstanceName as ‘UpdateClassification’,

SUM(CASE css.Status WHEN 2 THEN 1 END) AS ‘Total Required’, css.Status as ‘Status’

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 v_R_System sys on sys.ResourceID=css.ResourceID and sys.ResourceID = @RscID

where  css.ResourceID = @RscID

and (catinfo.CategoryInstanceName like ‘Microsoft’)

and ((catinfo2.CategoryInstanceName like ‘Critical Updates’) or (catinfo2.CategoryInstanceName like ‘Security Updates’))

Group By css.Status,catinfo2.CategoryInstanceName, sys.Netbios_Name0

HAVING      (css.Status = 2)

Select ‘ Total of Security And Critical Updates’ as ‘Total of Security And Critical Updates’

select

sys.Netbios_name0 as ‘PC Name’, catinfo2.CategoryInstanceName as ‘UpdateClassification’,

SUM(CASE WHEN css.Status = 3 OR css.Status = 2 THEN 1 END) AS ‘Total Updates’

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 v_R_System sys on sys.ResourceID=css.ResourceID and sys.ResourceID = @RscID

where  css.ResourceID = @RscID

and (catinfo.CategoryInstanceName like ‘Microsoft’)

and ((catinfo2.CategoryInstanceName like ‘Critical Updates’) or (catinfo2.CategoryInstanceName like ‘Security Updates’))

Group By catinfo2.CategoryInstanceName,sys.Netbios_name0

Prompt, MachineName;

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

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

3- Link the Missing Software Updates Count report to the Missing Software Updates Details. and Voila it’s done.

Advertisements

3 thoughts on “SCCM – Usefull Software update reports

  1. Frank

    I tried it in SCCM 2012 and I get a count of all the critical updates available , not only the assigned.
    Is there a way to see only the assigned updates.

  2. Alexander

    Hello,
    i’m using your sql-commands for those two reports as they are listed here, the first report works fine as expected, and the second report opens, but when clicking on any detail-shortcut i get the following error:
    *******************
    An error occurred when the report was run. The details are as follows:
    Conversion failed when converting the varchar value ‘Total of Security And Critical Updates’ to data type tinyint.
    Error Number: -2147217913
    Source: Microsoft OLE DB Provider for SQL Server
    Native Error: 245
    *******************
    my sql-knowledge is very poor, maybe you know what is causing this error?

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