Appendix B. Demonstration Outcomes
In This Appendix
Chapter 2 Demonstration Outcomes
Chapter 3 Demonstration Outcomes
Chapter 4 Demonstration Outcomes
Chapter 6 Demonstration Outcomes
Chapter 7 Demonstration Outcomes
Chapter 8 Demonstration Outcomes
Chapter 9 Demonstration Outcomes
This appendix provides the expected outcomes for all the end-of-chapter demonstrations throughout the book. Refer to the specific chapters for information on each demonstration.
Chapter 2 Demonstration Outcomes
Chapter 2, “Installing and Configuring Configuration Manager Reporting,” discussed creating a reporting services point, using Configuration Manager (ConfigMgr) security roles, and accessing ConfigMgr SQL Server Reporting Services (SSRS) reports. The demonstrations from this chapter were designed to expose you to ConfigMgr role-based administration (RBA) and show you how to create RBA roles to leverage your ConfigMgr environment.
Working with the Report Reader Security Role
This demonstration was designed to familiarize you with creating an RBA security role that allows users in the ConfigMgr Report Reader Active Directory (AD) group to access ConfigMgr SSRS reports without using the ConfigMgr console. This demonstration was also used as an example in Chapter 2. Any user assigned to the AD user group would be able to see all reports and folders, as shown in Figure B.1.
Working with the Software Updates Report Reader Security Role
This demonstration was designed to show you how easy it is to import a security role from one ConfigMgr environment to another ConfigMgr environment. As such, see Chapter 2 for the steps necessary to import a ConfigMgr security role and assign it to an AD security group. Remember with this demonstration that users are limited to what collections they see, and therefore there is a limit on the computers for which they can view results. Figure B.2 shows the resulting folders that a user with this ConfigMgr security role can view.
Working with the Inventory Report Reader Security Role
The demonstration for the Inventory Report Reader role grants access to users assigned with the ConfigMgr Inventory Report Reader AD security group to view inventory reports accessing the ConfigMgr console. The properties of your Inventory Report Reader security role should look similar to Figure B.3.
In addition, your test user account in the ConfigMgr Inventory Report Reader AD security group will only be able to access the folders and report as shown in Figure B.4. The export for the Inventory Report Readers security role can be found in the online file Inventory Report Readers.xml. See Appendix C for further information regarding this file.
Chapter 3 Demonstration Outcomes
As discussed in Chapter 3, “Understanding Configuration Manager Data,” ConfigMgr gathers and stores in its database a large amount of data across many different views. Chapter 3 introduced the most common and important SQL data views. The demonstrations from this chapter were designed to expose you to this data and help you understand how to access it using SQL Server Management Studio.
Creating and Executing a Query
This demonstration was designed to help you become familiar with SQL Server Management Studio and executing a saved query. The results for this demonstration should look similar to Figure B.5.
Looking at Views
The v_R_System SQL view is one of the most common ConfigMgr SQL views and contains a lot of information. For example, computer name, virtual machine status, and logged-on user details are available in this SQL view. The results for this demonstration should look similar to Figure B.6.
v_GS_Computer_System is another common view that provides details about a computer, such as manufacturer, model, and role within a domain. The results for this demonstration should look similar to Figure B.7.
The v_Add_Remove_Programs SQL view is used to determine what software is installed on a computer. This view provides exactly the same column information as v_GS_ADD_REMOVE_PROGRAMS and v_GS_ADD_REMOVE_PROGRAMS_64, with the data of both views combined. This demonstration was designed to acquaint you with the columns available; your v_Add_Remove_Programs SQL view should look similar to Figure B.8.
Chapter 4 Demonstration Outcomes
Chapter 4, “Transact-SQL Primer,” provided information on writing efficient SQL queries, which are used to create the foundation of effective and efficient Configuration Manager reports. Having a good understanding of the SQL language can help you create amazing reports. The Chapter 4 demonstrations provided examples of both an ineffective query and an efficient query.
Demonstrating SQL Operators
Several exercises were included with this demonstration:
You were provided with a sample SQL file to help become familiar with SQL Server Management Studio by executing an existing query. Since no two ConfigMgr environments are exactly alike, your results for this review question will vary.
You were also asked to edit a sample query. This demonstration built on the previous demonstration, and the query should look similar to Listing B.1, available as online content. See Appendix C for details.
LISTING B.1 Editing the Sample Query
SELECT DISTINCT
ARP.Publisher0 as 'Publisher',
ARP.DisplayName0 as 'Display Name',
ARP.Version0 as 'Version'
FROM
dbo.v_Add_Remove_Programs ARP
WHERE
ARP.Publisher0 in (
'Dell',
'Microsoft Corporation',
'Adobe Systems Incorporated')
ORDER BY
ARP.Publisher0,
ARP.DisplayName0,
ARP.Version0
Once the query is executed, your results should look similar to those shown in Figure B.9.
For the review questions, your answers will be unique, except for the part where you were asked to order the results. As shown in Figure B.9, the results should be sorted alphabetically by publisher, display name, and finally version.
You were asked to create a SQL query, using aliases. This query was to retrieve a list of computer names (PC), user names (User), application names (Application), and versions (Version) of systems with Adobe Reader installed. Your query should look similar to Listing B.2, available as online content. See Appendix C for details.
LISTING B.2 Creating a Query with Aliases Sample Query
SELECT
RV.Netbios_Name0 as 'PC',
RV.User_Name0 as 'User',
ARP.DisplayName0 as 'Application',
ARP.Version0 as 'Version'
FROM
dbo.v_R_System_Valid RV
join dbo.v_Add_Remove_Programs ARP
on RV.ResourceID = ARP.ResourceID
WHERE
ARP.DisplayName0 like '%Adobe Reader%'
ORDER BY
RV.Netbios_Name0
The results of executing this query should look similar to those shown in Figure B.10.
Working with Aggregate Functions
This demonstration included two exercises:
You were asked to create a query that finds the minimum, average, and maximum hard disk sizes for system roles. Your query should look similar to Listing B.3, and the results of your query execution should be similar to those shown in Figure B.11, available as online content. See Appendix C for details.
LISTING B.3 Aggregate Functions: Creating a New Query Sample Query
SELECT
S.SystemRole0 as 'System Role',
MIN (HD.Size0) as 'Min HD for a System Role',
AVG (HD.Size0) as 'Avg HD for a System Role',
MAX (HD.Size0) as 'Max HD for a System Role'
FROM
dbo.v_GS_DISK as HD
JOIN dbo.v_GS_SYSTEM as S on S.ResourceID = HD.ResourceID
GROUP BY
S.SystemRole0
The second exercise asked you to create a query to show the COUNT of each installed application (DisplayName0). Your query should look similar to Listing B.4, available as online content (see Appendix C for details), and the results of executing your query should be similar to Figure B.12.
For the review question, each answer will vary.
LISTING B.4 COUNT Function Sample Query
SELECT
v_Add_Remove_Programs.Publisher0,
v_Add_Remove_Programs.DisplayName0,
v_Add_Remove_Programs.Version0,
COUNT(v_Add_Remove_Programs.DisplayName0) as 'Total'
FROM
dbo.v_Add_Remove_Programs
GROUP BY
v_Add_Remove_Programs.Publisher0,
v_Add_Remove_Programs.DisplayName0,
v_Add_Remove_Programs.Version0
Working with Date and Time Functions
The purpose of this demonstration was to help you become familiar with the different date and time functions. Your query should find the following information from all systems in your environment:
The current date
The date of the last hardware inventory scan
The number of minutes since the last hardware inventory scan occurred
The day of the month on which the last hardware inventory scan occurred
Your query should look similar to Listing B.5, available as online content (see Appendix C for details), and the results of your query execution should be similar to those shown in Figure B.13.
LISTING B.5 Date and Time Functions Sample Query
SELECT
R.Netbios_Name0 as 'PC',
GETDATE () as 'Today Date',
WS.LastHWScan as 'Last Hardware Inventory Scan',
DATEDIFF (MINUTE, WS.LastHWScan, getdate())
as 'Minutes since the last hardware inventory scan',
DATEPART (dd, WS.LastHWScan)
as 'Day of month last hardware inventory scan'
FROM
v_R_System as R
JOIN dbo.v_GS_WORKSTATION_STATUS as WS
on R.ResourceID = WS.ResourceID
ORDER BY
R.Netbios_Name0
Working with Data Transforms
This demonstration included a number of exercises to help you become familiar with the SQL functions CASE, CAST, COVERT, and ISNULL:
To use the CASE function, you were asked to create a query displaying all computer names and system enclosure chassis types and to convert the chassis type numeric codes to their actual meanings. Your query should look similar to Listing B.6, available as online content (see Appendix C for details), and the results of your query execution should be similar to those shown in Figure B.14.
LISTING B.6 CASE Function Sample Query
SELECT
R.Netbios_Name0 as 'PC',
CASE SE.ChassisTypes0
WHEN'1' THEN 'Other'
WHEN'2' THEN 'Unknown'
WHEN'3' THEN 'Desktop'
WHEN'4' THEN 'Low-profile desktop'
WHEN'5' THEN 'Pizza box'
WHEN'6' THEN 'Mini tower'
WHEN'7' THEN 'Tower'
WHEN'8' THEN 'Portable'
WHEN'9' THEN 'Laptop'
WHEN'10' THEN 'Notebook'
WHEN'11' THEN 'Handheld'
WHEN'12' THEN 'Docking station'
WHEN'13' THEN 'All-in-one'
WHEN'14' THEN 'Sub-notebook'
WHEN'15' THEN 'Space-saving'
WHEN'16' THEN 'Lunch box'
WHEN'17' THEN 'Main system chassis'
WHEN'18' THEN 'Expansion chassis'
WHEN'19' THEN 'Subchassis'
WHEN'20' THEN 'Bus expansion chassis'
WHEN'21' THEN 'Peripheral chassis'
WHEN'22' THEN 'Storage chassis'
WHEN'23' THEN 'Rack mount chassis'
WHEN'24' THEN 'Sealed-case PC'
END as 'Chassis Type'
FROM
v_R_System as R
JOIN dbo.v_GS_SYSTEM_ENCLOSURE as SE
on R.ResourceID = SE.ResourceID
ORDER BY
R.Netbios_Name0
To become familiar with the CAST and CONVERT functions, you were asked to create a query to get all system names and the last hardware scan date and convert the last hardware scan date to ANSI date format YYYY.MM.DD. Your query should look similar to Listing B.7, available as online content (see Appendix C for details), and the query results should be similar to those shown in Figure B.15.
LISTING B.7 CAST and CONVERT Functions Sample Query
SELECT
R.Netbios_Name0 as 'PC',
WS.LastHWScan as 'Last Hardware Inventory Scan',
CONVERT(nvarchar(30),WS.LastHWScan, 102) as 'Last Hardware Inventory Scan'
FROM
v_R_System as R
JOIN dbo.v_GS_WORKSTATION_STATUS as WS
on R.ResourceID = WS.ResourceID
ORDER BY
R.Netbios_Name0
The next exercise used the ISNULL function. You were asked to create a query to return all system names and user names (User_Name0) from v_R_System_Valid and have the query replace any NULL values in the v_R_System_Valid User_Name0 column with the value n/a. Your query should look similar to Listing B.8, available as online content (see Appendix C for details), and the results of executing the query should be similar to those shown in Figure B.16.
LISTING B.8 ISNULL Function Sample Query
SELECT
RV.Netbios_Name0 as 'PC',
ISNULL(RV.User_Name0,'n/a') as 'User'
FROM
dbo.v_R_System_Valid RV
ORDER BY
RV.Netbios_Name0
Executing an Inefficient Query
The results of the demonstration on executing an inefficient query should look similar to those shown in Figure B.17. Notice the execution time of 4:16 minutes and 990,176 rows.
Writing an Efficient Query
Your SQL query for this demonstration should look similar to Listing B.9, and when executed, the results should be similar to those shown in Figure B.18. Notice that the execution time is 1 second and the number of rows is 5,086. Comparing this to the inefficient query helps demonstrate the need for proper SQL joins.
Listing B.9 is available in the online file Efficient Query.sql. See Appendix C for further information.
LISTING B.9 INNER JOIN Sample Query
SELECT
R.Netbios_Name0 as 'Computer',
ARP.DisplayName0 as 'ARP Display Name'
FROM
dbo.v_R_System as R
INNER JOIN dbo.v_Add_Remove_Programs AS ARP on R.ResourceID = ARP.ResourceID
ORDER BY
R.Netbios_Name0 DESC,
ARP.DisplayName0 DESC
Chapter 6 Demonstration Outcomes
Chapter 6, “Building a Basic Report,” discussed using SQL Server Data Tools Business Intelligence (SSDT-BI) for Visual Studio 2013. Building on previously created SQL queries, you were to create a project and add a report to the project. This demonstration involved creating several reports and leveraging an existing SQL query to create a SSRS report with the core features. The first demonstration provides involved creating the Patch Compliance Progression report, and the second demonstration involved creating the Computer Hardware Information report.
Creating a New Project
After creating a project and shared data source, your end results should look similar to those in Figure B.19.
Creating a Patch Compliance Progression Report
Creating a Patch Compliance Progression report using the core SSRS report features as described in Chapter 6 should produce an outcome similar to Figure B.20.
Creating a Computer Hardware Information Report
The demonstration for this report provided several options, such as specifying color and logo. However, the basic design of your report should look similar to the design shown in Figure B.21.
Chapter 7 Demonstration Outcomes
Chapter 7, “Intermediate Reporting Concepts,” built on Chapter 6 by having you add prompts and chart to existing reports. Such items add tremendous value for report users, allowing them to visualize results and allowing them to select the exact value they need for a prompt.
Creating Template Reports
The results of the Creating Template Reports demonstrations should look similar to those in Figure B.22. Only the 14×8.5 template is shown in Figure B.22.
Modifying Page Sizes for Existing Reports
After configuring the Computer Hardware Information report with the landscape page size, your output should appear similar to Figure B.23.
Updating the Patch Compliance Progression Report
Because the prompt for the Patch Compliance Progression report was the example used in Chapter 7, the output is not provided in this appendix. However, the RDL is available in the online files for Chapter 7. See Appendix C for further information.
Updating the Computer Hardware Information Report
The prompt added to the Computer Hardware Information report provides results similar to those shown in Figure B.24.
Adding a Chart to the Patch Compliance Progression Report
The output of the Patch Compliance Progression report was an example provided within the chapter. The RDL file for this example is available in the online files for Chapter 7. See Appendix C for further information.
Adding a Chart to the Computer Hardware Information Report
Charts are an important part of SSRS reporting. Your demonstration on creating a Computer Hardware Information report output should look similar to that in Figure B.25.
Chapter 8 Demonstration Outcomes
Chapter 8, “SSRS Reporting Features,” discussed some of the advanced features of SSRS reports. The chapter discussed how to use report actions, which give you the ability to create drillthroughs to other reports as well as add URLs to objects. The chapter also discussed custom color palettes, which allow you to set your own color palettes for report items such as charts, either as a cycle of colors or associated to specific values. Finally, Chapter 8 described how to add your completed reports to the SSRS website and how to add a subscription.
Adding a Patch Compliance Progression Drillthrough Series
This demonstration asked you to create two new reports, a Patch Compliance Progression Details report and a subreport item for the Detailed Computer Information report. You modified the Patch Compliance Progression report, previously created in Chapter 6, by adding a Go To Report action on the [Name0] cell of its table item to drill through to the newly created Patch Compliance Progression Details report. To do this, you passed along the [Name0] value that was selected. The new report populates a list of missing patches in a table item and adds an action to the article ID values, linking to a URL for the specific Microsoft Knowledge Base article.
In addition to creating the reports, you needed to run the Patch Compliance Progression Details report and click on a value in the PC Name column. Figure B.26 shows the resulting Patch Compliance Progression Details report when the Surface PC Name is clicked. Don’t forget to click an article ID value from this report to view the Knowledge Base article for the patch, which opens in your web browser.
Adding a Computer Hardware Information Drillthrough Series
The demonstrations in Chapter 8 created a second drillthrough series, based on the Computer Hardware Information reports. Once completed, the Computer Hardware Information Chart report was previewed. Selecting a pie wedge in the chart opened the Computer Hardware Information Prompt report, which was modified in the demonstration to display a subreport of a newly created Hardware Model Chart report as well as a table of all models of computers belonging to the selected manufacturer.
Figure B.27 shows the resulting Computer Hardware Information Prompt report created by clicking the Dell Inc. pie wedge.
Applying a Custom Color Palette to Cycle Through Color Codes
This demonstration asked you to add to the Patching Compliance Progression Chart report a custom color palette to cycle through color codes of your choice. Once completed, the report preview results should look similar to those shown in Figure B.28.
Applying a Custom Color Palette Based on Values
The demonstration asked you to add a custom color palette to the Computer Hardware Information Chart report to define a specific color value for each manufacturer value. Once assigned to the Chart item, the results should look similar to Figure B.29.
Manually Adding Reports to the SSRS Website
Once your custom reports are created with various features and items available in SSDT-BI, you will want to add them to the SSRS website to allow readers to run and view them. This demonstration asked you to manually add the identified reports to a new folder created at the SSRS website. When complete, the listing of reports should be similar to that shown in Figure B.30. Be sure to run each report to ensure that it displays properly, without errors. Remember to change the report’s data source to the default created data source for SSRS.
Publishing Reports to SSRS
Another method of adding reports to the SSRS website is to publish them from SSDT-BI. To do so, configure the Deploy settings in SSDT-BI and then right-click a report from the Solution Explorer and select Deploy. Your reports are automatically added to the website, as shown in the example in Figure B.31.
Creating a Subscription
Once you added reports to the SSRS website, you were asked to create a subscription for a report of your choice to export the report to a PDF file on a Windows file share. After creating and executing the subscription, you were to validate that it ran successfully by looking at the report’s subscription tab, shown in Figure B.32, and verifying that the PDF file exists on the specified share.
Chapter 9 Demonstration Outcomes
Chapter 9, “Role-Based Administration and Reporting,” demonstrated the steps necessary to convert a SQL query to an RBA SQL query and included the one-time steps to ensure SSDT-BI could create RBA reports. In the first demonstration you converted the Patch Compliance Progression report created in Chapter 7 and updated it to be an RBA-complaint report. This demonstration was also used as an example in Chapter 9; you can follow along with the chapter as the report is upgraded to an RBA report version. The RDL for this report is available in the online files for Chapter 9; see Appendix C for further information.
In the second demonstration in Chapter 9 you were to convert the Computer Hardware Information query to an RBA query and test that query. The RBA query results for prompting for the manufacturer should look similar to Listing B.10. This listing is available in the online file RBA Prompt for Manufacturer.sql for Chapter 9; see Appendix C for further information.
LISTING B.10 RBA Prompt for Manufacturer
SELECT DISTINCT
cs.Manufacturer0
FROM
fn_rbac_GS_COMPUTER_SYSTEM(@UserSIDs) CS
ORDER BY
cs.Manufacturer0
The primary query for the Computer Hardware Information report would look similar to Listing B.11. This listing is available in the online file Computer Hardware Information RBA Query.sql for Chapter 9. See Appendix C for further information.
LISTING B.11 Computer Hardware Information RBA Query
SELECT
CS.Name0,
CS.UserName0,
CS.Manufacturer0,
CS.Model0,
WS.lasthwscan as 'Last HW scan'
FROM
fn_rbac_GS_COMPUTER_SYSTEM(@UserSIDs) CS
LEFT OUTER JOIN fn_rbac_GS_WORKSTATION_STATUS(@UserSIDs) WS
on WS.resourceid = CS.resourceid
WHERE
CS.Manufacturer0 = @Manu