Posted on by mteegarden

Software Update Reports

Do you ever use the Software Updates reports that are built into SCCM?  Have you ever wanted a report that details information about patch compliance on a particular collection of machines? 

List Of All Updates

This report will list the following information:

    • Bulletin ID – example MS12-006
    • Article ID – example 2638806
    • Date Posted
    • Date Revised
    • Title
    • Infromation URL
    • Expired – yes or no
    • Superseded – yes or no
    • IsDeployed – yes or no

The report will have five prompts

    • Enter a month – example 10 for October or % for ALL
    • Enter a year – example 2012 or % for ALL
    • Enter an ArticleID – example 2638806 or % for ALL
    • Enter an Update Title – Example %Windows 7% for all Windows 7 related patches
    • Enter a BulletinID – Example MS12-006 or % for ALL

This report does not link to any other reports

Here is the report.  You will need to do the following:

  • Copy the contents of the report into Notepad
  • Chose File > Save As > Save as type “All Files”
  • Chose Unicode for the encoding type
  • Save the file with a .MOF extension
// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Tuesday, January 17, 2012 created
//
//		File Name: List of all updates.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report lists the updates that were deployed for a given month and year.";
	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "* List of all updates *";
	NumPrompts = 5;
	RefreshInterval = 0;
	ReportGUID = "{11EA627B-6499-4C60-9D4F-C980E30B630F}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter a month";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n   select distinct DATEPART(month,ui.DatePosted) as Month
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   order by DATEPART(month,ui.DatePosted)
\n else
\n   select distinct DATEPART(month,ui.DatePosted) as Month from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   where DATEPART(month,ui.DatePosted) like @__filterwildcard
\n   order by DATEPART(month,ui.DatePosted)
\nend";
	VariableName = "Month";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter a year";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct DATEPART(year,ui.DatePosted) as Year from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   order by DATEPART(year,ui.DatePosted)
\n else
\n  select distinct DATEPART(year,ui.DatePosted) as Year from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   where DATEPART(year,ui.DatePosted) like @__filterwildcard
\n  order by DATEPART(year,ui.DatePosted)
\nend";
	VariableName = "Year";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter an ArticleID or % for all.  Example:  987654";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n   select distinct ui.articleID
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   order by ui.articleID
\n else
\n   select distinct ui.articleID
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   where ui.articleID like @__filterwildcard
\n   order by ui.articleID
\nend";
	VariableName = "articleID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter an update title or % for all.  Example:  %microsoft%";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n   select distinct ui.title
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   order by ui.title
\n else
\n   select distinct ui.title
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   where ui.title like @__filterwildcard
\n   order by ui.title
\nend";
	VariableName = "title";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter a BulletinID or % for all.  Example:  MSxx-xxx";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n   select distinct ui.BulletinID
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   order by ui.BulletinID
\n else
\n   select distinct ui.BulletinID
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   where ui.BulletinID like @__filterwildcard
\n   order by ui.BulletinID
\nend";
	VariableName = "BulletinID";
}};
	SecurityKey = "";
	SQLQuery = "select ui.BulletinID, ui.ArticleID, ui.DatePosted, ui.DateRevised, ui.title, ui.infoURL,
\n\tExpired = case when ui.Isexpired = 0 then 'NO' else 'YES' end,
\n\tSuperseded = case when ui.isSuperseded = 0 then 'NO' else 'YES' end,
\n\tIsDeployed = case when ui.IsDeployed = 0 then 'NO' else 'YES' end
\nfrom v_UpdateInfo ui
\nwhere  (datepart(month, ui.DatePosted) like @Month and datepart(year,ui.DatePosted) like @Year
\nor
\ndatepart(month, ui.DateRevised) like @Month and datepart(year,ui.DateRevised) like @Year )
\nand ui.articleID like @articleID
\nand ui.title like @title
\nand ui.BulletinID like @BulletinID
\norder by ui.bulletinID desc, ui.articleID desc";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

 

Overall Software Update Status

This report will display the patch status for a particular collection of machines.

This report will list the following information

  • CollectionID
  • Business Unit – the name of the collection
  • Venor
  • Article ID – example 2638806
  • Bulletin ID – example MS12-006
  • Date Posted
  • Date Revised
  • Title
  • Expired – yes or no
  • Superseded – yes or no
  • IsDeployed – yes or no
  • Approved – yes or no
  • Installed – quantity of the machines in the collection that have installed the patch
  • Required – quantity of the machines in the collection that require the patch
  • Not Required – quantity of the machines in the collection that do not require the patch
  • Unknown – quantity of the machines in the collection that have a software update status of unknown for the patch
  • Total – total number of machines in the collection
  • %Compliant
  • %Not Compliant
  • %Unknown
  • Update ID – this is used for links to other reports.

The report will have two prompts

  • Select a Business Unit by clicking on the values button or enter % for ALL
  • Enter YES for patches that are being pushed, NO for ALL

This report will link to the “Specific Software Updates States” report

  • UpdateID – Column 21
  • CollectionID – Column 1

Here is the report. You will need to do the following:

  • Copy the contents of the report into Notepad
  • Chose File > Save As > Save as type “All Files”
  • Chose Unicode for the encoding type
  • Save the file with a .MOF extension
// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Overall Software Update Status.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report displays all updates for a particular buisness unit and  the percentage of compliant machines.";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Overall Software Update Status";
	NumPrompts = 2;
	RefreshInterval = 0;
	ReportGUID = "{F18FAD6D-0BDD-4697-82C8-D96F6FF39922}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Select a Buisness Unit by clicking the values button";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  where b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]' and a.name not like '%No Client%'
\n  order by a.Name
\n else
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  WHERE a.CollectionID like @__filterwildcard
\n  order by a.Name
\nend";
	VariableName = "CollectionID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter YES for patches that are being pushed.  NO for all";
	SampleValueSQL = "create table #temp (Choice char(3))
\ninsert into #temp (choice)
\nselect 'yes'
\ninsert into #temp (choice)
\nselect 'no'
\n
\nselect choice from #temp
\n";
	VariableName = "IsPushed";
}};
	SecurityKey = "";
	SQLQuery = "if @ISpushed = 'yes'
\nselect distinct us.CollectionID,
\nus.CollectionName as 'Business Unit',
\ncatinfo.CategoryInstanceName as Vendor0,
\n\tui.ArticleID as ArticleID,
\n\tui.BulletinID as BulletinID,
\nui.DatePosted,
\n\tui.DateRevised,
\n\tui.Title as Title,
\n\tExpired = case when ui.Isexpired = 0 then 'NO' else 'YES' end,
\n\tSuperseded = case when ui.isSuperseded = 0 then 'NO' else 'YES' end,
\n\tIsDeployed = case when ui.IsDeployed = 0 then 'NO' else 'YES' end,
\n\tApproved = case when col.CollectionID is not null then 'YES' else 'NO' end,\t
\n\tus.NumPresent as Present,
\n\tus.NumMissing as Missing,
\n\tus.NumNotApplicable as NotApplicable, 
\n\tus.NumUnknown as Unknown,
\n\tus.NumTotal as Total,
\n\tPCompliant=case when NumTotal<>0 then CAST((NumPresent+NumNotApplicable)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
\n\tPNotCompliant=case when NumTotal<>0 then CAST((NumMissing)*100.0/NumTotal as numeric(5,2)) else 0.0 end, 
\n\tPUnknown=case when NumTotal<>0 then CAST((NumUnknown)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
\nUniqueUpdateID=ui.CI_UniqueID
\nfrom v_CIRelation cir 
\njoin  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\njoin (v_CICategories_All catall join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company') 
\n\ton catall.CI_ID=ui.CI_ID
\n--left join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID='[enter the collectionID of the collection that you target software updates to]'
\n--join v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID='[enter the collectionID of the collection that you target software updates to]'
\nleft join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID in (
\n\tselect a.collectionID
\n\tfrom v_collection a
\n\tjoin v_CollectToSubCollect b
\n\ton a.collectionID = b.subcollectionid
\n\twhere b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]' )
\njoin v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID = @CollectionID
\n--where cir.FromCIID=@AuthListLocalID and cir.RelationType=1
\nwhere cir.FromCIID in 
\n(
\nselect CI_ID
\nfrom v_AuthListInfo where Title like '%[Enter the portion of the title of the update lists you want displayed. Example:  if you had update lists titled "Servers - 2010", "Servers 2011", etc you could just enter %Servers%]%'
\n)
\nand cir.RelationType=1
\nand IsDeployed = 1
\norder by bulletinID desc, articleID desc
\nelse
\nselect distinct us.CollectionID,
\nus.CollectionName as 'Business Unit',
\ncatinfo.CategoryInstanceName as Vendor0,
\n\tui.ArticleID as ArticleID,
\n\tui.BulletinID as BulletinID,
\nui.DatePosted,
\n\tui.DateRevised,
\n\tui.Title as Title,
\n\tExpired = case when ui.Isexpired = 0 then 'NO' else 'YES' end,
\n\tSuperseded = case when ui.isSuperseded = 0 then 'NO' else 'YES' end,
\n\tIsDeployed = case when ui.IsDeployed = 0 then 'NO' else 'YES' end,
\n\tApproved = case when col.CollectionID is not null then 'YES' else 'NO' end,\t
\n\tus.NumPresent as Present,
\n\tus.NumMissing as Missing,
\n\tus.NumNotApplicable as NotApplicable, 
\n\tus.NumUnknown as Unknown,
\n\tus.NumTotal as Total,
\n\tPCompliant=case when NumTotal<>0 then CAST((NumPresent+NumNotApplicable)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
\n\tPNotCompliant=case when NumTotal<>0 then CAST((NumMissing)*100.0/NumTotal as numeric(5,2)) else 0.0 end, 
\n\tPUnknown=case when NumTotal<>0 then CAST((NumUnknown)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
\nUniqueUpdateID=ui.CI_UniqueID
\nfrom v_CIRelation cir 
\njoin  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\njoin (v_CICategories_All catall join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company') 
\n\ton catall.CI_ID=ui.CI_ID
\n--left join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID='[enter the collectionID of the collection that you target software updates to]'
\n--join v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID='[enter the collectionID of the collection that you target software updates to]'
\nleft join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID in (
\n\tselect a.collectionID
\n\tfrom v_collection a
\n\tjoin v_CollectToSubCollect b
\n\ton a.collectionID = b.subcollectionid
\n\twhere b.parentcollectionID = '[enter the collectionID of the collection that you target software updates to]' )
\njoin v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID = @CollectionID
\n--where cir.FromCIID=@AuthListLocalID and cir.RelationType=1
\nwhere 1=1
\norder by bulletinID desc, articleID desc";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

 

Specific Software Updates States

This report will show the various states of a particular update for a certain collection.

This report will list the following information:

  • CollectionID
  • Business Unit – the name of the collection
  • State Name – Required, installed, unknown or not required
  • Count – The count of update states for the collection
  • %Total – percent of update states for the collection
  • UpdateID – this is used for linking to other reports

The report will have the following prompts

  • UpdateID – ie: MS12-006
  • CollectionID – The collection ID of a particular business unit

The report will link to the “Computers in a specific compliance state for an update” report

  • UpdateID – column 6
  • Status – column 3
  • CollectionID – column 1

Here is the report. You will need to do the following:

  • Copy the contents of the report into Notepad
  • Chose File > Save As > Save as type “All Files”
  • Chose Unicode for the encoding type
  • Save the file with a .MOF extension
// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Specific software updates states.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report returns the count and percentage of computers in each compliance state for the specified software update.";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Specific software updates states";
	NumPrompts = 2;
	RefreshInterval = 0;
	ReportGUID = "{7197C921-F2D4-4D59-A838-56B9868EF503}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Update ID (Required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct CI_UniqueID, ArticleID, BulletinID, Title from v_UpdateInfo
\n\twhere title not like '%server%'
\n\torder by BulletinID desc, ArticleID desc
\n else
\n  select distinct CI_UniqueID, ArticleID, BulletinID, Title from v_UpdateInfo
\n  WHERE (CI_UniqueID like @__filterwildcard
\n OR Title like @__filterwildcard
\n OR BulletinID like @__filterwildcard
\n OR ArticleID like @__filterwildcard)
\nand title not like '%server%'
\n  order by BulletinID desc, ArticleID desc
\nend";
	VariableName = "UpdateID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter a collection ID";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  where b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]' and a.name not like '%No Client%'
\n  order by a.Name
\n else
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  WHERE a.CollectionID like @__filterwildcard
\n  order by a.Name
\nend";
	VariableName = "CollectionID";
}};
	SecurityKey = "";
	SQLQuery = "declare @Unknown as varchar (256)
\ndeclare @NotRequired as varchar (256)
\ndeclare @Missing as varchar (256)
\ndeclare @Installed as varchar (256)
\ndeclare @Total as int
\n
\nselect @Unknown = StateName from v_StateNames where TopicType=500 and StateID=0
\nselect @NotRequired = StateName from v_StateNames where TopicType=500 and StateID=1
\nselect @Missing = StateName from v_StateNames where TopicType=500 and StateID=2
\nselect @Installed = StateName from v_StateNames where TopicType=500 and StateID=3
\n
\nselect @Total = NumTotal 
\n\tfrom v_UpdateSummaryPerCollection us
\n\tjoin v_Collection col on col.CollectionID=us.CollectionID
\n\tjoin v_UpdateInfo ui on us.CI_ID=ui.CI_ID
\nwhere col.CollectionID = @CollectionID
\nand (@UpdateID='' or ui.CI_UniqueID = @UpdateID)
\n
\nselect\tui.ArticleID as ArticleID, 
\n\tui.BulletinID as BulletinID,
\n\tui.Title as Title
\nfrom v_UpdateInfo ui where ui.CI_UniqueID = @UpdateID
\n
\nselect @Total as NumberInCollection
\n
\nselect 
\n         col.CollectionID as CollectionID,
\n\t\t col.Name as 'Business Unit',
\n         Status=case a.SetVariable 
\n\t\t\twhen 1 then @Unknown 
\n\t        when 2 then @NotRequired 
\n\t        when 3 then @Missing 
\n\t        else @Installed
\n\t        end,
\n         NumberOfComputers=case a.SetVariable 
\n\t\t\twhen 1 then NumUnknown 
\n\t  \t\twhen 2 then NumNotApplicable 
\n\t\t\twhen 3 then NumMissing
\n\t\t\telse NumPresent
\n \t\t    end,
\n         PComputers=case a.SetVariable 
\n\t\t\twhen 1 then convert(numeric(5,2), (isnull(NumUnknown, 0)*100.0) / isnull(nullif(@Total, 0), 1))
\n\t  \t\twhen 2 then convert(numeric(5,2), (isnull(NumNotApplicable, 0)*100.0) / isnull(nullif(@Total, 0), 1))
\n\t\t\twhen 3 then convert(numeric(5,2), (isnull(NumMissing, 0)*100.0) / isnull(nullif(@Total, 0), 1))
\n\t\t\telse convert(numeric(5,2), (isnull(NumPresent, 0)*100.0) / isnull(nullif(@Total, 0), 1))
\n \t\t    end,\t\t  
\n          UniqueUpdateID= ui.CI_UniqueID
\nfrom (select SetVariable=3 union all 
\n          select SetVariable=4 union all 
\n          select SetVariable=1 union all
\n          select SetVariable=2 )as a 
\ncross join v_UpdateSummaryPerCollection us 
\njoin v_Collection col on col.CollectionID=us.CollectionID
\njoin v_UpdateInfo ui on us.CI_ID=ui.CI_ID
\nwhere col.CollectionID = @CollectionID
\nand (@UpdateID='' or ui.CI_UniqueID = @UpdateID)";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

 

Computers in a specific compliance state for an update

This report lists the specific software update status for each machine in a collection

This report will list the following information:

  • Computer Name
  • AD Site
  • Business Unit – The name of the collection
  • Article ID – example 2585542
  • Bulletin ID – MS12-006
  • Title
  • Update Status
  • SCCM Last Hardwar Inventory – date
  • Last WSUS Scan – date
  • WSUSscan_State
  • Error Status ID – If the WSUSscan_State is not “scan completed” then this column will display the ErrorID for troubleshooting
  • Error Code – If the WSUSscan_State is not “scan completed” then this column will display the ErrorCode for troubleshooting
  • HexErrorCode – If the WSUSscan_State is not “scan completed” then this column will display the HexCode for troubleshooting
  • Last Logged On User
  • Assigned Site – SCCM Assigned Site
  • Client Version – SCCM Client Version

The report will have three prompts

  • UpdateID – example MS12-006
  • State Name – Example “Update is installed” or “Update is Required”
  • CollectionID – The collection ID of a particular business unit

The report will link to the “Compliance for a specific computer” report

Here is the report. You will need to do the following:

  • Copy the contents of the report into Notepad
  • Chose File > Save As > Save as type “All Files”
  • Chose Unicode for the encoding type
  • Save the file with a .MOF extension
// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Computers in a specific compliance state for an update.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report returns all computers in a collection that have a specific compliance state for a software update.";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Computers in a specific compliance state for an update";
	NumPrompts = 3;
	RefreshInterval = 0;
	ReportGUID = "{C5616815-4C02-430A-B132-F69580667844}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Update ID (Required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct CI_UniqueID as UniqueUpdateID, ArticleID, BulletinID, Title from v_UpdateInfo
\n  where title not like '%server%'
\n  order by BulletinID desc, ArticleID desc
\n else
\n  select distinct CI_UniqueID as UniqueUpdateID, ArticleID, BulletinID, Title from v_UpdateInfo
\n  WHERE (CI_UniqueID like @__filterwildcard
\n OR Title like @__filterwildcard
\n OR BulletinID like @__filterwildcard
\n OR ArticleID like @__filterwildcard)
\nand title not like '%server%'
\n  order by BulletinID desc, ArticleID desc
\nend";
	VariableName = "UpdateID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "State Name (Required)";
	SampleValueSQL = "begin
\nif (@__filterwildcard = '')
\nselect StateName as State, StateID as StateID
\n\tfrom v_StateNames where TopicType=500
\nelse 
\nselect StateName as State, StateID as StateID
\n\tfrom v_StateNames where TopicType=500
\n\tand ((StateName like @__filterwildcard)
\n\tor (StateID like @__filterwildcard))
\nend";
	VariableName = "Status";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "CollectionID (required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  where (b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]') and a.name not like '%No Client%'
\n  order by a.Name
\n else
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  WHERE a.CollectionID like @__filterwildcard
\n  order by a.Name
\nend";
	VariableName = "CollectionID";
}};
	SecurityKey = "";
	SQLQuery = "declare @Unknown as varchar (256)
\ndeclare @NotRequired as varchar (256)
\ndeclare @Missing as varchar (256)
\ndeclare @Installed as varchar (256)
\n
\nselect @Unknown = StateName from v_StateNames where TopicType=500 and StateID=0
\nselect @NotRequired = StateName from v_StateNames where TopicType=500 and StateID=1
\nselect @Missing = StateName from v_StateNames where TopicType=500 and StateID=2
\nselect @Installed = StateName from v_StateNames where TopicType=500 and StateID=3
\n
\ndeclare @CIID int; select @CIID=CI_ID from v_UpdateInfo where CI_UniqueID=@UpdateID
\nselect 
\nm.Name0 as ComputerName0,
\nm.AD_Site_Name0 as 'AD Site',
\ncol.Name as 'Business Unit',
\nui.ArticleID as ArticleID, 
\nui.BulletinID as BulletinID,
\nui.Title as Title,
\n@Status as 'Update Status',
\n            ws.LastHWScan as 'SCCM Last Hardware Inventory',
\n            Dateadd(hour,(datediff(hour,getutcdate(),getdate())),uss.lastscantime) as 'Last WSUS Scan'
\n            , scan.StateName 'WSUSscan_State'
\n\t\t\t, scan.ErrorStatusID
\n\t\t\t, scan.ErrorCode
\n\t\t\t, scan.HexErrorCode,
\nm.User_Name0 as LastLoggedOnUser,
\nasite.SMS_Assigned_Sites0 as AssignedSite, 
\nm.Client_Version0 as ClientVersion,
\nDATEADD(ss,@__timezoneoffset,LastStatusCheckTime) as LastStateReceived, 
\nDATEADD(ss,@__timezoneoffset,LastStatusChangeTime) as LastStateChange,
\n''
\nfrom v_Update_ComplianceStatusAll ucs 
\njoin v_UpdateInfo ui on ucs.CI_ID=ui.CI_ID
\njoin v_ClientCollectionMembers cm on cm.ResourceID=ucs.ResourceID
\njoin v_collection col on col.collectionID = cm.collectionID
\njoin v_R_System m on m.ResourceType=5 and m.ResourceID=ucs.ResourceID and isnull(m.Obsolete0,0)<>1
\nleft join v_RA_System_SMSAssignedSites asite on m.ResourceID=asite.ResourceID
\nleft join \tv_GS_WORKSTATION_STATUS ws on cm.ResourceID = ws.ResourceID
\nleft join \tv_UpdateScanStatus uss on cm.ResourceID = uss.ResourceID
\nleft join
\n(
\nselect uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
\n\tisnull(uss.LastErrorCode,0) as ErrorCode,
\n\tdbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
\n from v_UpdateScanStatus uss
\n\tjoin v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
\n\tjoin v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID 
\n\tjoin v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
\n\tjoin v_StateNames sn on sn.TopicType = 501 and 
\n\t\tsn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end)
\nwhere 1= 1
\n\t--and (sass.SMS_Assigned_Sites0= 'P01')
\n\t--and (sn.StateName='Scan failed')
\n\tand (sus.UpdateSource_UniqueID = '{BE893E98-BD57-4E80-93F6-5D38B61C6069}')
\n) scan
\non cm.ResourceID = scan.ResourceID 
\nwhere ucs.CI_ID=@CIID
\nand cm.CollectionID  = @CollectionID
\nand ((@Status='')
\n\tor (@Status = @Unknown and ucs.Status=0) 
\n\tor (@Status = @NotRequired and ucs.Status=1)
\n\tor (@Status = @Missing and ucs.Status=2)
\n\tor (@Status = @Installed and ucs.Status=3))
\norder by m.Name0";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

Compliance for a specific computer

This report shows the patch status for a specific computer

This report will list the following information:

  • MachineName
  • Vendor
  • Update Class – example:  security update, service pack, etc
  • BulletinID – example MS12-006
  • ArticleID – example 2526086
  • Title
  • Date Posted
  • Date Revised
  • Approved – this means that the patch is in an Update List
  • Installed – an asterisk indicates that the patch is installed
  • Is Required – an asterisk indicates that the patch is required
  • PatchInstallDate
  • Last State Change – date
  • Deadline – This is the deadline you set in the Deployment Management
  • SCCM Last Hardware Inventory – Date
  • Last WSUS Scan – date
  • WSUSscan_State
  • Error Status ID – If the WSUSscan_State is not “scan completed” then this column will display the ErrorID for troubleshooting
  • Error Code – If the WSUSscan_State is not “scan completed” then this column will display the ErrorCode for troubleshooting
  • HexErrorCode – If the WSUSscan_State is not “scan completed” then this column will display the HexCode for troubleshooting
  • UpdateID
  • Information URL

This report has one prompt

  • Enter a MachineName

This report does not have any links to other reports

Here is the report. You will need to do the following:

  • Copy the contents of the report into Notepad
  • Chose File > Save As > Save as type “All Files”
  • Chose Unicode for the encoding type
  • Save the file with a .MOF extension
// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Compliance for a specific computer.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report returns the software update compliance data for a specific computer.";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Compliance for a specific computer";
	NumPrompts = 1;
	RefreshInterval = 0;
	ReportGUID = "{DE76B7D3-29B5-493F-AE12-1DDC591163E2}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Computer Name (Required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct Name0 from v_R_System WHERE isnull(Obsolete0,0)<>1 order by Name0
\n else
\n  select distinct Name0 from v_R_System
\n  WHERE Name0 like @__filterwildcard
\n and isnull(Obsolete0,0)<>1 order by Name0
\nend";
	VariableName = "MachineName";
}};
	SecurityKey = "";
	SQLQuery = "SET NOCOUNT ON
\n
\nCreate table #ResourceID (ResourceID int, name0 varchar(64))
\nInsert into #ResourceID (ResourceID, name0)
\nselect resourceID, name0 from v_R_System where ((Name0 like @MachineName) and (Active0 = 1) and Operating_System_Name_and0 not like '%server%')
\n--select * from #ResourceID
\n--drop table #ResourceID
\n
\n--select 'Total number of machines', count(ResourceID) from #ResourceID
\n
\nselect 
\n\t\t\t#ResourceID.name0 as 'Machine Name',
\n\t\t\tcatinfo.CategoryInstanceName as Vendor,
\n\t\t\tcatinfo2.CategoryInstanceName as UpdateClassification,
\n            ui.BulletinID as BulletinID,
\n            ui.ArticleID as ArticleID,
\n            ui.Title as Title,   
\n            ui.dateposted,
\n            ui.daterevised,             
\n            Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
\n            Installed=(case when css.Status=3 then '*' else '' end),
\n            IsRequired=(case when css.Status=2 then '*' else '' end),
\n\t\t\tcss.LastStatusCheckTime as 'PatchInstallDate',
\n\t\t\tcss.LastStatusChangeTime as 'LastStateChange',
\n            Deadline=cdl.Deadline,
\n            ws.LastHWScan as 'SCCM Last Hardware Inventory',
\n            Dateadd(hour,(datediff(hour,getutcdate(),getdate())),uss.lastscantime) as 'Last WSUS Scan'
\n            , scan.StateName 'WSUSscan_State'
\n\t\t\t, scan.ErrorStatusID
\n\t\t\t, scan.ErrorCode
\n\t\t\t, scan.HexErrorCode,
\n            ui.CI_UniqueID as UniqueUpdateID,
\n\t\t\tui.InfoURL as InformationURL
\nfrom v_Update_ComplianceStatusall css
\njoin #ResourceID on css.ResourceID = #ResourceID.ResourceID
\njoin v_UpdateInfo ui on ui.CI_ID=css.CI_ID
\njoin v_CIRelation cir on cir.ToCIID = ui.CI_ID
\njoin v_CICategories_All catall on catall.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company' 
\njoin v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' 
\n--left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
\nleft join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = #ResourceID.ResourceID
\nleft join (
\n                        select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
\n                        join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
\n                        group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID
\n--where  css.ResourceID = @RscID 
\nleft join \tv_GS_WORKSTATION_STATUS ws on css.ResourceID = ws.ResourceID
\nleft join \tv_UpdateScanStatus uss on css.ResourceID = uss.ResourceID
\nleft join
\n(
\nselect uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
\n\tisnull(uss.LastErrorCode,0) as ErrorCode,
\n\tdbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
\n from v_UpdateScanStatus uss
\n\tjoin v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
\n\tjoin v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID 
\n\tjoin v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
\n\tjoin v_StateNames sn on sn.TopicType = 501 and 
\n\t\tsn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end)
\nwhere 1= 1
\n\t--and (sass.SMS_Assigned_Sites0= 'P01')
\n\t--and (sn.StateName='Scan failed')
\n\tand (sus.UpdateSource_UniqueID = '{BE893E98-BD57-4E80-93F6-5D38B61C6069}')
\n) scan
\non css.ResourceID = scan.ResourceID 
\nwhere  css.ResourceID = #ResourceID.ResourceID
\nand ((css.Status=2) or (css.Status=3))
\n--and (@Vendor = '' or catinfo.CategoryInstanceName = @Vendor)
\n--and (@UpdateClass = '' or catinfo2.CategoryInstanceName = @UpdateClass)
\nand cir.FromCIID in 
\n(
\nselect CI_ID
\nfrom v_AuthListInfo where Title like '[Enter the portion of the title of the update lists you want displayed. Example:  if you had update lists titled "Servers - 2010", "Servers 2011", etc you could just enter %Servers%]'
\n)
\nand cir.RelationType=1
\n--order by  ui.BulletinID desc
\norder by #ResourceID.name0, ui.BulletinID desc";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
};
// ***** End *****

 

Specific software update status

This report list the compliance percentage of a particular patch

This report will list the following information:

  • Vendor
  • ArticleID – example:  2585542
  • BuletinID – example: MS12-006
  • Title
  • Installed – quantity of machines with the patch installed
  • Required – quantity of machines that require the patch
  • Not Required – quantity of machines that do no require the patch
  • Unknown – quantity of machines that have an status of “unknown” for the patch
  • Total – total quantity of the machines
  • %Compliant
  • %Not Compliant
  • %Unknown
  • CollectionID
  • Update ID

This report will have one prompt

  • Update Title, Bulletin ID or Article ID

This report will link to the Specific Software Updates States report

Here is the report. You will need to do the following:

  • Copy the contents of the report into Notepad
  • Chose File > Save As > Save as type “All Files”
  • Chose Unicode for the encoding type
  • Save the file with a .MOF extension
// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Specific software update status.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report returns the patch percentage of machines for a particular update.";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Specific software update status";
	NumPrompts = 1;
	RefreshInterval = 0;
	ReportGUID = "{0D228B49-56B8-4DA9-A0E2-27D64642B0E3}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Update Title, Bulletin ID or Article ID (Required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct ArticleID, BulletinID, Title from v_UpdateInfo
\n  where title not like '%server%'
\n  order by BulletinID desc, ArticleID desc
\n else
\n  select distinct ArticleID, BulletinID, Title from v_UpdateInfo
\n  WHERE ((ArticleID like @__filterwildcard) or 
\n         (BulletinID like @__filterwildcard) or 
\n         (Title like @__filterwildcard))
\n\tand title not like '%server%'
\n  order by BulletinID desc, ArticleID desc
\nend";
	VariableName = "Update";
}};
	SecurityKey = "";
	SQLQuery = "set nocount on
\ndeclare @UpdInfo table (CI_ID int primary key, BulletinID varchar(64), ArticleID varchar(64), Title varchar(512), CI_UniqueID varchar(512))
\ninsert into @UpdInfo (CI_ID, BulletinID, ArticleID, Title, CI_UniqueID) 
\n    select CI_ID, BulletinID, ArticleID, Title, CI_UniqueID
\n    from v_UpdateInfo ui
\n    where (ui.ArticleID=@Update) 
\n    or (ui.BulletinID=@Update) 
\n    or (ui.Title=@Update) 
\n    or (ui.CI_UniqueID=@Update)
\n    and ui.title not like '%server%'
\n
\nselect  catinfo.CategoryInstanceName as Vendor0,
\n        ui.ArticleID as ArticleID, 
\n        ui.BulletinID as BulletinID,
\n        ui.Title as Title,
\n        NumPresent as Present,
\n        NumMissing as Missing,
\n        NumNotApplicable as NotApplicable,
\n        NumUnknown as Unknown,
\n        NumTotal as Total,
\n        PCompliant=convert(numeric(5,2), (isnull(NumPresent, 0)+isnull(NumNotApplicable, 0))*100.0 / isnull(nullif(NumTotal, 0), 1)),
\n        PNotCompliant=convert(numeric(5,2), (isnull(NumMissing, 0))*100.0 / isnull(nullif(NumTotal, 0), 1)),
\n        PUnknown=convert(numeric(5,2), (isnull(NumTotal - (isnull(NumPresent, 0)+isnull(NumMissing, 0)+isnull(NumNotApplicable, 0)), 0))*100.0 / isnull(nullif(NumTotal, 0), 1)),
\n        CollectionID as CollectionID,
\n        ui.CI_UniqueID as UniqueUpdateID 
\nfrom @UpdInfo ui
\njoin v_UpdateSummaryPerCollection uspc on ui.CI_ID = uspc.CI_ID and uspc.CollectionID in ('[Enter the collection ID of the collection that you target Software Updates to]')
\njoin v_CICategoryInfo_All catinfo on ui.CI_ID = catinfo.CI_ID and catinfo.CategoryTypeName = 'Company'
\norder by ui.ArticleID";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

 

Machines that are missing a patch over 30 days old

This report lists all machines that require a patch that has been created or modified over 30 days ago

This report will list the following information

  • MachineName
  • Vendor
  • Update Class
  • ArticleID – example: 2585542
  • BuletinID – example: MS12-006
  • Title
  • Date Posted
  • Date Revised
  • Expired – Yes or no
  • Superseded – yes or no
  • IsDeployed – yes or no
  • Is Required – an asterisk indicates that the patch is required
  • Deadline – this is the deadline for the Deployment Management
  • SCCM Last Hardwar Inventory – date
  • Last WSUS Scan – date
  • WSUSscan_State
  • Error Status ID – If the WSUSscan_State is not “scan completed” then this column will display the ErrorID for troubleshooting
  • Error Code – If the WSUSscan_State is not “scan completed” then this column will display the ErrorCode for troubleshooting
  • HexErrorCode – If the WSUSscan_State is not “scan completed” then this column will display the HexCode for troubleshooting
  • Update ID
  • Information URL

This report will have two prompts

  • Select a Business Unit by clicking on the values button or enter % for ALL
  • Enter YES for patches that are being pushed, NO for ALL

This report will link to the “Compliance for a specific computer” report

  • Machinename – column 1

Here is the report. You will need to do the following:

  • Copy the contents of the report into Notepad
  • Chose File > Save As > Save as type “All Files”
  • Chose Unicode for the encoding type
  • Save the file with a .MOF extension
// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Machines that are missing a patch over 30 days old.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report lists all machines that require a patch that has been created or modified over 30 days ago.";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Machines that are missing a patch over 30 days old";
	NumPrompts = 2;
	RefreshInterval = 0;
	ReportGUID = "{7AE2D12B-C0DE-4728-BFA7-58CDE4ADFE84}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Select a business unit or % for all";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  where b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]' and a.name not like '%No Client%'
\n  order by a.Name
\n else
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  WHERE a.CollectionID like @__filterwildcard
\n  order by a.Name
\nend";
	VariableName = "CollectionID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter YES for pushed patches or NO for all patches";
	SampleValueSQL = "";
	VariableName = "IsPushed";
}};
	SecurityKey = "";
	SQLQuery = "if @ISpushed = 'yes'
\nselect distinct
\n\t\t\tRsys.name0 as 'Machine Name',
\n\t\t\tcatinfo.CategoryInstanceName as Vendor,
\n\t\t\tcatinfo2.CategoryInstanceName as UpdateClassification,
\n            ui.BulletinID as BulletinID,
\n            ui.ArticleID as ArticleID,
\n            ui.Title as Title,    
\n            ui.DatePosted,
\n            ui.DateRevised,        
\n            --Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
\n            --Installed=(case when css.Status=3 then '*' else '' end),
\n            Expired = case when ui.Isexpired = 0 then 'NO' else 'YES' end,
\n\t\t\tSuperseded = case when ui.isSuperseded = 0 then 'NO' else 'YES' end,
\n\t\t\tIsDeployed = case when ui.IsDeployed = 0 then 'NO' else 'YES' end,
\n            IsRequired=(case when css.Status=2 then '*' else '' end),
\n\t\t\tcss.LastStatusCheckTime as 'PatchInstallDate',
\n\t\t\tcss.LastStatusChangeTime as 'LastStateChange',
\n            Deadline=cdl.Deadline,
\n            ws.LastHWScan as 'SCCM Last Hardware Inventory',
\n            Dateadd(hour,(datediff(hour,getutcdate(),getdate())),uss.lastscantime) as 'Last WSUS Scan'
\n            , scan.StateName 'WSUSscan_State'
\n\t\t\t, scan.ErrorStatusID
\n\t\t\t, scan.ErrorCode
\n\t\t\t, scan.HexErrorCode,
\n--\t\t\tmaint.MaintenanceWindowName as 'CEPSWName', maint.Description, maint.StartTime, maint.DurationMinutes as 'DurationMinutes', maint.Enabled as 'CEPSWEnabled',
\n            ui.CI_UniqueID as UniqueUpdateID,
\n\t\t\tui.InfoURL as InformationURL
\nfrom v_Update_ComplianceStatusall css
\njoin v_R_System Rsys on css.ResourceID = Rsys.ResourceID
\njoin v_UpdateInfo ui on ui.CI_ID=css.CI_ID
\njoin v_CIRelation cir on cir.ToCIID = ui.CI_ID
\njoin v_CICategories_All catall on catall.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company' 
\njoin v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' 
\n--left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
\nleft join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = rsys.ResourceID
\nleft join (
\n                        select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
\n                        join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
\n                        group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID
\n--where  css.ResourceID = @RscID 
\n/*
\nleft join (
\n\t\t\tselect fcm.resourceid, sw.Name as 'MaintenanceWindowName', sw.Description, sw.StartTime, sw.Duration as 'DurationMinutes', sw.IsEnabled as 'Enabled',case when sw.RecurrenceType = 1 then 'No Recurrence'
\n\t\t\twhen sw.RecurrenceType = 2 then 'Daily'
\n\t\t\twhen sw.RecurrenceType = 3 then 'Weekly'
\n\t\t\twhen sw.RecurrenceType = 4 then 'Monthly'
\n\t\t\telse 'Unkwnon' end as RecurrenceType, case when sw.ServiceWindowType=5 then '*' else ' ' end as 'OSDServiceWindow'
\n\t\t\t, col.name as 'CollectionName'
\n\t\t\t, col.CollectionID
\n\t\t\tfrom v_ServiceWindow sw
\n\t\t\tjoin v_FullCollectionMembership fcm on sw.CollectionID = fcm.CollectionID
\n\t\t\tjoin v_Collection col
\n\t\t\ton sw.collectionid = col.collectionid
\n\t\t\twhere sw.Name <> 'NO RUN'
\n\t\t\t) Maint on maint.ResourceID = Rsys.ResourceID
\n*/
\nleft join \tv_GS_WORKSTATION_STATUS ws on Rsys.ResourceID = ws.ResourceID
\nleft join \tv_UpdateScanStatus uss on Rsys.ResourceID = uss.ResourceID
\nleft join
\n(
\nselect uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
\n\tisnull(uss.LastErrorCode,0) as ErrorCode,
\n\tdbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
\n from v_UpdateScanStatus uss
\n\tjoin v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
\n\tjoin v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID 
\n\tjoin v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
\n\tjoin v_StateNames sn on sn.TopicType = 501 and 
\n\t\tsn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end)
\nwhere 1= 1
\n\t--and (sass.SMS_Assigned_Sites0= 'P01')
\n\t--and (sn.StateName='Scan failed')
\n\tand (sus.UpdateSource_UniqueID = '{BE893E98-BD57-4E80-93F6-5D38B61C6069}')
\n) scan
\non Rsys.ResourceID = scan.ResourceID 
\nwhere  css.ResourceID = rsys.ResourceID
\nand ((css.Status=2))
\nand Rsys.Operating_System_Name_and0 not like '%server%'
\n--and (@Vendor = '' or catinfo.CategoryInstanceName = @Vendor)
\n--and (@UpdateClass = '' or catinfo2.CategoryInstanceName = @UpdateClass)
\nand cir.FromCIID in 
\n(
\nselect CI_ID
\nfrom v_AuthListInfo where Title like '%[Enter the portion of the title of the update lists you want displayed. Example:  if you had update lists titled "Servers - 2010", "Servers 2011", etc you could just enter %Servers%]%' and (Title not like '%test%' or title not like '%sandbox%')
\n)
\nand cir.RelationType=1
\nand (ui.DatePosted <= (GETDATE()-30) or ui.DateRevised <= (GETDATE()-30))
\n--and rsys.Name0 like 'SWMN00XB03231'
\n--order by  ui.BulletinID desc
\nand IsDeployed = 1
\nand rsys.Name0 in
\n\t(
\n\tselect Name
\n\tfrom dbo.v_FullCollectionMembership
\n\twhere CollectionID = @CollectionID
\n\t)
\norder by rsys.name0, ui.BulletinID desc
\nelse
\nselect distinct
\n\t\t\tRsys.name0 as 'Machine Name',
\n\t\t\tcatinfo.CategoryInstanceName as Vendor,
\n\t\t\tcatinfo2.CategoryInstanceName as UpdateClassification,
\n            ui.BulletinID as BulletinID,
\n            ui.ArticleID as ArticleID,
\n            ui.Title as Title,    
\n            ui.DatePosted,
\n            ui.DateRevised,        
\n            --Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
\n            --Installed=(case when css.Status=3 then '*' else '' end),
\n            Expired = case when ui.Isexpired = 0 then 'NO' else 'YES' end,
\n\t\t\tSuperseded = case when ui.isSuperseded = 0 then 'NO' else 'YES' end,
\n\t\t\tIsDeployed = case when ui.IsDeployed = 0 then 'NO' else 'YES' end,
\n            IsRequired=(case when css.Status=2 then '*' else '' end),
\n\t\t\tcss.LastStatusCheckTime as 'PatchInstallDate',
\n\t\t\tcss.LastStatusChangeTime as 'LastStateChange',
\n            Deadline=cdl.Deadline,
\n            ws.LastHWScan as 'SCCM Last Hardware Inventory',
\n            Dateadd(hour,(datediff(hour,getutcdate(),getdate())),uss.lastscantime) as 'Last WSUS Scan'
\n            , scan.StateName 'WSUSscan_State'
\n\t\t\t, scan.ErrorStatusID
\n\t\t\t, scan.ErrorCode
\n\t\t\t, scan.HexErrorCode,
\n--\t\t\tmaint.MaintenanceWindowName as 'CEPSWName', maint.Description, maint.StartTime, maint.DurationMinutes as 'DurationMinutes', maint.Enabled as 'CEPSWEnabled',
\n            ui.CI_UniqueID as UniqueUpdateID,
\n\t\t\tui.InfoURL as InformationURL
\nfrom v_Update_ComplianceStatusall css
\njoin v_R_System Rsys on css.ResourceID = Rsys.ResourceID
\njoin v_UpdateInfo ui on ui.CI_ID=css.CI_ID
\njoin v_CIRelation cir on cir.ToCIID = ui.CI_ID
\njoin v_CICategories_All catall on catall.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company' 
\njoin v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' 
\n--left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
\nleft join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = rsys.ResourceID
\nleft join (
\n                        select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
\n                        join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
\n                        group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID
\n--where  css.ResourceID = @RscID 
\n/*
\nleft join (
\n\t\t\tselect fcm.resourceid, sw.Name as 'MaintenanceWindowName', sw.Description, sw.StartTime, sw.Duration as 'DurationMinutes', sw.IsEnabled as 'Enabled',case when sw.RecurrenceType = 1 then 'No Recurrence'
\n\t\t\twhen sw.RecurrenceType = 2 then 'Daily'
\n\t\t\twhen sw.RecurrenceType = 3 then 'Weekly'
\n\t\t\twhen sw.RecurrenceType = 4 then 'Monthly'
\n\t\t\telse 'Unkwnon' end as RecurrenceType, case when sw.ServiceWindowType=5 then '*' else ' ' end as 'OSDServiceWindow'
\n\t\t\t, col.name as 'CollectionName'
\n\t\t\t, col.CollectionID
\n\t\t\tfrom v_ServiceWindow sw
\n\t\t\tjoin v_FullCollectionMembership fcm on sw.CollectionID = fcm.CollectionID
\n\t\t\tjoin v_Collection col
\n\t\t\ton sw.collectionid = col.collectionid
\n\t\t\twhere sw.Name <> 'NO RUN'
\n\t\t\t) Maint on maint.ResourceID = Rsys.ResourceID
\n*/
\nleft join \tv_GS_WORKSTATION_STATUS ws on Rsys.ResourceID = ws.ResourceID
\nleft join \tv_UpdateScanStatus uss on Rsys.ResourceID = uss.ResourceID
\nleft join
\n(
\nselect uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
\n\tisnull(uss.LastErrorCode,0) as ErrorCode,
\n\tdbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
\n from v_UpdateScanStatus uss
\n\tjoin v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
\n\tjoin v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID 
\n\tjoin v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
\n\tjoin v_StateNames sn on sn.TopicType = 501 and 
\n\t\tsn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end)
\nwhere 1= 1
\n\t--and (sass.SMS_Assigned_Sites0= 'P01')
\n\t--and (sn.StateName='Scan failed')
\n\tand (sus.UpdateSource_UniqueID = '{BE893E98-BD57-4E80-93F6-5D38B61C6069}')
\n) scan
\non Rsys.ResourceID = scan.ResourceID 
\nwhere  css.ResourceID = rsys.ResourceID
\nand ((css.Status=2))
\nand Rsys.Operating_System_Name_and0 not like '%server%'
\n--and (@Vendor = '' or catinfo.CategoryInstanceName = @Vendor)
\n--and (@UpdateClass = '' or catinfo2.CategoryInstanceName = @UpdateClass)
\n--and cir.FromCIID in 
\n--(
\n--select CI_ID
\n--from v_AuthListInfo where Title like '%Wintel%' and (Title not like '%test%' or title not like '%sandbox%')
\n--)
\n--and cir.RelationType=1
\nand (ui.DatePosted <= (GETDATE()-30) or ui.DateRevised <= (GETDATE()-30))
\n--and rsys.Name0 like 'SWMN00XB03231'
\n--order by  ui.BulletinID desc
\nand rsys.Name0 in
\n\t(
\n\tselect Name
\n\tfrom dbo.v_FullCollectionMembership
\n\twhere CollectionID = @CollectionID
\n\t)
\norder by rsys.name0, ui.BulletinID desc";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

States for a deployment

This report returns the states for all software updates in the specified deployment for a specified collection

NOTE:  This is a very process intensive query.  DO NOT run this report against a large collection of machines.

This report will list the following information:

  • ArticleID – example:  2393802
  • Bulletin ID – example MS11-011
  • Title
  • State Name – example “Update is installed” or “Update is downloaded”
  • Error Status ID
  • Update ID
  • Name – MachineName
  • Last Bootup Time – Datetime
  • SCCM Last Hardware Inventory – Datetime

The report will have two prompts

  • Deployment ID – this is the ID of the Update List you want to interrogate
  • Enter a business unit or CollectionID – This is the collection of machines you are interested in

This report does not link to any other reports

Here is the report. You will need to do the following:

  • Copy the contents of the report into Notepad
  • Chose File > Save As > Save as type “All Files”
  • Chose Unicode for the encoding type
  • Save the file with a .MOF extension
// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: States for a deployment.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report returns the states for all software updates in the specified deployment for a specified collection";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "States for a deployment";
	NumPrompts = 2;
	RefreshInterval = 0;
	ReportGUID = "{CA6D9DC9-B7F4-4AA4-AE42-1B2892301B79}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Deployment ID (Required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '') 
\nselect 
\ncia.Assignment_UniqueID as DeploymentID , cia.AssignmentName as DeploymentName
\nfrom v_CIAssignment cia
\nwhere cia.UpdateAssignment=1 and AssignmentName like '%[Enter the portion of the title of the update lists you want displayed. Example:  if you had update lists titled "Servers - 2010", "Servers 2011", etc you could just enter %Servers%]%'
\norder by AssignmentName
\nelse
\nselect 
\ncia.Assignment_UniqueID as DeploymentID , cia.AssignmentName as DeploymentName
\nfrom v_CIAssignment cia
\nwhere cia.UpdateAssignment=1
\nand Assignment_UniqueID like @__filterwildcard or AssignmentName like @__filterwildcard
\norder by AssignmentName
\nend";
	VariableName = "DEPLOYMENTID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter a business unit or Collection ID";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  where (b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]' or b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]') and a.name not like '%No Client%' 
\nor a.collectionID in (select collectionID from v_SCCM_WintelPatchWindowCollections)
\n  order by a.Name
\n else
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  WHERE a.CollectionID like @__filterwildcard
\n  order by a.Name
\nend";
	VariableName = "CollectionID";
}};
	SecurityKey = "";
	SQLQuery = "select 
\nui.ArticleID as ArticleID,
\nui.BulletinID as BulletinID,
\nui.Title as Title,
\nsn.StateName as Status,
\nucs.LastEnforcementStatusMsgID&0x0000FFFF as ErrorStatusID,
\nui.CI_UniqueID as UniqueUpdateID,
\ncol.name,
\nos.LastBootUpTime0 as 'LastBootUpTime',
\nws.LastHWScan as 'SCCM Last Hardware Inventory'
\nfrom v_CIAssignment a
\njoin v_CIAssignmentToCI aci
\n   on aci.AssignmentID=a.AssignmentID
\njoin v_CIAssignmentTargetedMachines atm
\n   on atm.AssignmentID=a.AssignmentID
\njoin v_UpdateState_Combined ucs
\n   on ucs.CI_ID=aci.CI_ID and ucs.ResourceID=atm.ResourceID
\njoin v_StateNames sn
\n   on sn.StateID=ucs.StateID and sn.TopicType=ucs.StateType
\njoin v_UpdateInfo ui
\n   on ui.CI_ID=aci.CI_ID
\nleft join   v_GS_Operating_System os on atm.ResourceID = os.resourceid
\nleft join \tv_GS_WORKSTATION_STATUS ws on atm.ResourceID = ws.ResourceID
\nleft join v_FullCollectionMembership Col
\non atm.ResourceID = col.ResourceID
\nwhere (a.Assignment_UniqueID = @DEPLOYMENTID)  and col. CollectionID = @CollectionID
\norder by ui.ArticleID";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****