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

Software Updates Summary.rdl

Comments (19)

  1. @Bryan

    I copied and pasted the query in SQL 2012 and SQL 2008 R2 and it ran fine. Perhaps your copy and paste brought something over?

    Can you run it directly in SQL Management Studio? It’ll give you the line number where the syntax error occurred.

  2. @AnnDC, stay tuned. I’ll have a new blog in the coming weeks on a software update dashboard that you may find very helpful.

  3. Ed (DareDevil57) says:

    Thank you for this.

  4. Gary Simmons says:

    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?

  5. Anonymous says:

    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

  6. Gary Simmons says:

    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 ;).

  7. Helao says:

    Excellent blog!!!

  8. Bryan says:

    I am still getting errors with the new query (SQL 2008 R2). Only error is incorrect syntax near ‘ ‘. (error: 102).

    Any ideas?

  9. AnnDC says:

    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.

  10. Robert says:

    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.

  11. Robert says:

    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!!

  12. adam says:

    Hello, I am being prompted to define query parameters when I click to apply the query for Software Update Details Dataset. SQL 2008 R2

  13. adam says:

    NM, read the commends on Part 4 and saw the updated SQL 2008 R2 query

  14. Aaron M. says:

    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!

  15. Tamim says:

    Excellent.
    Thx.

  16. alex says:

    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!

  17. Simon says:

    Thank you Gary, I will have a look at that!

    //Simon

  18. David says:

    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?

  19. David says:

    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.

Skip to main content