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
Comments
- Anonymous
January 01, 2003
The comment has been removed - Anonymous
January 01, 2003
@AnnDC, stay tuned. I'll have a new blog in the coming weeks on a software update dashboard that you may find very helpful. - Anonymous
January 01, 2003
Thank you for this. - Anonymous
January 01, 2003
Alex,
It's hard to say what's happening with your situation. Do you get results from the queries in the datasets? Have you tried building the report following the steps from part 1? - Anonymous
January 01, 2003
Hi,
Thanks for the information in this blog, I've successfully created this report.
In the software update table, would it be possible to have a column showing the computer name?
That would help a lot cause then I could first filter the collection and then I'm able to see all the updates required for a certain server.
//Simon - Anonymous
January 01, 2003
Simon,
Technically it is possible but with the current query it would have a negative impact to the charts without modifying them. You could have the an expandable column that would show the computer names from the existing or new dataset. This is a lot more involved than the intention of this blog series. The easiest method would be to create a sub report.
The Software Update title could be a link to a sub report that will display the list of computers that it applies to. If you look at my other blog series (http://blogs.technet.com/b/gary_simmons_mcs/archive/2014/09/16/system-center-2012-r2-configuration-manager-software-update-compliance-dashboard-part-1.aspx Software Update Compliance Dashboard) you can use that as an example on how to do it or just use that dashboard instead ;). - Anonymous
June 10, 2014
Excellent blog!!! - Anonymous
June 17, 2014
I am still getting errors with the new query (SQL 2008 R2). Only error is incorrect syntax near ' '. (error: 102).
Any ideas? - Anonymous
July 09, 2014
This blog has been a tremendous help. Thank you! I am looking to list all the patches that are required (deployed or not) in a summary and then link a secondary report that would list all the systems that require the missing patch(es). I have seen a couple of examples but I not having much luck. I would appreciate any direction at this point. Thanks again for all your work. - Anonymous
August 15, 2014
I hope you are still monitoring this post. I have been trying to get this to work and I'm getting a few errors I can't explain. As I build the Dashboard from parts 1, 2 and 3 and run the page to test my header and image are gone and covered up by the month and year selection section. I can't see the other parts of the Dashboard. Charts and other areas are gone. I kept your sizing to make sure and it still happens. Also, your code in part 5 looks right but why is it when I try to use it I get errors that I need to declare sections like EndMonth and StartYear and once I do I get a scalar error? Thanks for posting. This is what I wanted and I'd like to get it running. - Anonymous
August 15, 2014
Never mind this works Great. I had the same issues as Bryan with the ' '. and it was a space. Once I cleared the space the code and sheet ran great. I look forward to more posts. Thank You!! - Anonymous
September 16, 2014
Hello, I am being prompted to define query parameters when I click to apply the query for Software Update Details Dataset. SQL 2008 R2 - Anonymous
September 16, 2014
NM, read the commends on Part 4 and saw the updated SQL 2008 R2 query - Anonymous
September 22, 2014
Super writeup~!
The issue with the syntax and the '.' (error:102) is due to a space in the webpage that gets copied over. It's between:
Set @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0))
<---RIGHT HERE
Select distinct ucs.ci_id, count(ucs.status)[Count],
Delete this and your all set! - Anonymous
October 03, 2014
Excellent.
Thx. - Anonymous
April 15, 2015
I imported RDL file to 2012 R2 SSRS and change dataset to shared one in SCCM report folder. Table is displayed fine, but all pie charts are empty blue squares :( . Am I doing something wrong? Thank you! - Anonymous
May 07, 2015
Thank you Gary, I will have a look at that!
//Simon - Anonymous
June 05, 2015
Funny, I pasted the Part 5 query into the Software_Update_Details dataset, but Scanned Last 30 Days and Inventoried Last 30 Days still show results for all machines, not just the ones in the selected collection. Did I miss something simple? - Anonymous
June 08, 2015
Sorry, I was thinking about the Collection_Details dataset, not the Software_Update_Details dataset. I had the query adjusted so it shows scan and inventory sums for the collection, not all systems.