Creating SMS Web Reports from SMS Queries - Part IV
SQL Views
In Part 3, we dismantled our SQL query and explained what it is doing and the logic behind it. In this article, we will be covering SQL views.
What are SQL Views?
SQL Views are predefined SQL queries that are run against one or more tables to present data to the user without actually giving them direct access to the SQL database. Views are kind of like a sneeze guard at a buffet line. Just as that quarter-inch piece of plastic protects the food from people’s forcefully expelled germs, SQL views prevent your users from contaminating your SMS database.
To use a view in a SQL query, you would treat it just as if it was a SQL table name. SMS automatically creates SQL views for the tables that are in the SMS database, so you should always be able to find the view you need to access any of the tables. Let’s open up Query Analyzer and look at a SQL view by expanding Views in the Object Browser. Scroll down to dbo.v_R_System, expand it, and then expand Columns. Your Object Browser should now look like this:
You may have noticed that after dbo., most views start with a letter ‘v’ followed by an underscore, which makes it easier to denote them as views. Now we will examine the SQL statement that defines the view. Right-click on dbo.v_R_System and select Edit from the menu. The SQL query for the view will open in a new Query Analyzer window, and that query looks like this:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER VIEW dbo.v_R_System AS SELECT ItemKey As 'ResourceID', DiscArchKey As 'ResourceType',Active0,AD_Site_Name0,Client0,Client_Type0,Client_Version0,CPUType0,Creation_Date0,Decommissioned0,Hardware_ID0,User_Domain0,User_Name0,Name0,Netbios_Name0,Obsolete0,Operating_System_Name_and0,Previous_SMS_UUID0,Resource_Domain_OR_Workgr0,SMS_Unique_Identifier0,SMS_UUID_Change_Date0,Community_Name0 FROM System_DISC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
There are a lot of new SQL keywords that you hadn’t seen before, but for the purpose of this article you don’t need to be concerned with what they do. For now, just look at the ALTER VIEW statement, where you can see that it is querying FROM System_DISC. We will look at that table next and compare its columns to the ones in the v_R_System view. Just to keep things easier to read, I will be leaving the dbo. off of the beginning of the view names throughout the rest of these articles. Now, close the child window and select to not save any changes.
Next, scroll up to User Tables, and then expand SystemDisc and Columns. You will see that most of the same columns exist in both the table and its related view. Here’s a look at both of them side by side so you can see the similarities. Query Analyzer doesn’t list them in alphabetical order, but you can see how the v_R_System view is comprised of most of the columns in the System_DISC table.
In most cases, you will be able to figure out which view in your SMS database corresponds to which table just by browsing through the list of names as they are named similarly. Here are a few examples:
|
Table Name |
View Name |
|
System_DISC |
v_R_System |
|
ADD_REMOVE_PROGRAMS_DATA |
v_GS_ADD_REMOVE_PROGRAMS |
|
CD_ROM_DATA |
v_GS_CDROM |
|
OPERATING_SYSTEM_DATA |
v_GS_OPERATING_SYSTEM |
|
System_IP_Subnets_ARR |
v_RA_System_IPSubnets |
|
SoftwareFile |
v_SoftwareFile |
As you can see, it’s pretty straightforward. The table names usually end with _DATA and the aliases usually start with v_GS_, which is information that is gathered during a Hardware Inventory cycle. One thing that you do need to know is that the views that start with v_HS_ are referencing tables containing SMS history data. So unless you are writing an SMS Web Report to use historical information you will generally want to avoid using the v_HS views.
At this point, you should have enough of an idea of what the SQL statement is doing and how views are used, so we can now move on to Part 5 where we will start converting our SQL query to use SQL views.