Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 5
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