Windows OS Hub
  • Windows Server
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Group Policies
  • Windows Clients
    • Windows 10
    • Windows 8
    • Windows 7
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
  • PowerShell
  • Exchange
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Group Policies
  • Windows Clients
    • Windows 10
    • Windows 8
    • Windows 7
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
  • PowerShell
  • Exchange

 Windows OS Hub / SCCM / Converting SCCM WQL Query to SQL

April 1, 2019 SCCM

Converting SCCM WQL Query to SQL

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.

sccm : wql query builder

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.

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.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

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 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.

sccm db table permissions for the 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.

1 comment
0
Facebook Twitter Google + Pinterest
previous post
PowerShell: Get Folder Sizes on Disk in Windows
next post
How to Remove Installed Updates in Windows 10 and Windows Server?

Related Reading

Upgrading Windows 10 Build with Setup.exe Command-Line Switches

May 28, 2020

How to Automatically Uninstall All Previous Office Versions...

June 28, 2018

Windows Update Error 0x80244022 and WsusPool Memory Limit

December 8, 2017

Configuring Remote Control in SCCM 2012

May 6, 2016

SCCM and WMI Query to Find All Laptops...

March 24, 2016

1 comment

Fahim February 24, 2020 - 8:06 pm

Thanks a lot, I’m sure there are other ways of converting WQL to SQL but I was able to find what I needed using your article.

Reply

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange
  • Windows 10
  • Windows 8
  • Windows 7
  • Windows Server 2016
  • Windows Server 2012 R2
  • Windows Server 2008 R2
  • PowerShell
  • VMWare
  • MS Office

Recent Posts

  • How to Troubleshoot, Repair and Rebuild the WMI Repository?

    March 2, 2021
  • Accessing USB Flash Drive from VMWare ESXi

    February 26, 2021
  • How to Sign a PowerShell Script (PS1) with a Code Signing Certificate?

    February 25, 2021
  • Change the Default Port Number (TCP/1433) for a MS SQL Server Instance

    February 24, 2021
  • How to Shadow (Remote Control) a User’s RDP session on RDS Windows Server 2016/2019?

    February 22, 2021
  • Configuring PowerShell Script Execution Policy

    February 18, 2021
  • Configuring Proxy Settings on Windows Using Group Policy Preferences

    February 17, 2021
  • Updating Group Policy Settings on Windows Domain Computers

    February 16, 2021
  • Managing Administrative Shares (Admin$, IPC$, C$, D$) in Windows 10

    February 11, 2021
  • Packet Monitor (PktMon) – Built-in Packet Sniffer in Windows 10

    February 10, 2021

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • How to Automatically Uninstall All Previous Office Versions using OffScrub Script
  • Upgrading Windows 10 Build with Setup.exe Command-Line Switches
Footer Logo

@2014 - 2018 - Windows OS Hub. All about operating systems for sysadmins


Back To Top