Thursday, December 11, 2014

Creating a Custom Report for System Center 2012 R2 Configuration Manager

This blog is intended to demonstrate how to create a dashboard for System Center 2012 R2 Configuration Manager using SQL 2012 Reporting Services and Report Builder 3.0. 
For a practical demonstration, a dashboard for software updates will be created that uses tables, a pie chart, a line chart, and a column chart.
The built in reports for software updates contain a large quantity of information however, there isn’t a dashboard to provide a quick overview of update status.  In its given state, you would have to drill down into multiple reports, export the data, and then manipulate it in a spreadsheet to present it in a dashboard form.  It is possible to run SQL queries from within Excel or PowerPoint to display a dashboard but that’s beyond the scope of this blog.
Since there are so many different angles to display software update statuses, this dashboard will contain a query that will display data based on the v_Update_ComplianceStatusReported view.  This view contains the status of each software update for each system that has successfully performed a scan. It is important to note this as if you want a query based off a list of updates (software update groups) or a success rate to include inactive systems then this dashboard won’t work for you although you could use the examples and create your own dashboard.
The v_Update_ComplianceStatusReported view contains a column called “Status” to indicate if an update is Installed, Required, or Not Applicable.  Since we are only concerned about installed or required updates, the not applicable status will be filtered out and significantly reduce the number of data queried on.
The dashboard will allow filtering by collection and a duration (a start date and an end date) to display the following:
  • A table with the collection name, number of devices, number of devices that performed a software update scan in the last 30 days, number of systems that performed a hardware inventory in the last 30 days
  • A table that displays each required or installed update with the number of applicable systems, the article ID, the Bulletin ID, the Title, whether it is superseded, the Date the update was posted, whether it is deployed or not, the category, and the severity of the update.
  • A Pie chart that displays each inventoried operating system
  • A Line chart that displays the number of required and installed updates by month (or every couple months to fit in the graph)
  • A table with the total number of updates, the count or required updates, the count of installed updates, the count of deployed updates, and the count of updates not deployed
  • A Column chart that displays the number of updates installed or required by severity
  • A Column chart that displays the number of updates installed or required by category

This screenshot is what the dashboard will look like

Getting Started 
Ensure you can browse to both the Reports (e.g. http:///Reports) and ReportServer (e.g. http:///ReportServer) web pages on your server hosting SQL Reporting services.
Launch Report Builder 3.0 elevated (“Run as Administrator”) using and an account that has admin credentials to both the SQL server and ConfigMgr.  Although the account doesn’t need to be an administrator, for simplicity sake an admin account will be used in this demonstration.
Close the Getting Started screen.  On the left hand pane there are 5 folders to get familiar with.  If you don’t see the pane click the view tab then check Report Data.
 
  • The Built-in Fields won’t be used
  • The Parameters are variables that will be passed to the Datasets
  • The images will contain the system center logo
  • The Data Sources is the connection to the ConfigMgr database
  • The Datasets are the SQL queries
For more information view the Report Data Pane on Technet.
  1. Let’s start with the layout first.  Select the View tab then check  Properties. Click the center of the report then change the size in the Properties pane to 12.7in, 9.48in or your own custom size.
  2. Click the line in for the title text box.  In the Properties pane change the Location to 1in, 0in and the Size to 4.15in, 1in. Click the Home tab then change the font to Calibri 20 Bold. Click inside the Textbox and enter Software Updates Dashboard. 
  3. Click the Insert tab then click Text Box. Draw a textbox next to the Software Updates Dashboard textbox to the edge of the report.  Click the border of the textbox and change the size to 7.37833in, 1in and the location to 5.32167in, 0in. You may need to adjust the width to ensure it  isn’t off the report.  When resizing the textbox it should snap to the edge.
  4. Within the Insert tab click Image.  Draw the image in the upper left hand corner.  When the Image Properties box appears, click the Import button then select an image.
  5. The top of your report should look like this:
  6. Scroll to the bottom of the report and delete the textbox with the Execution Time.
You should now have a basic layout for your dashboard and can change it to meet your needs.   
To view what the report will look like press F5 or click the Run button in the Home tab.
In Part 1 it was mentioned that the dashboard will accept
certain parameters. By default, SRS will list 2 parameters per line.
For the Collection parameter, the dashboard will display the collections name but will pass its Unique ID to the datasets in a variable called ColID. 
To create this parameter, a dataset needs to be created.  To create the dataset, a data source is required.
Creating the Data Source
  1. In the Report Data pane right click Data Sources--> Click Add Data Source
    1. The Data Source Properties window will appear
  2. Change the Name to CM
  3. Select Use a connection embedded in my report
  4. Select Microsoft SQL Server as the connection type
  5. Click the Build… button
  6. Enter the Server name to the SQL server
  7. Select the ConfigMgr database
  8. Click Test Connection to ensure that a connection could be established
  9. Click OK
  10. Click OK
Creating the All_Collections Data Set
  1. In the Report Data pane right click Datasets --> Click Add Dataset
    1. The Dataset Properties window will appear
  2. Change the Name to All_Collections
  3. Select Use a dataset embedded in my report
  4. Select the CM Data source
  5. In the Query box enter the following: select distinct c.CollectionID, c.Name from v_Collection c order by c.Name
  6. Click OK
Creating the ColID Parameter 
  1. In the Report Data pane right click the Parameters folder --> Click Add Parameter.
    1. The Report Parameter Properties window will appear
  2. Change the Name to ColID (this is spelled colid)
  3. Change the prompt to Select a collection
  4. Click Available Values
  5. Select Get values from a query from the dataset
  6. Select CollectionID for the Value field
  7. Select Name for the Label field
  8. Click Default Values
  9. Select Specify values
  10. Click the Add button
  11. Enter SMS00001 for the Value
    1. This is the Collection ID for the All Systems collection.
  12. Click OK
Your Report data pane should look like this:
Creating the other parameters
Month Parameters
Although it is possible to create datasets for the other parameters, this demonstration will use static entries.
  1. In the Report Data pane right click the
    Parameters folder --> Click Add Parameter.
  1. The Report Parameter Properties window will appear
  • Change the Name to StartMonth
  • Change the prompt to Select a month that the report will start from
  • Change the Data type to Integer
  • Click Available Values
  • Select Specify values
  • Click the Add button 12 times
  • Enter January for the Label and 1 for the Value
  • Repeat this until you have December for the Label and 12 for the Value
  • Click Default Values
  • Select Specify values
  • Click the Add button
  • Click the fx button
  1. The Expression window will appear
  • Enter =Month(today()) for the expression value
  1. This will get the number value of the month for day the report is run
  • Click OK
  • Click OK 
Repeat these steps for the EndMonth parameter with the prompt of Select a month the report will end with
Year Parameters
Similar to the Month parameter the dashboard will create static year parameters with the starting year having a slight twist to its default value.
  1. In the Report Data pane right click the Parameters folder --> Click Add Parameter.
  1. The Report Parameter Properties window will appear
  • Change the Name to StartYear
  • Change the prompt to Select a year that the report will start from
  • Change the Data type to Integer
  • Click Available Values
  • Select Specify values
  • Click the Add button 10 times
  • Enter 2004 for both the Label and Value
  • Repeat this until you have 2014for both the Label and Value
  1. You may add or remove in more entries if you want to report update older or newer than 2004
  • Click Default Values
  • Select Specify values
  • Click the Add button
  • Click the fx button
  1. The Expression window will appear
  • Enter =Year(today()) -1 to default the report to the year previous to when it is run
  • Click OK
  • Click OK
Repeat these steps for the EndYear parameter with the prompt of Select a year that the report will end withand a default value of =Year(today())
Your Report Data pane should now look like this:
Creating Other Datasets
  1. Similar to how the All_Collections dataset was created, create the All_OperatingSystems Dataset with the query of:
    select distinct c.Name [Collection Name], gsos.Caption0 [Operating System], count(gsos.Caption0)[Number of Systems]from v_R_System rs
    JOIN v_FullCollectionMembership fcm on rs.ResourceID = fcm.ResourceIDJOIN v_Collection c on c.CollectionID = fcm.CollectionIDJOIN v_GS_OPERATING_SYSTEM gsos on gsos.ResourceID = fcm.ResourceIDWHERE c.CollectionID = @ColID
    group by c.Name, fcm.collectionID, gsos.Caption0
  2. Create another Dataset called Collection_Details with the query of:select distinct c.Name, count(rs.ResourceID) [Devices], (Select Count(LastScanTime) fromv_UpdateScanStatus uss where datediff("d",LastScanTime, getdate()) <= 30) [Scanned last 30 Days],(Select Count(LastHWScan) from v_GS_WORKSTATION_STATUS gsws where datediff("d",LastHWScan,getdate()) <= 30) [Inventory last 30 Days]from v_FullCollectionMembership fcm
    JOIN v_Collection c on c.CollectionID = fcm.CollectionIDJOIN v_R_System rs on rs.ResourceID = fcm.ResourceIDLEFT JOIN v_GS_WORKSTATION_STATUS gsws on gsws.ResourceID = fcm.ResourceIDwhere fcm.CollectionID = @ColID    group by  c.Name
  3. Create the final dataset called Software_Update_Details with the query of:DECLARE @StartDate datetime, @EndDate datetimeSet @StartDate = DATEFROMPARTS(@StartYear,@StartMonth,1)Set @EndDate = DATEFROMPARTS(@EndYear,@EndMonth,1)
    Select ucs.CI_ID, count(ucs.CI_ID) [Count],    CASE((status))
    When 3 Then 'Installed' When 2 Then 'Required' Else 'Not Applicable' End as 'Status', BulletinID,ArticleID,
    CASE((IsDeployed))
    When 0 Then 'No' Else 'Yes' End as 'Deployed',
    CASE((ui.IsSuperseded))    When 0 Then 'No' Else 'Yes' End as 'Superseded',
    CASE((ui.IsExpired))
    When 0 Then 'No' Else 'Yes' End as 'Expired',ui.Title, ui.DatePosted, 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_ComplianceStatusReported ucsJOIN v_UpdateInfo ui on ucs.CI_ID = ui.CI_IDJOIN v_CICategoryInfo_All cica JOIN v_CategoryInfo ci on cica.CategoryInstanceID =ci.CategoryInstanceID AND cica.CategoryTypeName = 'UpdateClassification' on ucs.CI_ID = cica.CI_IDWhere resourceid IN(Select ResourceID from v_FullCollectionMembership where CollectionID = @ColID)AND ui.DatePosted BETWEEN @StartDate AND @EndDate AND ucs.status <> 1 
    GROUP BY ucs.CI_ID, ucs.status, BulletinID, ArticleID, IsDeployed, ui.Title, DatePosted,cica.CategoryInstanceName, cica.CategoryInstanceID,ui.IsSuperseded, ui.Severity, ui.IsExpiredORDER BY ucs.status, BulletinID, ArticleID
Your Report Data pane should now look like this:

Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 4

Gary Simmons - MCSGary Simmons - MCS 
10 Dec 2013 6:32 PM 
Creating the Summary Tables
In Part 3 the dataset were created.  We’ll now take the data and place them into the tables and charts.
  1. Click the Insert tab --> click Table --> Insert Table
  2. Draw a table below the title
  1. A default table with 3 columns will appear
  • Hover over the bottom row of the first column
    and click the little notepad icon symbol that appears
  • Select the Dataset --> Select Collection_Details --> Select Name
  • Click Next
  • Add Devices to the next column and the scanned_last_30_days to the last column
  • Right click the top of the last column --> Select Insert Column --> Select Right
  • Add Inventory_last_30_Days to the last column
  • Right click the bottom row in the Devices column
  • Select fx Expression
  • Change the expression from =Fields!Devices.Value to =SUM(Fields!Devices.Value)
  • Repeat this step for the Scanned_last_30_Days
    and Inventory_last_30_Days column
  • Right click the bottom row of any column
  1. Notice the orange bracket in the first column
  • Select Row Group --> Delete Group
  • Select Delete group only--> Click OK
    1. This disables the looping ability of the table and the orange bracket will disappear
The table should look like this:
Press F5 to run the report to see if it is working properly
  1. Create another table and place it under the one just created
  2. Right click the bottom row of any column
  3. Select Row Group --> Delete Group
  4. Select Delete group only --> Click OK
  5. Hover over the bottom row of the first column and click the little notepad icon symbol that appears
  6. Select the Dataset-> Software_Update_Details -->  Title
  7. Hover over the bottom row of the second column and click the little notepad icon symbol that appears
  8. Select Software_Update_Details --> Status
  9. Hover over the bottom row of the third column and click the little notepad icon symbol that appears
  10. Select Software_Update_Details --> Status
  11. Create a new column
  12. Hover over the bottom row of the forth column and click the little notepad icon symbol that appears
  13. Select Software_Update_Details --> Deployed
  14. Hover over the bottom row of the forth column and click the little notepad icon symbol that appears
  15. Create a new column
  16. Select Software_Update_Details --> Deployed
  17. Right click the last row in the first column -->  Select fx Expression
  18. Change the expression to =Count(Fields!Title.Value)
  19. Right click the last row in the second column --> Select fx Expression
  20. Change the Expression to =SUM(IIF(Fields!Status.Value = "Required",1,0))
  21. Right click the last row in the third column --> Select fx Expression
  22. Change the expression to =SUM(IIF(Fields!Status.Value = "Installed",1,0))
  23. Right click the last row in the fourth column --> Select fx Expression
  24. Change the expression to =Sum(IIF(Fields!Deployed.Value = "Yes", 1, 0))
  25. Right click the last row in the fifth column --> Select fx Expression
  26. Change the expression to =Sum(IIF(Fields!Deployed.Value = "No", 1, 0))
The table should look like this:
Press F5 to run the report to see if it is working properly
Creating the Operating System Pie Chart

  1. Click the Insert tab --> Click Chart --> Chart Wizard
    1. The New Chart window will appear
  2. Select the All_OperatingSystems dataset
  3. Click Next
  4. Select the Pie Chart
  5. Click Next
  6. Place Operating_System in the Series
  7. Place Number_of_Systems in Values
  8. Click Next
  9. Select the Ocean style
  10. Click Finish
  11. Right click the Pie chart --> Select Show Data Labels
  12. Rename the Chart Title to Operating Systems
Press F5 to run the report to see if it is working properly
Creating the Line Chart

  1. Click the Insert tab --> Click Chart --> Chart Wizard
    1. The New Chart window will appear
  2. Select the Software_Update_Details dataset
  3. Click Next
  4. Click the Line Chart
  5. Click Next
  6. Add Count(DatePosted) in Values
  7. Add Status in Series
  8. Add DatePosted in Categories
  9. Click Next
  10. Select the Ocean style
  11. Click Finish
  12. Right click the chart --> Select Show Data Labels
  13. Click off the chart then right click it again
    1. The Chart Data Properties should appear next to it
  14. Under Category groups right click DatePosted
  15. Select Category Group Properties…
  16. Click the Fx button for the Label
  17. Change the Expression to =Format(Fields!DatePosted.Value, "MMM yy")
  18. Click OK
  19. Click the fx button for the group
  20. Change the Expression to =MonthName(Month(Fields!DatePosted.Value))
Press F5 to run the report to see if it is working properly
To make the Required line red follow these steps:

  1. Click the View tab
  2. Select Properties
  3. Click off then back on to the chart
  4. In the properties pane under Chart look for CustomPaletteColors
  5. Click the … button for the CustomPaletteColors collection
  6. Change the color in row #1 (it starts at 0) to Red
Press F5 to run the report to see if it is working properly
Creating the Severity Chart
  1. Click the Insert tab --> Click Chart --> Chart Wizard
    1. The New Chart window will appear
  2. Select the Software_Update_Details dataset
  3. Click Next
  4. Click the Column Chart
  5. Click Next
  6. Add Status in Series
  7. Add Severity in Categories
  8. Add Count(Severity) in Values
  9. Click Next
  10. Select the Ocean style
  11. Click Finish
  12. Right click the chart --> Select Show Data Labels
Creating the Category Chart
  1. Click the Insert tab --> Click Chart --> Chart Wizard
    1. The New Chart window will appear
  2. Select the Software_Update_Details dataset
  3. Click Next
  4. Click the Column Chart
  5. Click Next
  6. Add Status in Series
  7. Add Count(CategoryInstanceName) in Values
  8. Add CategoryInstanceName in Categories
  9. Click Next
  10. Select the Ocean style
  11. Click Finish
  12. Right click the chart -->Select Show Data Labels
Creating the Software Update Table
Adding in the list of all applicable updates doesn’t fit in with the concept of a dashboard but it is good information to see.
  1. Create another table and place it under the charts
  2. Hover over the bottom row of the first column and click the little notepad icon symbol that appears
  3. Select the Dataset --> Software_Update_Details --> Count
  4. Continue to add in the columns for each field returned from the dataset
To add sorting to the columns follow these steps:
  1. Right click a column header --> Select Text Box Properties…
  1. The Text Box Properties window will appear
  • Select Interactive Sorting
  • Check Enable interactive sorting on this text box
  • Select the column to be sorted in the Sort by selection
  • Repeat this for each column you want to allow sorting on
Press F5 to run the report to see if it is working properly
You should now have a dashboard to give you a quick view of overall software update status.  Although you may not use this dashboard in its entirety, you can create your own custom queries to satisfy your own requirements and gain more out of one the most powerful but underused  features of ConfigMgr 2012.

I purposely didn't update the top right text box.  Based on the steps within this demonstration, see if you can have it display the start month, start year, end month and end year.

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.CategoryInstanceIDAND 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
Courtesy Link : http://blogs.technet.com/b/gary_simmons_mcs/archive/2013/12/09/creating-a-custom-report-for-system-center-2012-r2-configuration-manager-part-1.aspx
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)