Chapter 4. Transact-SQL Primer
In This Chapter
Understanding the Basic SQL Sections in a Query
Understanding Aggregate Functions
Understanding Date and Time Functions
Transforming Data Stored in the ConfigMgr Database
Understanding the SQL JOIN Statement
Demonstrating Aggregate Functions
Demonstrating Date and Time Functions
Transact-SQL, otherwise known as SQL or T-SQL, is the language used to query SQL Server databases. As a Configuration Manager (ConfigMgr) administrator, you should already be familiar with collection queries, which use Windows Management Instrumentation (WMI) Query Language (WQL). Since WQL is a subset of SQL, there are many similarities between the two languages. However, they are two separate and distinct languages.
This chapter is important for a couple of reasons:
SQL Server Reporting Services (SSRS) uses SQL queries to create the reports that everyone from a ConfigMgr administrator to C-level managers will use to make informed decisions.
SQL is a nondiscriminatory language; it allows you to create and execute queries that could have a serious impact on the performance of your SQL Server. Worse yet, it can provide you with inaccurate results.
This chapter discusses the basics of SQL queries. It provides an understanding of what is required to successfully write your own SQL queries. Using the guidance in this chapter, you can create SQL queries by incorporating best practices for ConfigMgr reporting, providing optimal performance and reliable and expected results.
The chapter also covers the structure of a basic SQL query; it discusses and demonstrates using operators such as DISTINCT and GROUP BY. It illustrates how to work with date and time functions and how to translate the resulting data into a more usable result. This chapter also discusses and demonstrates using JOIN statements. Appropriate use of the JOIN statement alone can provide the most significant performance improvement in SQL queries, ensuring that the results are appropriate and accurate yet with minimal performance impact on your SQL Server.
SELECT
FROM
WHERE
ORDER BY
These sections form the basic core of a SQL query. Not all these sections are mandatory to obtain results from a query, but they are strongly recommended for producing clear and easy-to-read results.
This chapter uses the query in the PatchReport.txt file introduced in Chapter 3, “Understanding Configuration Manager Data” (available as online content for this book; see Appendix C, “Available Online”), to break down and analyze each of these sections. The query is provided in Listing 4.1.
LISTING 4.1 Patch Report Sample
SELECT distinct
CS.Name0,
CS.UserName0,
CASE
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then
('Needs '+(cast(sum(case when UCS.status=2 then 1
else 0 end)as varchar(10))+ ' Patches'))
else 'Good Client'
end as 'Status',
ws.lasthwscan as 'Last HW scan'
FROM
dbo.v_UpdateComplianceStatus as UCS
LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM as CS
on CS.ResourceID = UCS.ResourceID
JOIN v_CICategories_All as catall2
on catall2.CI_ID = UCS.CI_ID
JOIN v_CategoryInfo as catinfo2
on catall2.CategoryInstance_UniqueID
= catinfo2.CategoryInstance_UniqueID
and catinfo2.CategoryTypeName = 'UpdateClassification'
LEFT JOIN v_gs_workstation_status as ws
on ws.resourceid = CS.ResourceID
LEFT JOIN v_FullCollectionMembership as FCM
on FCM.ResourceID = CS.ResourceID
WHERE
UCS.status = '2'
and FCM.CollectionID = 'SMS00001'
GROUP BY
CS.Name0,
CS.UserName0,
ws.lasthwscan,
FCM.CollectionID
ORDER BY
CS.Name0,
CS.UserName0
Using the SELECT Statement
A SQL query starts with the SELECT section. This section is used to indicate the columns within a SQL view that will be included in the results of a query. Notice that you must separate columns with commas.
Listing 4.2 shows the SELECT section of the query shown in Listing 4.1. Executing the full query in Listing 4.1 displays the computer names, user names, count of missing software updates (status), and last hardware inventory scan data (Last HW scan) of systems in your environment. Each of these columns is identified in Listing 4.2.
Tip: SQL Introduction Note
This chapter is a general primer for T-SQL; it does not discuss every possible T-SQL feature, function, or option. This SQL primer is designed to provide an understanding of virtually everything you could need for ConfigMgr report writing. As such, T-SQL items that you would not normally see in a ConfigMgr environment are not covered in this book.
LISTING 4.2 The SELECT Section of a SQL Query
SELECT distinct
CS.Name0,
CS.UserName0,
CASE
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then
('Needs '+(cast(sum(case when UCS.status=2 then 1
else 0 end) as varchar(10))+ ' Patches'))
else 'Good Client'
end as 'Status',
ws.lasthwscan as 'Last HW scan'
Using the FROM Statement
The FROM section of a query indicates the SQL views within the ConfigMgr database used to find the data. When you join multiple views together, this section also defines the common column between views, allowing the query results to display one row of data that spans multiple views.
Listing 4.3 shows the FROM section of the query shown in Listing 4.1. As shown, data is gathered from several SQL views and, as indicated by the ON statement, the views are linked together via a common column. This example is retrieving data from the v_UpdateComplianceStatus and v_GS_COMPUTER_SYSTEM views. In order for the results to have unique rows containing columns from both of these views, you should connect them on the ResourceID column, which is common to both.
LISTING 4.3 The FROM Section of a SQL Query
FROM
dbo.v_UpdateComplianceStatus as UCS
LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM as CS
on CS.ResourceID = UCS.ResourceID
JOIN v_CICategories_All as catall2
on catall2.CI_ID = UCS.CI_ID
JOIN v_CategoryInfo as catinfo2
on catall2.CategoryInstance_UniqueID
= catinfo2.CategoryInstance_UniqueID
and catinfo2.CategoryTypeName = 'UpdateClassification'
LEFT JOIN v_gs_workstation_status as ws
on ws.resourceid = CS.ResourceID
LEFT JOIN v_FullCollectionMembership as FCM
on FCM.ResourceID = CS.ResourceID
Defining Criteria with the WHERE Statement
The WHERE section allows you to narrow your query results by defining criteria or filtering out unwanted data. Although technically an optional section in a SQL query, its use is strongly recommended in order to focus your results to the information you want to relay.
Listing 4.4 shows the WHERE section of the query in Listing 4.1. In this case, the query is only gathering data where the status value is equal to 2 and the computer objects are in the ConfigMgr collection where the CollectionID is SMS00001, better known as the default All Systems collection.
LISTING 4.4 The WHERE Section of a SQL Query
WHERE
UCS.status = '2'
and FCM.CollectionID = 'SMS00001'
Using ORDER BY
The ORDER BY section allows for results to be sorted by columns, in a manner that makes sense to you. In most cases, each column is sorted in alphabetical order, from A to Z. Much like the WHERE section, ORDER BY is also optional in a SQL query; its use is also strongly recommended as it allows viewers to easily read the results because it can sort in alphabetical order. While incorporating ORDER BY may create a minimal delay in returning results as they must first be sorted, a sorted list is far more usable and readable than an unsorted list.
Listing 4.5 shows the ORDER BY section for the query in Listing 4.1. In this example, the results are sorted in alphabetical order, starting with the computer name (CS.Name0) column and then by the user name (CS.UserName0) column.
LISTING 4.5 The ORDER BY Section of a SQL Query
ORDER BY
CS.Name0,
CS.UserName0
Using Secondary Operators
Several secondary sections, or operators, can be used in SQL queries to further manipulate and focus the results. As you begin to use and become familiar with basic queries, incorporating these secondary operators will be key to further expanding and fine-tuning your reports:
DISTINCT
GROUP BY
Aliases
Operators
Once again, the query in Listing 4.1 demonstrates all these operators, and the following sections focus on each of them in turn.
Using the DISTINCT Keyword
The DISTINCT keyword is an operator used to remove any duplication of data from the results, thereby making each row unique. When writing ConfigMgr reports, you may need to search for a specific item, such as the display name of an application from Add/Remove Programs (v_Add_Remove_Programs). If you were to select all display names from v_Add_Remove_Programs, you would end up with hundreds of thousands of rows. When you add the DISTINCT operator after SELECT, as shown in Listing 4.6, only one unique row is returned for each display name.
LISTING 4.6 The DISTINCT Operator
SELECT DISTINCT
CS.Name0,
CS.UserName0,
CASE
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then
('Needs '+(cast(sum(case when UCS.status=2 then 1
else 0 end)as varchar(10))+ ' Patches'))
else 'Good Client'
end as 'Status',
ws.lasthwscan as 'Last HW scan'
The GROUP BY Operator
You use the GROUP BY operator with a number of SQL actions, such as COUNT, SUM, AVG, MIN, and MAX. This section of a query allows you to combine rows together into groups. Listing 4.7 shows this operator grouping computer names (CS.Name0), user names (CS.UserName0), last hardware scan date (ws.lasthwscan), and collection ID (CollectionID) together so that they can be counted in the SELECT section to provide a numeric value for the missing updates.
LISTING 4.7 The GROUP BY Section of a SQL Query
GROUP BY
CS.Name0,
CS.UserName0,
ws.lasthwscan,
FCM.CollectionID
Using SQL View Aliases
Aliases allow you to temporarily rename a SQL view to something more meaningful and unique for the purpose of your query. In many cases, it is used to shorten the SQL view name to make it more manageable and easier to read. Aliases are identified as the text following as when specifying a SQL view under the FROM section. The boldface in Listing 4.8 indicates the use of aliases.
FROM
dbo.v_UpdateComplianceStatus as UCS
LEFT OUTR JOIN dbo.v_GS_COMPUTER_SYSTEM as CS
on CS.ResourceID = UCS.ResourceID
JOIN v_CICategories_All as catall2
on catall2.CI_ID = UCS.CI_ID
JOIN v_CategoryInfo as catinfo2
on catall2.CategoryInstance_UniqueID
= catinfo2.CategoryInstance_UniqueID
and catinfo2.CategoryTypeName = 'UpdateClassification'
LEFT JOIN v_gs_workstation_status as ws
on ws.resourceid = CS.ResourceID
LEFT JOIN v_FullCollectionMembership as FCM
on FCM.ResourceID = CS.ResourceID
It is important to note that if you abbreviate a SQL view using an alias, you must refer to the view using the defined alias throughout the rest of the query. For example, if you shorten dbo.v_GS_COMPUTER_SYSTEM to CS, you must refer to CS in all sections of the query. If you use the original, long form instead of the alias in the SELECT section, you receive the error shown in Figure 4.1.
Using Operators
Operators allow you to perform arithmetic or logical operations within a query. They can help filter, identify, and manipulate data to return useful and more specific results. As in other programming languages, there are several different operators available for use. Table 4.1 lists the operators most commonly used in SQL queries.
Tip: Complete List of Operators
For a complete list of available operators, see the SQL Operators section in the T-SQL online help, available at http://msdn.microsoft.com/en-us/library/ms174986(v=SQL.110).aspx.
Column Naming in Query Results
Every column in a SQL view has a default name. These names are assigned by developers and may not necessarily seem meaningful. Without clear column names, it could be challenging for readers who are not administrators or familiar with ConfigMgr to identify the results of a query.
For example, consider the ws.Lasthwscan column name, shown as part of the query in Listing 4.9. This column name is not meaningful for most report readers. However, you can give the column a more meaningful display name in the query results by using the AS operator after identifying a column in the SELECT section of the query, followed by the new display name, within single quotes. Figure 4.2 shows the display name Last HW Scan, instead of ws.lasthwscan, in the Results window when the query is executed.
LISTING 4.9 Column-Naming Example
SELECT DISTINCT
CS.Name0,
CS.UserName0,
CASE
when (sum(case when UCS.status=2 then 1 else 0 end))>0
then ('Needs '+(cast(sum(case when UCS.status=2 then 1
else 0 end)as varchar(10))+ ' Patches'))
else 'Good Client'
end as 'Status',
ws.lasthwscan AS 'Last HW Scan'
Caution: Quotation Marks with Operators
Pay close attention to the single quotation marks when copying a query from a website or document as it might get replaced with a stylized (or “smart”) single quote. You must be careful to always use ' instead of ‘ or ’.
It might be difficult to notice the difference, but the last two quotes are stylized, and using either of them will result in an error.
Understanding Aggregate Functions
Aggregate function is just a more technical name for a numeric function. From a non-programmer’s perspective, an aggregate function simply returns numbers for the results rather than individual values. As you may know by now, ConfigMgr captures a considerable amount of data; instead of creating reports with hundreds or even thousands of rows and having to manually count each individual item, you can use aggregate functions to count, sum, or average those results.
In the “Demonstrating SQL Operators” section, later in this chapter, you will be asked how many times Adobe Reader XI is listed. This is the type of question you as a ConfigMgr administrator will often encounter, and it is a scenario where using aggregate functions can help provide the information you need without spending a significant amount of time trying to determine the answer.
Table 4.2 provides a list of aggregate functions. These functions are generally used in a summarized data report to give an overview of the results before drilling down into the details. Chapter 5, “Basic Report Design,” explains summarized data reports.
It is important to note that aggregate functions use the GROUP BY section of a query, previously explained in the “Using Secondary Operators” section of this chapter.
The query shown in Listing 4.10 returns a list of system roles in the ConfigMgr environment, with a count of computers for each role; the minimum, average, and maximum RAM in each system role; and the total RAM for all systems within each specific system role. Using this query, you can further analyze each aggregate function. Figure 4.3 displays the results of this query.
LISTING 4.10 Aggregate Functions Query
SELECT
S.SystemRole0 as 'System Role',
COUNT(S.SystemRole0) as 'Count of System Roles',
MIN (Ram.TotalPhysicalMemory0) as 'Min RAM for a System Role',
AVG (Ram.TotalPhysicalMemory0) as 'Avg RAM for a System Role',
MAX (Ram.TotalPhysicalMemory0) as 'Max RAM for a System Role',
SUM (Ram.TotalPhysicalMemory0) as 'Sum of RAM for a System Role'
FROM
dbo.v_GS_X86_PC_MEMORY as RAM
join dbo.v_GS_SYSTEM as S on S.ResourceID = RAM.ResourceID
GROUP BY
S.SystemRole0
Using the COUNT Function
The COUNT function returns the total number of items. The example in Listing 4.10 looks for a count of the SystemRole0 column. The query returns the number of systems within the system role, shown in the second column.
Using the MIN (Minimum) Function
The MIN function returns the smallest number for a column. The example in Listing 4.10 looks for the smallest amount of TotalPhysicalMemory0 on a system for each SystemRole0. Using the results shown in Figure 4.3, you can tell that the lowest amount of RAM is 1.5GB in a server and 1GB in a workstation.
Using the AVG (Average) Function
The AVG function returns the average number for a column. The example in Listing 4.10 looks for the average TotalPhysicalMemory0 for each SystemRole0. From the results shown in Figure 4.3, you can determine that the average RAM for servers is 13GB, and it’s over 4GB for a workstation.
Using the MAX (Maximum) Function
The MAX function returns the largest number for a column. The example in Listing 4.10 looks for the largest amount of TotalPhysicalMemory0 on a system for each SystemRole0. From the results shown in Figure 4.3, you can see that the most RAM is 54GB in a server and 10GB in a workstation.
Using the SUM Function
The SUM function returns the total value for a column. The example in Listing 4.10 looks for a sum of TotalPhysicalMemory0 for each SystemRole0. The results displayed in Figure 4.3 show that the total RAM for all servers is 226GB, and it’s 74GB total for workstations.
Understanding Date and Time Functions
Date and time data is an important aspect of report writing. There will be many occasions when you need to return a date or compare one date to another within ConfigMgr data. You might, for example, need to display the install date for an application, compare an operating system (OS) install date to today in order to get the number of days since deployment, or show the date and time of the last hardware scan for a system.
The following sections discuss the most common date and time functions used in ConfigMgr reports.
Tip: List of Date and Time Functions
The SQL online help at http://msdn.microsoft.com/en-us/library/ms186724.aspx contains a complete list of date and time functions.
Understanding Date Parts
Before starting to define date and time functions, let’s explain the date format. Say you’re working with the date July 6, 2014, at 9:44 AM; SQL displays this as 2014-07-06 09:44:59.120. To use most date/time functions, you must first define what you are looking for, such as the number of years or the number of days between two dates. Transact SQL uses date parts to define this information. The most commonly used date parts in ConfigMgr reporting are listed in Table 4.3.
Tip: Further Date Part Information
Refer to the SQL online help at http://msdn.microsoft.com/en-us/library/ms174420.aspx for a complete list of available date parts.
Using Common Date and Time Functions
Table 4.4 lists and describes the common date and time functions; it also provides the proper syntax to use for each function within queries and ConfigMgr reports. Listing 4.11 further demonstrates the use of these functions in a sample SQL query. Figure 4.4 shows the results of this query.
ConfigMgr stores date values differently, depending on where the date originates. For example, the last hardware inventory scan date and time is based on the computer’s clock, whereas status messages are generally recorded using Universal Coordinated Time (UTC).
LISTING 4.11 Date and Time Sample Query
SELECT
R.Netbios_Name0,
GETDATE () as 'Today Date',
OS.InstallDate0 as 'OS install date',
DATEDIFF (dd, OS.InstallDate0, getdate()) as '# of Days since installed',
DATEPART (mm, OS.InstallDate0) as 'Month installed'
FROM
v_R_System as R
JOIN dbo.v_GS_OPERATING_SYSTEM as OS on R.ResourceID = OS.ResourceID
ORDER BY
R.Netbios_Name0
The functions mentioned in this section work only with data stored in datetime format. It is important to note that some dates and times within the ConfigMgr database are recorded in string format. Before using these functions for string data, you must first convert the values to datetime format. Converting data is further discussed in the “Transforming Data Stored in the ConfigMgr Database” section of this chapter.
Transforming Data Stored in the ConfigMgr Database
Many items in ConfigMgr are stored in a SQL database either in short form or as codes. This method of storing data is useful because it saves space; however, from a reporting standpoint, the meaning or description of these codes may not always be clear. For example, if a query returns a chassis type value of 3, what does this mean? The following sections detail how to use functions to transform that data to make it more meaningful to the report reader.
Using the CASE Function
You use the CASE function to evaluate an expression and translate the data based on the results. For example, the CASE function could be used to translate the value 1 to January. Listing 4.12 shows the proper syntax for the CASE function.
CASE <Expression>
When 1 then 'January'
When 2 then 'February'
When 3 then 'March'
...
Else 'Winter'
End as 'column name'
The sample query provided in Listing 4.13 uses a CASE function to return all system names, today’s date, the last hardware scan date, the month of the last hardware scan, and a translated value for the month. Figure 4.5 display the results of this query.
LISTING 4.13 CASE Sample Query
SELECT
R.Netbios_Name0,
GETDATE() as 'Today Date',
WS.LastHWScan as 'Last HW Scan date',
datepart(mm,WS.LastHWScan) as 'Month',
CASE DATEPART (mm,WS.LastHWScan)
When 1 then 'January'
When 2 then 'February'
When 3 then 'March'
When 4 then 'April'
When 5 then 'May'
When 6 then 'June'
When 7 then 'Summer'
When 8 then 'Summer'
When 9 then 'Fall'
When 10 then 'Fall'
Else 'Winter'
End as 'time of year'
FROM
v_R_System as R
join dbo.v_GS_WORKSTATION_STATUS as WS on R.ResourceID = WS.ResourceID
ORDER BY
R.Netbios_Name0
Tip: Case Function Information
For more information about the CASE function, see the SQL online books at http://msdn.microsoft.com/en-us/library/ms181765.aspx.
Converting Data with the CONVERT and CAST Functions
The CONVERT and CAST functions convert data from one type to a different data type that you specify. For example, if data is returned as a string, these functions allow you to convert the data to datetime format. As mentioned in the “Understanding Date and Time Functions” section earlier in this chapter, certain functions can be used only with specific data types.
Table 4.5 shows the proper syntax for the CONVERT and CAST functions. The sample query provided in Listing 4.14 returns computer names, all installation dates from Add/Remove Programs (v_Add_Remove_Programs), the same installation date in datetime format (using the CAST function), and the installation date converted from datetime to string format (using the CONVERT function). Notice the display difference between datetime format and string format.
LISTING 4.14 CONVERT and CAST Sample Query
SELECT
R.Netbios_Name0 as 'PC',
ARP.InstallDate0 as 'ARP Install Date',
CAST (ARP.InstallDate0 as datetime) AS 'ARP Date/Time',
CONVERT (Char(19), Cast(ARP.InstallDate0 AS DATETIME), 0) as 'ARP Date string'
FROM
v_R_System as R
join v_Add_Remove_Programs as ARP on R.ResourceID = ARP.ResourceID
join v_GS_SYSTEM as S on R.ResourceID = S.ResourceID
WHERE
S.SystemRole0 = 'Workstation'
and isdate(ARP.InstallDate0)=1
and ARP.InstallDate0 is not Null
ORDER BY
R.Netbios_Name0
Tip: Convert and Cast Function Information
For more information about the CONVERT and CAST functions, refer to the SQL online books at http://msdn.microsoft.com/en-us/library/ms187928.aspx.
Using the ISNULL Function
The ISNULL function checks to see if the data returned contains NULL values; if so, ISNULL returns either the actual value or a value specified as its replacement. The sample query in Listing 4.15 returns all system names (R.Netbios_Name0), their last logon time stamp (R.Last_Logon_Timestamp), and a third column where all NULL values for R.Last_Logon_Timestamp are replaced with 1980-04-05, identified by the black arrows in Figure 4.7. Following is the proper syntax for the ISNULL function:
ISNULL(Column Name, 'Replacement Value')
LISTING 4.15 ISNULL Sample Query
SELECT distinct
R.Netbios_Name0,
R.Last_Logon_Timestamp0,
isnull(R.Last_Logon_Timestamp0, '1980-04-05')
FROM
v_R_System as R
ORDER BY
R.Netbios_Name0
Tip: ISNULL Function Information
For more information about the ISNULL function, refer to the SQL online books at http://msdn.microsoft.com/en-us/library/ms184325.aspx.
Understanding the SQL JOIN Statement
There are many places to report on data within ConfigMgr. All the SQL views are independent of each other, and each one stores specific data. For example, v_Add_Remove_Programs stores the Add/Remove Programs data (also known as Programs and Features in Windows Vista and later) that is inventoried from each computer. If you were to query both v_R_System and v_Add_Remove_Programs within one query without joining them, the results from your query would be huge.
To get an idea what we mean by huge, assume that you have a small lab with 200 computers within the v_R_System SQL view. Also assume that v_Add_Remove_Programs contains only 4,000 entries. If you query the two views without any joining information, you have 200 computers × 4,000 ARP entries, which would equal 800,000 rows of data. Listing 4.16 shows two queries to support the previous example and a third query that does not join any views together. Figure 4.8 shows the results of these queries; the numbers are similar to those in the previous example.
LISTING 4.16 Sample Query Without a JOIN Statement
SELECT
count(R.ResourceID) as '# of computers'
FROM
dbo.v_R_System as R
SELECT
count(ARP.ResourceID) as '# of ARP entries'
FROM
dbo.v_Add_Remove_Programs AS ARP
SELECT
R.Netbios_Name0 as 'Computer',
ARP.DisplayName0 as 'ARP Display Name'
FROM
dbo.v_R_System as R,
dbo.v_Add_Remove_Programs AS ARP
ORDER BY
R.Netbios_Name0 DESC,
ARP.DisplayName0 DESC
More important than the actual results of the query is the impact of running a query without any joins. As you can see in Figure 4.9, it took the query 2 minutes 13 seconds to execute (see the first black arrow, on the left), and it returned 927,303 rows (see the second black arrow). Not only did this query take a long time to run, the amount of data returned is so large that it is not usable by anyone.
The following sections discuss how to join SQL views to each other, the different types of joins available, and when to use them all.
What Is a JOIN?
A SQL JOIN allows you to combine information from two or more SQL views and to specify how to link the tables together. The sample query in the “Understanding the SQL JOIN Statement” section of this chapter does not use any joins, and therefore SQL assumes that all rows of data from the first view must apply to each row of data in the second view. When you use joins to define how the two views link together, the query returns rows of data that contain results from both views combined.
Using the ResourceID Column
For most ConfigMgr views, you use the ResourceID column from one SQL view to join another. This column is a unique ID given to each system in ConfigMgr and allows you to link data rows together for results that pertain to each system. Using the example in the “Understanding the SQL JOIN Statement” section, let’s adjust the query to join the SQL views together and see how this affects the results. The query in Listing 4.16 returns a huge number of rows that are unusable because it does not join any views together. Listing 4.17 adds the appropriate JOIN to the query to provide the desired results. Figure 4.10 shows the results of this new query.
LISTING 4.17 Sample Query Using JOIN
SELECT
R.Netbios_Name0 as 'Computer',
ARP.DisplayName0 as 'ARP Display Name'
FROM
dbo.v_R_System as R
JOIN dbo.v_Add_Remove_Programs AS ARP ON R.ResourceID = ARP.ResourceID
ORDER BY
R.Netbios_Name0 DESC,
ARP.DisplayName0 DESC
Notice in Figure 4.10 that it took less than one second (see the first black arrow, on the left) to execute the query (from Listing 4.17), and the number of rows returned is only 5487 (see the second black arrow). This is a much more valuable and efficient query than the one in Listing 4.16, which does not use a JOIN statement.
Real World: User Versus Computer
It would seem logical to join the user’s ResourceID with a computer’s ResourceID. However, this is one of the few cases where ResourceID should not be joined together as it represents completely different information in the two tables, and the two are not relevant to each other.
When joining views on a user account, it is best to use the user’s unique ID. The user’s unique ID in the v_R_System_Valid view would be the combination of the User_Domain0 and User_Name0 columns with a in-between; on the other hand, for v_R_User, the matching unique ID that can be joined is stored in the Unique_User_Name0 column. You will see this later in the chapter, in Listing 4.19.
Using the v_R_System_Valid View
The v_R_System_Valid view is very important when you’re writing SQL queries for a couple of reasons:
The underlying data in this view is indexed, which makes your query faster and much more efficient.
This view shows only active clients in ConfigMgr. This is important in ConfigMgr because when someone deletes a client, all its references are not immediately deleted as well. Instead, ConfigMgr keeps the data until it is automatically purged from the database, approximately five days later.
For these reasons, the authors recommend using the v_R_System_Valid view instead of v_R_System when writing queries.
Explaining the JOIN Statements
The following sections use the RGB color wheel shown in Figure 4.11 to help explain all the different variations of the JOIN statement. The color names and index letters are listed in Table 4.6.
The INNER JOIN Statement
INNER JOIN, or in its short form simply JOIN, is the most commonly used join statement. It returns all common items between two or more SQL views.
To help visualize how an INNER JOIN works, let’s assign colors to each SQL view in the query shown in Listing 4.18, based on the RGB color wheel:
Green: v_R_System_Valid (RV)
Red: v_GS_X86_PC_MEMORY (RAM)
Blue: v_HS_X86_PC_MEMORY (HRAM)
This query performs a JOIN (INNER JOIN) between all three SQL views, as shown in Figure 4.12. This means it is looking for data where all three of the views intersect. The column that is joining the views (RV.ResourceID) must contain matching data in each view for the column to be returned in the results. Since the three views in the query represent the green, red, and blue sections of the RGB color wheel, the results from the query represent the white area.
LISTING 4.18 Sample INNER JOIN Statement
SELECT
RV.Netbios_Name0 as 'PC Name',
RV.User_Domain0 as 'User Name',
RAM.TotalPhysicalMemory0 as 'Current RAM',
HRAM.TotalPhysicalMemory0 as 'Historic RAM'
FROM
dbo.v_R_System_Valid RV
JOIN dbo.v_GS_X86_PC_MEMORY RAM on RV.ResourceID = RAM.ResourceID
JOIN dbo.v_HS_X86_PC_MEMORY HRAM on RV.ResourceID = HRAM.ResourceID
WHERE
RAM.TotalPhysicalMemory0 <> HRAM.TotalPhysicalMemory0
and RV.Is_Virtual_Machine0 = 0
ORDER BY
RV.Netbios_Name0
The LEFT OUTER JOIN Statement
The LEFT OUTER JOIN statement takes all data from the left side of the JOIN equation and joins it to any common data on the right side of the equation. Again, using the RGB color wheel, let’s assign colors to each SQL view in the query in Listing 4.19:
Green: v_R_System_Valid (RV)
Blue: v_R_User (RU)
The v_R_System_Valid view (green) is on the left side of the equation. All data rows in this view are returned as part of the results. Any data from the v_R_User view (blue) that matches with a row (containing the same user name) from v_R_System_Valid is joined and included in the results. The final results from this query are the green circle in addition to the cyan section from the blue circle, shown in Figure 4.13.
LISTING 4.19 Sample LEFT OUTER JOIN Statement
SELECT
RV.Netbios_Name0 as 'PC Name',
RV.User_Domain0+''+RV.User_Name0 as 'User Name',
RU.Unique_User_Name0 as 'Unique User Name'
FROM
dbo.v_R_System_Valid RV
LEFT OUTER JOIN dbo.v_R_User RU
on RV.User_Domain0+''+RV.User_Name0 = RU.Unique_User_Name0
ORDER BY
RV.Netbios_Name0,
RU.Unique_User_Name0
When executing this query, notice in Figure 4.14 that rows 16, 17, 22, and 27 have NULL values for the unique user name. This is because the right side of the equation has no common data for that row.
The RIGHT OUTER JOIN Statement
The RIGHT OUTER JOIN, as you might guess, is the mirror-opposite of a LEFT OUTER JOIN: It takes all data from the right side of the equation and any common data on the left side of the equation.
Notice that the only change to the query in Listing 4.20 is that the LEFT OUTER JOIN statement now reads RIGHT OUTER JOIN.
LISTING 4.20 Sample RIGHT OUTER JOIN Statement
SELECT
RV.Netbios_Name0 as 'PC Name',
RV.User_Domain0+''+RV.User_Name0 as 'User Name',
RU.Unique_User_Name0 as 'Unique User Name'
FROM
dbo.v_R_System_Valid RV
RIGHT OUTER JOIN dbo.v_R_User RU
on RV.User_Domain0+''+RV.User_Name0 = RU.Unique_User_Name0
ORDER BY
RV.Netbios_Name0,
RU.Unique_User_Name0
Much as in the example in the section “The LEFT OUTER JOIN Statement” with the sample code in Listing 4.19, let’s assign the same colors from the RGB color wheel to the SQL views in Listing 4.20:
Green: v_R_System_Valid (RV)
Blue: v_R_User (RU)
Since the v_R_User view (blue) is on the right side of the equation, all data rows in this view are returned as part of the results. Any data from the v_R_System_Valid view (green) that matches with a row, containing the same user name, from v_R_User is joined and included in the results. The final results from this query are the blue circle with the cyan section from the green circle, shown in Figure 4.15.
When you execute this query, notice in Figure 4.16 that rows 1 through 49 have NULL values for the PC name and user name. This is because the left side of the equation has no common data for that row.
The FULL JOIN Statement
A FULL JOIN statement does exactly what its name tells you it does: It takes everything from both sides of the equation and displays that as the results.
Notice that the only change to the query in Listing 4.21 is that the RIGHT OUTER JOIN statement now reads FULL JOIN.
LISTING 4.21 Sample FULL JOIN Statement
SELECT
RV.Netbios_Name0 as 'PC Name',
RV.User_Domain0+''+RV.User_Name0 as 'User Name',
RU.Unique_User_Name0 as 'Unique User Name'
FROM
dbo.v_R_System_Valid RV
Full Join dbo.v_R_User RU on RV.User_Domain0+''+RV.User_Name0 =
RU.Unique_User_Name0
ORDER BY
RV.Netbios_Name0,
RU.Unique_User_Name0
Once again, let’s assign the same colors to the SQL views, as previously done in the examples in Listing 4.19 and Listing 4.20:
Green: v_R_System_Valid (RV)
Blue: v_R_User (RU)
In the FULL JOIN statement, the results shown include all green and blue circles, with the cyan section represents a row where the results contain a matching user name from both views, meaning no NULL values, as illustrated in Figure 4.17.
When you execute the query in Listing 4.21, notice in Figure 4.18 that there are some rows containing NULL values on the left side, some rows with NULL values on the right side, and some rows without any NULL values—indicating the differences between the green, blue, and cyan sections of the RGB color wheel.
Demonstrating SQL Operators
The purpose of this section is to help you become familiar with many of the secondary SQL operators discussed in this chapter. In each of the following sections, you will modify a query based on the required results. Use the information explained throughout this chapter to return the requested data.
Running the Sample SQL File
To get started, load a sample SQL file into SQL Server Management Studio and execute the SQL query. Then follow these steps:
1. Open SQL Server Management Studio.
2. Connect to your ConfigMgr database server. (See the “Connecting to the ConfigMgr Database Server” section in Chapter 3 for information on how to perform this step.)
3. Using the dropdown list of available databases, change the database to your ConfigMgr site database.
4. Open a new query window by clicking New Query.
5. Open the Chapter4Demo.SQL file in Windows Notepad (provided as online content for this book; see Appendix C) and then copy and paste the contents to the new query window.
6. Execute the query by clicking Execute.
7. Review the results:
How many rows are there?
Take note of how many versions are listed for a single application, such as Adobe Reader, in your environment.
How many times is a specific version of that application, say Adobe Reader XI, listed?
Editing the Sample Query
To edit the sample query, perform the following steps:
1. Edit the query to add the following items:
DISTINCT
An alias named ARP for the view dbo.v_Add_Remove_Programs
Results filtered to the following publishers:
Dell
Microsoft Corporation
Adobe Systems Incorporated
Results ordered by Publisher, Display Name, and Version
A viewer-friendly display name for each column with the trailing 0s removed and spaces added between separate words instead of underscores
2. Execute the query by clicking Execute.
3. Review the results:
How many rows are now there?
Using an application installed in your environment, say Adobe Reader, note which versions are present.
After making the changes to the query, how many times is this application listed now?
How are the results ordered?
List the computer names (PC), user names (User), application names (Application), and versions (Version) of systems with Adobe Reader installed. (The column names that should appear in the results are listed here in parentheses.)
Order the results alphabetically by computer name.
Demonstrating Aggregate Functions
The purpose of this section is to help you become familiar with the different aggregate functions and their use within ConfigMgr queries. These functions, COUNT in particular, appear throughout this book, and you will utilize them as you begin creating your own queries. These demonstrations begin by asking you to modify existing queries to incorporate and analyze the different aggregate functions discussed to this point.
Tip: Aggregate Query Answers
Answers for these aggregate queries can be found in Appendix B, “Demonstration Outcomes.”
Creating a New Query
To get started, create a query that returns the minimum, average, and maximum hard disk sizes for system roles. Perform the following steps:
1. Open SQL Server Management Studio.
2. Connect to your ConfigMgr database server.
3. Using the dropdown list of available databases, change the database to your ConfigMgr database.
4. Open a new query window by clicking New Query.
5. Create a query to find the minimum, average, and maximum hard disk sizes for each system role.
1. Open SQL Server Management Studio.
2. Connect to your ConfigMgr database server.
3. Using the dropdown list of available databases, change the database to your ConfigMgr database.
4. Open a new query window by clicking New Query.
5. Open the Chapter4Demo.SQL file in Windows Notepad (provided as online content for this book; see Appendix C) and then copy and paste the contents to the new query window.
6. Modify this query to show the COUNT of each installed application (DisplayName0).
7. Execute the query.
8. Review the results. How many times is Adobe Reader XI installed?
Demonstrating Date and Time Functions
This section is meant to help you become familiar with the different date and time functions. Create a new query to 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
The day of the month on which the last hardware inventory scan occurred
To become familiar with using date and time functions, follow these steps:
1. Open SQL Server Management Studio.
2. Connect to your ConfigMgr database server.
3. Using the dropdown list of available databases, change the database to your ConfigMgr database.
4. Open a new query window by clicking New Query.
5. Copy the sample query from Listing 4.11 and paste it into the New Query window.
6. Modify the query to include the following details:
The current date
The date of the last hardware inventory scan
The number of minutes since the last hardware inventory scan
The day of the month on which the last hardware inventory scan occurred
Tip: Date and Time Demonstration
See Listing 4.1 to find the view and column for the last hardware inventory scan (Last HW Scan).
Demonstrating Data Transforms
The following sections will help you become familiar with the CASE, CAST, CONVERT, and ISNULL SQL functions. In each section, start with the following steps and then apply the required changes and functions to achieve the requested results:
1. Open SQL Server Management Studio.
2. Connect to your ConfigMgr database server.
3. Using the dropdown list of available databases, change the database to your ConfigMgr database.
4. Open a new query window by clicking New Query.
Applying the CASE Function
Create a query to display all computer names and system enclosure chassis types. Convert the chassis type numeric codes to their descriptions, provided in Table 4.7.
Tip: Locating Chassis Type
Use the dbo.v_GS_SYSTEM_ENCLOSURE SQL view in the ConfigMgr database to find the ChassisType column.
Applying the CAST and CONVERT Functions
Create a query to get all system names and the last hardware scan date. Convert the last hardware scan date to ANSI date format YYYY.MM.DD.
Tip: Cast and Convert Hint
The ANSI date format YYYY.MM.DD is style 102.
Applying the ISNULL Function
Create a query to return all system names and user names (User_Name0) from the v_R_System_Valid view. Have the query replace any NULL values in the v_R_System_Valid User_Name0 column with the value n/a.
Demonstrating JOIN Statements
The purpose of this section is to help you become familiar with the different type of JOIN statements. For each example, start with the following steps and then apply the necessary changes to achieve the requested results.
1. Open SQL Server Management Studio.
2. Connect to your ConfigMgr database server.
3. Using the dropdown list of available databases, change the database to your ConfigMgr database.
4. Open a new query window by clicking New Query.
Executing an Inefficient Query
Execute the query in Listing 4.22 to show the impact and results of an inefficient query that does not use JOIN statements. Take note of the number of rows as well as the time it takes to execute the query.
LISTING 4.22 Demonstrating an Inefficient Query
SELECT
R.Netbios_Name0 as 'Computer',
ARP.DisplayName0 as 'ARP Display Name'
FROM
dbo.v_R_System as R,
dbo.v_Add_Remove_Programs AS ARP
ORDER BY
R.Netbios_Name0 DESC,
ARP.DisplayName0 DESC
Writing an Efficient Query
Modify the query from Listing 4.22 to join both views. Use the appropriate join to get all data from the v_R_System view as well as matching data (ResourceID) from the v_Add_Remove_Programs view.
Working with the Different JOIN Statements
To become familiar with the different joins, read through this chapter again and execute the queries provided as examples for the INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL JOIN statements. Take note of the variations and locations of the NULL results for each type of join. Are there any NULL results in an INNER JOIN?
Summary
The foundation of ConfigMgr reporting is writing good and efficient SQL queries. This chapter has reviewed many aspects of SQL queries to help you not only write but understand the underlying SQL language of ConfigMgr reports. Most queries you create will incorporate many of the topics in this chapter.
Many demonstrations are provided throughout the chapter. Although this might seem like repetition or recaps, run these queries in your lab environment and take note of the results, variations, and similarities. As you become familiar and comfortable with writing SQL queries, these queries will become second nature; but until then, be sure to take your time and understand each section of this chapter.
The next chapters of this book focus on the design, concepts, and features of ConfigMgr reports rather than the underlying SQL queries themselves.