Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 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
New-ADUser: Bulk Creating AD Users Using PowerShell
next post
How to Remove Installed Updates in Windows 10 and Windows Server?

Related Reading

How to Completely Uninstall Previous Versions of Office...

April 26, 2022

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

May 28, 2020

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 Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows 7
  • Windows Server 2019
  • Windows Server 2016
  • Windows Server 2012 R2
  • PowerShell
  • VMWare
  • Hyper-V
  • MS Office

Recent Posts

  • Create Organizational Units (OU) Structure in Active Directory with PowerShell

    May 17, 2022
  • Windows Security Won’t Open or Shows a Blank Screen on Windows 10/ 11

    May 17, 2022
  • How to Manually Install Windows Updates from CAB and MSU Files?

    May 16, 2022
  • RDS and RemoteApp Performance Issues on Windows Server 2019/2016

    May 16, 2022
  • Deploying Software (MSI Packages) Using Group Policy

    May 12, 2022
  • Updating VMware ESXi Host from the Command Line

    May 11, 2022
  • Enable or Disable MFA for Users in Azure/Microsoft 365

    April 27, 2022
  • Fix: You’ll Need a New App to Open This Windows Defender Link

    April 27, 2022
  • How to Reset an Active Directory User Password with PowerShell and ADUC?

    April 27, 2022
  • How to Completely Uninstall Previous Versions of Office with Removal Scripts?

    April 26, 2022

Follow us

woshub.com

ad

  • Facebook
  • Twitter
  • RSS
Popular Posts
  • 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