Creating SMS Web Reports from SMS Queries - Part I
From SMS Query to SQL Query
In the past, there have been some articles written on myITforum.com and also some forum posts describing how to use the smsprov.log file to convert SMS queries to SQL queries. This is a fairly simple procedure, but if you try to take it a step further to create a web report using this method, the query causes a 'SELECT permission denied' error when the report is run. In this series of articles, I am going to explain how to use SQL views from the SMS database in a SQL query to avoid this error, speed up the query process as well, and teach you some basic Transact-SQL.
Converting from an SMS Query to a SQL Query
The first thing we will look at is how to take an SMS query and convert it to a SQL query which will later become our web report. We'll start with a fairly simple SMS query to return the SMS Site Code, System Name, Last User Logon Name, and Add/Remove Program Display Name for any systems that have Internet Explorer 7 installed. Here's the WQL language so you can just paste it into an SMS query:
select SMS_R_System.SMSAssignedSites, SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Windows Internet Explorer 7"
Run the query in SMS and view the results. Now open up the SMS SQL provider log file named smsprov.log which resides in the \SMS\Logs directory on the site server and scroll to the bottom of the log. You will see a line that starts with "Execute WQL=" followed by the SMS query that you pasted in. Immediately below that, there is a line that begins with "Execute SQL=" followed by the SQL query that the SMS SQL Provider converted from WQL. Highlight the SQL query from "select all" to the end and copy it. The SQL query looks like this:
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"
From a SQL Query to an SMS Web Report that doesn’t work
Now we’re going to create an SMS Web Report from this SQL query so you can see what the ‘SELECT permission denied’ error looks like. In the SMS Administrator’s Console, expand the Reporting node, and then right-click on Reports. From the pop-up menu, select New -> Report and title it, “All Systems with Microsoft Internet Explorer 7” and then click on the Edit SQL Statement button. Delete the default query text in the SQL Statement window, paste in our SQL query, and then click on OK. The report properties should look like this:
Click on OK to save the report. To run the report, right-click on it in the SMS Administrator Console, and then from the pop-up menu select All Tasks -> Run -> YourSMSReportingServerName. When you run the report, you should see an error message like this: 
So why is it that we can run this query in SMS which passes it to SQL flawlessly, yet when we take the same SQL query and dump it into a web report we get this error? The answer is that the SQL query references SQL tables, and the SMS SQL provider lets you access those tables through the SMS Administrator’s console. When you try to run the web report, the SQL provider is out of the picture, and in the context of permissions that you are running the web report under you don’t have permissions to the SQL tables – and that’s a good thing! You wouldn’t want your users who have access to your web reports to have direct rights to the data in your SQL tables.
The solution to this is to use SQL views in your web report, which your users will have access to and it will also speed up your querying process. In part 2 of this article, we will go though how to convert your SQL query to use SMS views.