In part one of this article series, we saw how to convert an SMS query to a SQL query, but when we tried to take the same SQL statement and make it a web report it returned a ‘permissions denied’ error. Part two will provide an overview on how to use Query Analyzer, what SQL tables are, and how to run a SQL query.
**DISCLAIMER: The exercises in this article discuss how to read data from a SQL database using SELECT statements. As long as you stay within the confines of what is being presented, you do not run any risk of damaging or altering your SQL data. Unless you are aware of what you are doing or are instructed by someone who knows SQL, you should never alter the data in your SMS database either directly or through INSERT or UPDATE statements. That being said, please don’t blame me if you crush your database by deviating from the exercises and venturing off on your own!
Editing the SQL Query using Query Analyzer
Go to the Microsoft SQL Server program group and open the SQL 2000 Query Analyzer which allows you to run SQL queries against your SMS database. I'm using the older version of SQL for this article since not everyone is using SQL 2005 so the SQL 2000 Query Analyzer is a better common ground. (If you’re using SQL 2005 then you would use the Query Editor instead of Query Analyzer.) Now type in your SQL server name where the SMS database resides and select either Windows or SQL Server authentication depending on which option you chose when you installed SMS. If you use SQL Server authentication you will need to provide a username and password for a SQL account that has access to the SMS database. After clicking on OK, you need to select your SMS database from the drop down menu on the toolbar at the top of the screen. If you don’t do this then you will get an error stating, “Invalid object name” when the query tries to run. Now cut and paste the SQL query that we pulled from the smsprov.log file in Part 1 of this article series into the Query Analyzer window. For convenience, here’s the SQL query:
select all SMS_R_System.ItemKey,SMS_R_System.Name0,SMS_R_System.User_Name0,__System_ADD_REMOVE_PROGRAMS0.DisplayName00 from System_DISC AS SMS_R_System INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey where __System_ADD_REMOVE_PROGRAMS0.DisplayName00 = "Windows Internet Explorer 7"
Your window should now look something like the screen shot below, but please note that I have removed my server name from the Query Analyzer window due to company policy. If you’re not seeing the object browser pane on the left side, then hit the F8 key to display it. For readability, I’ve added some line breaks into the query, which Query Analyzer will ignore when the query runs.
What are SQL Tables?
Before we proceed in modifying this query, we need to understand what it is doing. One of the first things you will need to know if you don’t already is that SQL stores its data in tables. Let’s use the Object Browser window on the left side to look at some tables in the SMS database. Expand your SMS database name, and then expand User Tables. If you scroll down, you will see a list of all the tables in your SMS database.
Now we’re going to choose a table and display the data that resides in it. Scroll down to the table named, “dbo.CD_ROM_DATA” and expand it, then expand Columns. This shows all the different CD ROM information that SMS collects on a client system during a Hardware Inventory cycle as defined in your sms_def.mof file.
Next, we’ll look at the data that resides in this table. Right click on dbo.CD_ROM_DATA. From the pop-up menu that appears, choose “Script object to new window as” then “Select” which will open a new window in Query Analyzer with a SQL query to return all the rows in the table. To run the query, you can either choose Execute from the Query menu in Query Analyzer or click on the green triangle in the toolbar. When the query runs, it will display all the CD ROM data that SMS has collected from your clients in the results pane on the bottom. If you scroll over to the right, you can see all the different columns.
When you’re finished viewing the results, close the child window in SQL Analyzer by clicking on the gray X in the upper right hand corner to go back to our original SQL query for Internet Explorer 7. We will continue with this in Part 3 of this article, where we will look at the SQL query and decipher what each line in the query is doing.