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

  • 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.