Thursday, December 27, 2012

Report Building Part 4: The SQL Query soo far

So now that we've gone through the absolute basics on building a SQL Query (I left a lot out on purpose, I don't have the patience to write THAT much about creating SQL Queries), lets circle around and get the Query in order.

Original Query from the Interwebs:

SELECT Manufacturer0, Model0, Count(Model0) AS 'Count'
FROM dbo.v_GS_COMPUTER_SYSTEM
GROUP BY Manufacturer0,Model0
ORDER BY Model0


So first thing we will need to do is figure out how to limit this by collection.  As mentioned at the beginning of this series, you will need to familiarize yourself with the SCCM 2007 Database Schema/Diagram, linked in the first article.  In this case, we need to figure out how to get from v_GS_COMPUTER_SYSTEM too v_Collection.  In the diagram the two do not share any common tables, however v_FullCollectionMembership DOES have tables in common with both: ResourceID and CollectionID.  Knowing this, we can create some INNER JOINs to link them all together.  The Query now looks like this:

SELECT Manufacturer0, Model0, COUNT(Model0) AS 'Count'
FROM v_GS_COMPUTER_SYSTEM
INNER JOIN v_FullCollectionMembership ON v_GS_COMPUTER_SYSTEM.ResourceID = v_FullCollectionMembership.ResourceID
INNER JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
GROUP BY Manufacturer, Model0
ORDER BY Model0

This only gets us part of the way there, however, as we haven't told SQL what collections we need to query from.  Nor do we have a mechanism to select Collections at will.  This is going to take one more feature of creating SQL Queries: Parameters!

Sunday, December 2, 2012

Report Building Part 3: Lets all JOIN hands

This next segment is dedicated to JOINs and how they are basically the backbone of querying the SCCM 2012 Database.  If you haven't already done so please download and review the SCCM 2007 Schema located here.

As mentioned before, the SCCM database is pretty big.  When I first started out with SCCM 2007 I didn't have a full appreciation for how much information is actually stored in this database.  Consider that it collects information on all the installed applications, hardware, collection memberships, deployment packages, Application Packages, OS Deployment, PXE, etc ... that is a lot of data to store.

So, lets get right to it.

JOIN

A JOIN allows you to combine information from two or more related tables.  A JOIN can be one-to-one, one-to-many, or many-to-many.  As you add JOINs to your query you increase the amount of information that the query will return.  There are four main types of JOINs: INNER, OUTER, LEFT, and RIGHT.  You can also do a self-join but that is out of the scope of this series.  In the book Microsoft SQL Server 2008 Reporting Services (ISBN-13:978-0071548083) , the Author describes JOINs best by suggesting that you consider two overlapping circles, each representing a database table.  The part in the center where they overlap (data that is the same in both tables) is an INNER JOIN.  The data outside the center portion on the left is an LEFT OUTER JOIN and the data outside the center portion on the right is a RIGHT OUTER JOIN. 

INNER JOIN

An INNER JOIN is probably the most common type of JOIN that you will use with basic SQL Queries.  Basically an INNER JOIN compares the selected fields from two tables and merges the results into your Report.  NULL values (blank fields) will not be included in an INNER JOIN unless you specify a join condition like IS NULL or IS NOT NULL.  With basic SCCM Queries you will not have to worry about NULL values I think.  Here are a few different ways you can do INNER JOINs:

1: Explicit JOIN notation
SELECT *
FROM dbo.employee
INNER JOIN dbo.department ON dbo.employee.DepartmentID = dbo.department.DepartmentID;

2: Implicit JOIN notation
SELECT *
FROM dbo.employee, dbo.department
WHERE dbo.employee.DepartmentID = dbo.department.DepartmentID;

OUTER JOIN

For more advanced SQL Queries you will see mostly LEFT OUTER or RIGHT OUTER joins.  The LEFT and RIGHT refer to which side of the = sign takes preference.  In a LEFT OUTER join for instance, all the records from the left side of the = sign will be in the report even if there is NULL values on the right.  For Example:

SELECT *
FROM dbo.employee
LEFT OUTER JOIN dbo.department ON dbo.employee.DepartmentID = dbo.department.DepartmentID;

This will list all employees regardless of if they have been assigned to a department.  The reverse of that, a RIGHT OUTER JOIN, would list all departments regardless of what employees are assigned.

Hopefully this will give you more of an understanding on JOINs and how they affect your SQL Query.  Next we will dive into Parameters, the difference between Dataset and Report Parameters and when to use them ... just as soon as I learn how to use them!

Stay Tuned!

Table of Contents

Introduction to Series
Part 1: The Goal
Part 2: SQL Query Basics
Part 3: Lets all JOIN hands

Saturday, December 1, 2012

Report Building Part 2: The Basics of a SQL Query

Since I'm bored in my hotel room I thought that I would get this blog series off the ground by covering the basics of a SQL query.  To keep the information relevant I will use the query that I included in the last post as a template:

SELECT Manufacturer0, Model0, Count(Model0) AS 'Count'
FROM dbo.v_GS_COMPUTER_SYSTEM
GROUP BY Manufacturer0,Model0
ORDER BY Model0

SELECT

A SQL query always starts with SELECT, commonly referred too as a SELECT Statement.  There are many commands that you can use in SQL, but SELECT is what you need to start with when you want to retrieve data from your Database.  The other commands allow you to add and remove content to your DB so is not part of the scope of this series.

After SELECT you provide the details of which tables you wish to obtain information from.  You can use a * wildcard here if you wish to select all tables but its best to specify tables so you can keep the query results manageable.  In the example above, I'm selecting the tables called Manufacturer0, and Model0.

Another part of the SELECT statement is the Count function.  Basically, this function will count the number of entries in a column and provide a total.  To explain that based on the Query: Count the number of Models in the Model0 column and place the total in a column called Count.

FROM

The next part of a SQL query is choosing where the tables are located.  In this case the tables are located in dbo.v_GS_COMPUTER_SYSTEM.  This is where you need to know the layout, or schema, of the database you are trying to query.  Microsoft released the schema for SCCM 2007 some time ago, I've linked to it on the previous post.  I haven't seen a database schema for SCCM 2012 yet so I'm assuming that it is the same.

GROUP BY

This instructs SQL to display the query results in like-groups rather than individual rows.  As specified above, it will first group via Manufacturer, then by Model. Additional entries for each Manufacturer will be created as Models are discovered, and vice versa.

ORDER BY

This tells SQL how to order the data in the report, otherwise there will be no order to the data output.  Depending on the complexity of the query, this could make the results unusable.

To give you an idea of what the output from this query looks like, here is what my results look like:




For all you hard core SQL designers and report builders, you will notice that I have left out A LOT in this first post.  Missing are some very important syntax items such as JOIN, INNER JOIN, OUTER JOIN, and many others.  I hope to cover these in future posts as we add to this query to include the additional data required as well as how to incorporate Parameters into the query so we can add the ability to choose Collections to limit our results.

Please let me know in the comments if I made any errors, if I'm not clear in my description, or to provide any other feed back!


Table of Contents

Introduction to Series
Part 1: The Goal
Part 2: SQL Query Basics
Part 3: Lets all JOIN hands

Report Building Part 1: The Goal

Its always important to have a goal in mind when learning a new product or feature.  Sure there is the over arching goal of learning how to use a new tool but the important question and driver is WHY are you learning it?  At least this is the way that I learn anyway, I realize that everyone is different.

I find it easier to learn when I have to learn something in order to complete a specific task.  Here is what I've been given:

My company is getting ready to purchase a large number of desktops and laptops in order to finally move away from Windows XP SP3.  Internally we have developed a standard for what system models we want to maintain in the company and what we want to replace.  We also need to know what systems we can upgrade to Windows 7 vs. what we have to replace.  The reports that are built in to SCCM 2012 get us part of the way there but it doesn't give us a very specific piece of information:  How many of each system model are located in each office ?

I located a post on the TechNet Forums to help get me started with a basic SQL Query which I have to modify to be able to target specific collections.  Here is the base SQL Query that I'm going to start with:

SELECT     Manufacturer0, Model0, Count(Model0) AS 'Count'
FROM         dbo.v_GS_COMPUTER_SYSTEM
GROUP BY Manufacturer0,Model0
ORDER BY Model0


This query works great for discovering how many of each system model that we have in the entire company but it doesn't break it down by office.  In my case, Each office has its own Device Collection and User Collection.  Hopefully I can modify this to work for what I need to do while at the same time learning a new tool!  Please don't just post the answer to this if you know it, my goal is not just to figure this out but how to learn how to build SQL Queries with Report Builder.

This series assumes that you have already installed and configured SQL Server Reporting Services and installed the Reporting Services point role on your Primary/Central Site Server.  If you haven't, I've posted a link to an excellent thread on the windows-noob forum that walks you through how to set it up!

Links:

TechNet Discussion Thread:  http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/2541302e-f9bd-41ad-91cb-2f669a0df97c

Report Builder 3.0 Download: http://www.microsoft.com/en-us/download/details.aspx?id=6116

SCCM 2012 - Adding the Reporting Services Point Role: http://www.windows-noob.com/forums/index.php?/topic/4550-using-sccm-2012-rc-in-a-lab-part-11-adding-the-reporting-services-point-role/

Table of Contents

Introduction to Series
Part 1: The Goal
Part 2: SQL Query Basics
Part 3: Lets all JOIN hands

Introduction to Report Building with Report Builder 3.0 for SCCM 2012

Now that my 2012 environment is running smoothly; the kinks have been ironed out, the old 2007 environment decommissioned and old servers removed (enjoyed a glass of scotch to celebrate that moment), and IT staff are comfortable in the new console ... its time for me to change gears and dive into building reports in SCCM 2012 by using Report Builder 3.0.

This will spawn a new series while I literally teach myself how to build reports in SQL.  As someone who has almost no experience with SQL, other than basic installation and maintenance tasks, I hope this series of posts will be useful to anyone who needs to learn how to build reports in SCCM but don't have the slightest clue on how to do it.

Let me start by re-capping what my environment looks like so my posts will make sense:

SCCM Primary Site Server
Server OS: Windows 2008 R2
SQL: SQL Server 2008 R2 Standard w/ CU6, SSRS Installed and configured on same box
Report Builder 3.0 Installed

First post to come in a few days time I hope.  I'm spending the next 11 days in freezing cold Alberta rolling out an MS Lync-based phone systems in two offices so my hands are pretty full at the moment.

Table of Contents

Introduction to Series
Part 1: The Goal
Part 2: SQL Query Basics
Part 3: Lets all JOIN hands