Appendix C. Available Online
In This Appendix
Creating and Assigning ConfigMgr Security Roles
Understanding Configuration Manager Data
Transact-SQL Primer
Basic Report Design
Building a Basic Report
Intermediate Reporting Concepts
SSRS Reporting Features
Role-Based Administration and Reporting
Online content is available to provide added value to readers of System Center Configuration Manager Reporting Unleashed. This material, organized by chapter, can be downloaded from http://www.informit.com/store/product.aspx?isbn=0672337789. This content is not available elsewhere. Note that the authors and publisher do not guarantee or provide technical support for the material.
Creating and Assigning ConfigMgr Security Roles
Chapter 2, “Installing and Configuring Configuration Manager Reporting,” discussed how to install the Configuration Manager (ConfigMgr) reporting services point. It also discussed the importance of security and how to leverage ConfigMgr security roles and Active Directory (AD) groups to adhere to the principle of least privilege required. Three XML files are provided as online content:
Report Reader.xml: This file includes the exported results of creating the Report Reader security role. This security role allows users to view all SQL Server Reporting Services (SSRS) reports within ConfigMgr; however, users are limited to seeing the computers and users within the collections to which they are assigned. You can import this file into your own ConfigMgr environment to avoid going to the effort of creating it on your own.
Software Updates Report Reader.xml: This file includes the exported results of creating the Software Updates Report Reader security role. This security role allows users to view the software update reports within ConfigMgr; however, users are limited to seeing the computers and users within the collections to which they are assigned.
Inventory Report Readers.xml: This file includes the outcome of the Inventory Report Readers security role demonstration. The security role allows users to view the inventory reports within ConfigMgr; users are limited to seeing the computers and users within the collections to which they are assigned.
Understanding Configuration Manager Data
Chapter 3, “Understanding Configuration Manager Data,” discusses where ConfigMgr stores its inventory data and how to find that information. It also includes listings of sample SQL queries for several ConfigMgr views. These queries, available online for download, can be opened and executed against your ConfigMgr database using the SQL Server Management Studio tool. The following files are provided to accompany Chapter 3:
03list01_DiscoveryDataSampleQuery.sql: This discovery data query lists all computers in the ConfigMgr environment, the last logged-on user name, and the last discovery time for heartbeat discovery.
03list02_HardwareInventorySampleQuery.sql: This hardware inventory sample query lists computers with the Warranty Information Reporting v3 software product installed.
03list03_HardwareInventoryHistorySampleQuery.sql: This hardware inventory history view query lists computers where the hard drive size has changed since a previous inventory cycle. This query provides an example of using the history views and current hardware views together for comparison purposes.
03list04_SoftwareInventorySampleQuery.sql: This query uses software inventory information to provide a count of versions of Internet Explorer.
03list05_SoftwareUpdateSampleQuery.sql: This software update query provides a count of all missing software updates for each computer in ConfigMgr.
03list06_SoftwareMeteringSampleQuery.sql: This software metering sample query displays the start and stop times for all software metering data since February 16, 2013.
03list07_StatusMessageSampleQuery.sql: This status message sample query returns the date and time of the last ConfigMgr backup for each site server.
03list08_StateMessageSampleQuery.sql: This software update state message query returns the last enforcement message for each software update scanned on the computer named GJ5.
03list09_CollectionDataSampleQuery.sql: This collection data sample query lists all computer names within the ConfigMgr All Systems collection.
PatchReport.txt: This is a sample query used in the creating and executing a query demonstration in Chapter 3. When executed in SQL Server Management Studio, it lists all computers in ConfigMgr, the last logged-on user name for each system, the number of required updates to install, and date of the system’s last hardware inventory scan.
Transact-SQL Primer
Chapter 4, “Transact-SQL Primer,” discusses the basics of SQL queries to help you successfully write your own queries. The following .SQL query files, available online, provide the different queries discussed throughout this chapter. The file names beginning with xB_list are the outcomes of the demonstrations in Chapter 4:
04list01_PatchReportSample.sql: This sample query is provided as Listing 4.1 and is used throughout the chapter to analyze sections of SQL code. When executed, this query provides a list of computer names, the last logged-on user name of each computer, the number of missing software updates for the computer, and date of the last hardware scan.
04list10_AggregateFunctionsQuery.sql: This sample query is provided as Listing 4.10 and returns a list of system roles in the ConfigMgr environment. Along with each system role, the query provides a count of computers belonging to the role and the minimum, average, maximum, and sum of the RAM for all systems within each system role.
04list11_DateAndTimeSampleQuery.sql: This date and time sample query, provided as Listing 4.11, demonstrates the GETDATE, DATEDIFF, and DATEPART functions.
04list13_CASESampleQuery.sql: This CASE function sample query, provided as Listing 4.13, translates the month value of the last hardware scan date, using a CASE function.
04list14_CONVERTandCASTSampleQuery.sql: This sample query, provided as Listing 4.14, demonstrates the use of the CAST and CONVERT functions and illustrates the differences between the two.
04list15_ISNULLSampleQuery.sql: This sample query, provided as Listing 4.15, demonstrates the ISNULL function by replacing NULL values from the |Last_Logon_Timestamp with the date 1980-04-05. The query returns computer names, with the Last_Logon_Timestamp column shown next to the ISNULL function column for comparison.
04list16_SampleInefficientQuerywithoutJOINStatement.sql: This is an example of an inefficient query, provided as Listing 4.16. Caution should be taken if this query is executed within your ConfigMgr environment.
Caution: Running the Inefficient Sample Query is not Recommended
The authors do not recommend running 04list16_SampleInefficientQuerywithoutJOINstatement.sql in your environment; it is provided only for reference. The query will take a long time to execute, will produce a large number of results, and may have a negative performance impact on your ConfigMgr database. (Other than the performance impact, however, no negative or lasting effects will occur.)
04list17_SampleQueryUsingJOIN.sql: This is a similar query to Listing 4.16; however, it uses a JOIN statement. This query (from Listing 4.17) is much more efficient and takes very little time to execute.
04list18_SampleINNERJOINStatement.sql: This sample query uses an INNER JOIN statement to list all common values between multiple SQL views.
04list19_SampleLEFTOUTERJOINStatement.sql: This sample query uses a LEFT OUTER JOIN statement. This query displays all values on the left side of the JOIN equation (v_R_System_Valid) and joins any common data from the right side of the equation (dbo.v_R_User).
04list20_SampleRIGHTOUTERJOINStatement.sql: This sample query uses a RIGHT OUTER JOIN statement. This query is the opposite of the LEFT OUTER JOIN; it displays all data on the right-side of the equation (dbo.v_R_User) and joins any common data from the left side (dbo.v_R_System_Valid).
04list21_SampleFULLJOINStatement.sql: This sample query uses a FULL JOIN statement to list all data on both sides of the equation.
04list22_DemonstratingAnInefficientQuery.sql: This query is used in the JOIN demonstration in Chapter 4 to show the impacts and effects of an inefficient query.
Chapter4Demo.sql: This is a sample query used in demonstrations from Chapter 4.
xB_list01_DemonstratingSQLOperators.sql: This query shows the result of the SQL operators demonstration in Chapter 4, illustrated in Appendix B, “Demonstration Outcomes.”
xB_list02_CreatingAQueryWithAliases.sql: This query shows the demonstration outcome, listed in Appendix B, from Chapter 4, where a new query was written using aliases to list computer names, user names, application names, and versions of systems with Adobe Reader installed.
xB_list03_AggregateFunctions.sql: This query is the aggregate function demonstration outcome listed in Appendix B from Chapter 4. The query returns the minimum, average, and maximum hard disk size for system roles in ConfigMgr.
xB_list04_COUNTFunction.sql: This query is the demonstration outcome, listed in Appendix B, for the COUNT function in Chapter 4. This query is based from the Chapter4Demo.sql file and adds the function to show the number of installs for each application.
xB_list05_DateAndTimeFunctions.sql: This query is the demonstration outcome, listed in Appendix B, of the date and time functions in Chapter 4. This query is based on the 04list11_DateAndTimeSampleQuery.sql file, previously described in this section, and has been updated to include the current date, last hardware inventory scan, number of minutes since last scan, and day of the month of the last scan.
xB_list06_CASEFunction.sql: This query is the demonstration outcome, listed in Appendix B, for the CASE function in Chapter 4. This query uses the function to convert the system enclosure chassis type numeric codes to actual names.
xB_list07_CONVERTFunction.sql: This query is the demonstration outcome, listed in Appendix B, for the CONVERT function in Chapter 4. This query uses that function on the last hardware scan date to change the data to ANSI date format (YYYY.MM.DD).
xB_list08_ISNULLFunction.sql: This query is the demonstration outcome, listed in Appendix B, for the ISNULL function in Chapter 4. This query uses that function on the User_Name0 column to replace any NULL values with n/a.
xB_list09_JOINStatement.sql: This query is the demonstration outcome listed in Appendix B of the JOIN statement in Chapter 4. This query updates the inefficient query provided (04list22_DemonstratingAnInefficientQuery.sql) to use JOIN statements.
Basic Report Design
Chapter 5, “Basic Report Design,” discusses important concepts related to designing a report. It explains the different types of reports in a series and basic design rules, such as having a consistent look and feel across all reports, using standard page sizes, and using templates. A sample report request form that can be used in your environment, Requesting a Report.docx, is provided as online content for this chapter. This is the request form that is discussed and analyzed throughout Chapter 5 to help demonstrate how to gather important report information before beginning to write a report. You can tailor this document to gather specific report details for your environment.
Building a Basic Report
Chapter 6, “Building a Basic Report,” introduces creating reports with SQL Server Data Tools 2014 Business Intelligence (SSDT-BI) for Visual Studio 2013. This tool is used to create reports that can be added to your ConfigMgr SSRS website. The following SQL query (.sql) and report (.rdl) files, available online, can be opened with SQL Server Management Studio and SSDT-BI, respectively:
06list01_PatchProgressionQuery.sql: This is the query used for the PatchDataset of the Patch Compliance Progression report created in Chapter 6.
Chap6_Patch Compliance Progression.rdl: This report is the finished outcome, shown in Appendix B, of the Patch Compliance Progression report in Chapter 6.
Chap6_Computer Hardware Information query.sql: This is the query outcome for the Computer Hardware Information report in Chapter 6.
Chap6_Computer Hardware Information.rdl: This report is the finished outcome, shown in Appendix B, for the Computer Hardware Information report in Chapter 6.
To successfully preview the files with data from your ConfigMgr database, simply add them to an already created SSDT-BI project, as explained in Chapter 6, and modify the data source of the report to use the shared data source in your environment.
Intermediate Reporting Concepts
Chapter 7, “Intermediate Reporting Concepts,” illustrates how to create and use report templates as a starting point when creating new reports. The chapter described how to use parameters to create fewer and more generic reports that prompt readers for the details to filter and tailors the results to their specific requirements. The chapter also explained and demonstrated chart items, which you can use to make report data more visual. The following SQL query (.sql) and report (.rdl) files are available as online content to provide you with the different reports demonstrated throughout this chapter.
07list03_AllCollectionNamesAndIDs.sql: This query returns all collection names and their collection IDs from the ConfigMgr environment. This is used in Chapter 7 to populate the Collection Prompt parameter with available values.
Chap7_Template (8.5x11).rdl: This report is the letter portrait (8.5×11in) template outcome from the demonstration in Chapter 7.
Chap7_Template (11x8.5).rdl: This report is the letter landscape (11×8.5in) template outcome from the demonstration in Chapter 7.
Chap7_Template (14x8.5).rdl: This report is the legal landscape (14×8.5in) template outcome from the demonstration in Chapter 7, shown in Appendix B.
Chap7_Patch Compliance Progression(Modified Page Size).rdl: This is the report outcome from modifying the page size of the Patch Compliance Progression report to fit on a paper size of 8.5×11in, created as part of the demonstrations in Chapter 7.
Chap7_Computer Hardware Information(Modified Page Size).rdl: This is the report outcome from modifying the page size of the Computer Hardware Information report to fit on a paper size of 11×8.5in, created as part of the demonstrations in Chapter 7 shown in Appendix B.
Chap7_Patch Compliance Progression (Prompt).rdl: This is the report outcome of modifying the Patch Compliance Progression report to add a multi-value prompt for ConfigMgr collection names, created as part of the demonstrations in Chapter 7.
Chap7_Computer Hardware Information Prompt.rdl: This is the report outcome for the Computer Hardware Information Prompt report, part of the Chapter 7 demonstrations shown in Appendix B. The report contains a dropdown list prompt of manufacturer names from the computers in your ConfigMgr environment.
Chap7_Patch Compliance Progression Chart.rdl: This is the report outcome for the Patch Compliance Progression Chart report, part of the Chapter 7 demonstrations. This report includes a 3D exploded pie chart to illustrate the number of missing patches for systems in your ConfigMgr environment. The report also includes an action on the pie chart’s wedges that go to the Patch Compliance Progression report, passing along the CollectionID value to the prompt.
Chap7_Computer Hardware Information Chart.rdl: This is the report outcome for the Computer Hardware Information Chart report, part of the Chapter 7 demonstrations shown in Appendix B. This report includes a 3D exploded pie chart to illustrate the count of computers by manufacturer in your ConfigMgr environment. The report also includes an action on the pie chart’s wedges to go to the Computer Hardware Information Prompt report, passing along the Manufacturer value that was selected.
As discussed in the previous “Building a Basic Report” section, these reports must be added to an existing SSDT-BI project and have their data source updated for your environment; for the report actions to function properly, it is also required to download and add all reports (.rdl) listed in this chapter to the same SSDT-BI project.
SSRS Reporting Features
Chapter 8, “SSRS Reporting Features,” demonstrates advanced report features available in reports. These features include drillthroughs, custom color palettes for report objects, and different methods of adding completed reports to the SSRS website. The following files, discussed throughout the chapter, are provided for your reference:
08list01_DetailedComputerQuery.sql: This query is used to create the dataset for the Detailed Computer Information report, discussed in Chapter 8.
08list02_DetailedPatchComplianceQuery.sql: This query is used to create the dataset for the Patch Compliance Progression Details report, discussed in Chapter 8.
Software Update Compliance.rdl: This is the report used to illustrate custom color codes based on specific values, discussed in Chapter 8.
Chap8_Detailed Computer Information (Drillthrough).rdl: This is the report outcome for the Detailed Computer Information report, part of the Chapter 8 demonstrations. This report lists the computer name, user name, hardware make and model, and last hardware scan date for a computer specified by the user in a parameter.
Chap8_Patch Compliance Progression Details (Drillthrough).rdl: This is the report outcome for the Patch Compliance Progression Details report, part of the Chapter 8 demonstrations, shown in Appendix B. This report contains a subreport item, referencing the Detailed Computer Information report, and a table with a detailed list of missing patches for a specific computer. An action is added on the [ArticleID] value of the table that goes to a URL using the value of the [InfoURL] column from the dataset.
Chap8_Patch Compliance Progression (Drillthrough).rdl: This is the modified report named Patch Compliance Progression that was created in Chapter 7. The report is modified during the demonstrations in Chapter 8 to add an action on the [Name0] cell to go to the Patch Compliance Progression Details report, passing along the PC Name value to the report parameter.
Chap8_Hardware Model Chart (Drillthrough).rdl: This is the report outcome for the Hardware Model Chart report, part of the Chapter 8 demonstrations. This report contains a chart showing the count of models for a specific manufacturer.
Chap8_Computer Hardware Information Prompt (Drillthrough).rdl: This is the modified report named Computer Hardware Information Prompt, created in Chapter 7 and shown in Appendix B. The report is modified as part of the Chapter 8 demonstrations to add a subreport item referencing the Hardware Model Chart report.
Chap8_Patch Compliance Progression Chart (Custom Color Palette - Cycle).rdl: This is the report outcome of the custom color palette created to cycle through color codes, part of the Chapter 8 demonstrations and shown in Appendix B. The custom color palette is applied to the chart item in the Patch Compliance Progression Chart report, created in Chapter 7.
Chap8_Computer Hardware Information Chart (Custom Color Palette - Values).rdl: This is the report outcome of the custom color palette created to apply specific color codes based on dataset values, part of the Chapter 8 demonstrations and shown in Appendix B. The custom color palette is applied to the chart item in the Computer Hardware Information Chart report, created in Chapter 7. Colors are applied based on the hardware manufacturer values.
Role-Based Administration and Reporting
Chapter 9, “Role-Based Administration and Reporting,” illustrated how ConfigMgr RBA reporting works. The chapter examined a normal SQL query and reports covered in earlier chapters and showed how to enhance the reports so they can be used with the role-based administration (RBA) feature of ConfigMgr and SSRS. The following files are provided for your reference:
09list01_RBA_PatchReportSample.sql: This sample query provides a list of computer names, the last logged-on user name, the number of missing software updates for the computer, and the date of the last hardware scan.
09list02_RBA Software Update Sample Query.sql: This query is an updated version of Listing 9.1. It returns a list of computer names, the last logged-on user name, the number of missing software updates for the computer, and the date of the last hardware scan for a given user (defined by the @UserSIDs variable).
09list04_Query to determine ConfigMgr UserSIDs.sql: This query is used to query ConfigMgr for the UserSIDs. It returns a list of UserSIDs for a particular @UserTokenSIDs, The UserSIDs information is used to limit the information a user can see within SSRS reporting.
09list05_Mixed RBA Software Update Sample Query.sql: This sample query is an updated version of Listing 9.1 and provides a list of computer names, the last logged-on user name, the number of missing software updates for the computer, and the date of the last hardware scan for a given user. However, it does not follow best practices of always using RBA functions, which actually decreases the execution time for the query. This mixed query’s execution time is faster than that of the full RBA listing, 09list01_RBA_PatchReportSample.sql.
09list06_Query for all collection names and IDs.sql: This sample query is the non-RBA version of 09list07_RBA query for collection names and IDs.sql and provides a list of collection names and collection IDs.
09list07_RBA query for collection names and IDs.sql: This sample RBA query returns a list of collection names and collection IDs.
09list08_Software Update Sample Query.sql: This query is a non-RBA version of 09list01_RBA_PatchReportSample.sql and returns a list of computer names, the last logged-on user name, the number of missing software updates for the computer, and the date of the last hardware scan.
CodeGroup.txt: This text file includes edits to RSPreviewPolicy.config within the PrivateAssemblies folder and adds a CodeGroup block to grant the ConfigMgr reporting code assembly full trust permission, allowing RBA reports to be previewed in SSDT-BI.
Computer Hardware Information RBA Query.sql: This sample RBA query returns a list of computer names, the last logged-on user name, manufacturer, computer model, and date of the last hardware scan.
pageLoad function for SQL 2008.txt: This text file includes the edits to ReportingServices.js for SQL 2008. Implementing this edit allows web browsers such as Chrome to view ConfigMgr reports.
pageLoad function for SQL 2008 R2.txt: This text file includes the edit to ReportingServices.js for SQL 2008 R2. Implementing this edit allows web browsers such as Chrome to view ConfigMgr reports.
pageLoad function for SQL 2012.txt: This text file includes the edit to ReportingServices.js for SQL 2012. Implementing this edit allows browsers such as Chrome to view ConfigMgr reports.
Patch Compliance Progression.rdl: This report is the finished outcome, as shown in Appendix B, of the Patch Compliance Progression report in Chapter 9.
RBA Prompt for Manufacturer.sql: This RBA query returns a list of computer manufacturers.
Chap9_RBA_Computer Hardware Information Prompt.rdl: This report is the finished outcome, shown in Appendix B, of the Computer Hardware Information report in Chapter 9.