Based on the comments from the previous blog I have made a new query for the Software_Update_Details dataset which will show the status of updates for only the systems that are members of the selected collection.  The query also contains a change to the StartDate and EndDate variables that should work in SQL Server 2008 and the EndDate will select the last day of that month instead of the first day.

DECLARE @StartDate datetime, @EndDate datetime

Set @StartDate = CAST(@StartMonth as varchar) + '/1/' + CAST(@StartYear as varchar)

Set @EndDate = CAST(@EndMonth as varchar) + '/1/' + CAST(@EndYear as varchar)

Set @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0))

 

Select distinct ucs.ci_id, count(ucs.status)[Count],

Ui.BulletinID, ui.ArticleID, ui.Title,

CASE(ui.IsSuperseded)

When 0 Then 'No' Else 'Yes' End as 'Superseded',

Ui.DatePosted,

CASE (ucs.status)

When 3 Then 'Installed' When 2 Then 'Required' Else 'Not Applicable' End as 'Status',

Case (ui.IsDeployed)

When 0 Then 'No' Else 'Yes' End as 'Deployed',

Case(ui.IsExpired)

When 0 Then 'No' Else 'Yes' End as 'Expired',

cica.CategoryInstanceName,

CASE(ui.Severity)

When 2 Then 'Low' When 6 Then 'Moderate' When 8 Then 'Important' When 10 Then 'Critical' Else 'NA' End as 'Severity'

From v_Update_ComplianceStatus ucs

JOIN v_FullCollectionMembership fcm on ucs.ResourceID = fcm.ResourceID

JOIN v_UpdateInfo ui on ui.CI_ID = ucs.CI_ID

JOIN v_CICategoryInfo_All cica JOIN v_CategoryInfo ci on cica.CategoryInstanceID = ci.CategoryInstanceID AND cica.CategoryTypeName = 'UpdateClassification' on ucs.CI_ID = cica.CI_ID

Where fcm.CollectionID = @ColID AND ui.DatePosted BETWEEN @StartDate AND @EndDate

Group by ucs.CI_ID, ucs.status, ui.BulletinID, ui.ArticleID, ui.Title, ui.DatePosted, ui.IsDeployed, ui.IsSuperseded, ui.IsExpired, cica.CategoryInstanceName, ui.Severity

Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 1
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 2
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 3
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 4
Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 5