Converting SCCM WQL Query to SQL

To choose numerous information from computer systems, customers or units in System Center Configuration Manager database and to make collections, you may have to create numerous SCCM queries. In the SCCM question editor, there may be fairly a handy wizard to navigate you thru System Center lessons and attributes. To present the mandatory data to the customers, I desire making html SCCM stories. The downside is that if you want to make an HTML report based mostly on a SCCM question, you’ll uncover that the SCCM question code can’t be used to create a report.

The matter is that ConfigMgr queries are written within the WQL language, however SCCM stories require to use SQL queries to the Microsoft SQL Server database. Though WQL and SQL syntax have a lot in frequent, you can not straight convert a WQL question into SQL. In this text I’ll present a easy and quick manner to get an SQL question code from any WQL question within the SCCM.

sccm : wql query builder

I wanted to create an online report for customers that contained the listing of computer systems not turned off for the night time. I made the next WQL question:

choose 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 interior be part of SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId interior be part of SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId interior be part of SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId the place DATEPART(DY, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime) != DATEPART(DY, SMS_G_System_WORKSTATION_STATUS.LastHardwareScan)

As you may see, I think about consumer laptop has not been turned off at night time if the date of the final scan (LastHardwareScan) will not be equal to the OS boot date (LastBootUpTime).

Now you could make an SQL question from this one to create a report with the listing of computer systems and customers. It is sort of difficult to convert this question into SQL. (Of course, you need to use SQL Server Report Builder to assemble the question code, however there may be a better manner to do it.) The reality is that to execute queries the ConfigMgr engine interprets WQL queries into t-SQL syntax utilizing WMI supplier and executes an SQL question in opposition to SCCM database. All these operations are recorded to the smsprov.log file.

So run your question and open the file ConfigMgrLogssmsprov.log. Find your question within the log file. It ought to begin with Execute WQL =. Pay consideration to the string beneath ranging from Execute SQL =. This is my WQL question transformed to t-SQL. Copy the code of the SQL question. You can use it to construct a SCCM net report or within the Report Builder.

smsprov.log - execute WQL and SQL code

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.Modelzero 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 the place DATEPART (dayofyear,SMS_G_System_OPERATING_SYSTEM.LastBootUpTime0) <> DATEPART (dayofyear,___System_WORKSTATION_STATUS0.LastHWScan)

Since the unique WQL question has been restricted to the SCCM assortment, the SQL question additionally has the restriction on the gathering ID (on this instance it’s CM100213).

When producing an SCCM net report, the next error could seem:

An error occurred when the report was run. The particulars are as follows:
The SELECT permission was denied on the thing '_RES_COLL_CM100213', database 'SMS_CM1', schema 'dbo'.
Error Number: -2147217911
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 229

An error occurred when the report was run. The details are as follows: The SELECT permission was denied on the object Error Number: -2147217911 Source: Microsoft OLE DB Provider for SQL Server Native Error: 229

The error signifies that the present consumer has not been assigned the db_datareader function to entry the database desk. To repair this error, you could manually grant the entry to the desk or to the view in your SCCM database. To do it, open the SQL Server Management Studio, join to the SCCM database, discover the desk (on this instance it’s _RES_COLL_CM100213) within the Tables or View sections and open its properties.

On the Permissions tab, grant the Select permissions for smsschm_user and webreport_approle.

sccm db table permissions for the smsschm_user and webreport_approle.

Try to refresh the online report within the browser, and if one other “The SELECT permission was denied” error seems in relation to different SCCM tables, assign the entry permissions to these tables in the identical manner.

Check Also

How to Restore Deleted EFI System Partition in Windows 10?

In this text we’ll present you ways to manually restore an by chance deleted Windows …

Leave a Reply

Your email address will not be published. Required fields are marked *