To select various data from computers, users or devices in System Center Configuration Manager database and to make collections, you have to create various SCCM queries. In the SCCM query editor, there is quite a convenient wizard to navigate you through System Center classes and attributes. To show the necessary information to the users, I prefer making html SCCM reports. The problem is that if you would like to make an HTML report based on a SCCM query, you will discover that the SCCM query code cannot be used to create a report.
The matter is that ConfigMgr queries are written in the WQL language, but SCCM reports require to use SQL queries to the Microsoft SQL Server database. Though WQL and SQL syntax have much in common, you cannot directly convert a WQL query into SQL. In this article I’ll show a simple and fast way to get an SQL query code from any WQL query in the SCCM.
I needed to create a web report for users that contained the list of computers not turned off for the night. I made the following WQL query:
select distinct SMS_R_System.NetbiosName, SMS_R_System.LastLogonUserName, SMS_R_System.IPAddresses, SMS_R_System.ADSiteName, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, SMS_G_System_COMPUTER_SYSTEM.Model from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where DATEPART(DY, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime) != DATEPART(DY, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan)
As you can see, I consider that a user computer has not been turned off at night if the date of the last scan (LastHardwareScan) is not equal to the OS boot date (LastBootUpTime).
Now you must make an SQL query from this one to create a report with the list of computers and users. It is quite complicated to convert this query into SQL. (Of course, you can use SQL Server Report Builder to construct the query code, but there is an easier way to do it.) The fact is that to execute queries the ConfigMgr engine translates WQL queries into t-SQL syntax using WMI provider and executes an SQL query against SCCM database. All these operations are recorded to the smsprov.log file.
So run your query and open the file \ConfigMgr\Logs\smsprov.log. Find your query in the log file. It should start with Execute WQL =. Pay attention to the string below starting from Execute SQL =. This is my WQL query converted to t-SQL. Copy the code of the SQL query. You can use it to build a SCCM web report or in the Report Builder.
Distinct SMS_R_System.Netbios_Name0,SMS_R_System.User_Name0,SMS_R_System.AD_Site_Name0,___System_WORKSTATION_STATUS0.LastHWScan,SMS_G_System_OPERATING_SYSTEM.LastBootUpTime0,SMS_G_System_COMPUTER_SYSTEM.Model0 from System_DISC AS SMS_R_System INNER JOIN WorkstationStatus_DATA AS ___System_WORKSTATION_STATUS0 ON ___System_WORKSTATION_STATUS0.MachineID = SMS_R_System.ItemKey INNER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey INNER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey INNER JOIN _RES_COLL_CM100213 AS SMS_CM_RES_COLL_CM100213 ON SMS_CM_RES_COLL_CM100213 .MachineID = SMS_R_System.ItemKey where DATEPART (dayofyear,SMS_G_System_OPERATING_SYSTEM.LastBootUpTime0) <> DATEPART (dayofyear,___System_WORKSTATION_STATUS0.LastHWScan)
Since the original WQL query has been restricted to the SCCM collection, the SQL query also has the restriction on the collection ID (in this example it is CM100213).
When generating an SCCM web report, the following error may appear:
An error occurred when the report was run. The details are as follows:
The SELECT permission was denied on the object '_RES_COLL_CM100213', database 'SMS_CM1', schema 'dbo'.
Error Number: -2147217911
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 229
The error indicates that the current user has not been assigned the db_datareader role to access the database table. To fix this error, you must manually grant the access to the table or to the view in your SCCM database. To do it, open the SQL Server Management Studio, connect to the SCCM database, find the table (in this example it is _RES_COLL_CM100213) in the Tables or View sections and open its properties.
On the Permissions tab, grant the Select permissions for smsschm_user and webreport_approle.
Try to refresh the web report in the browser, and if another “The SELECT permission was denied” error appears in relation to other SCCM tables, assign the access permissions to these tables in the same way.