SCCM SQL & WQL Query Library — Categorised Index

This is a working index of the SCCM/Configuration Manager SQL and WQL queries published across the site, organised by what they’re actually used for so you can find the right one without hunting through the archive. Every query here has a full walkthrough on its own page – click through for the complete query text, where to run it, and any prerequisites (permissions, which view/table it hits, etc.).

How to Use This Library

SQL queries are written against the ConfigMgr site database (CM_<SiteCode>) and are meant to be run in SQL Server Management Studio (SSMS), or dropped into an SSRS report if you want them refreshing automatically
WQL queries are written for the ConfigMgr console itself – paste them into a Collection’s Membership Rules > Query Rule editor, or use them for a query-based Collection you want to keep live and self-updating
Always run a new query against a test collection or with TOP 100 / a WHERE filter first – some of these touch tables with millions of rows on a large hierarchy and can be slow if run unfiltered
Use a read-only SQL account for reporting queries where one is available – you don’t need write access to the CM database to pull inventory data
This page is a living index – it gets new entries as new queries are published

SQL vs. WQL – Which One Do I Need?

Short version: if you want a report (a list, a count, an export to CSV), you want SQL, run against the site database in SSMS. If you want a live Collection that ConfigMgr keeps updated on its own membership evaluation schedule – so you can target a deployment at it – you want WQL, entered directly into the Collection’s query rule. A few of the queries below exist in both flavours because the underlying question (“which machines have X installed?”) is useful both ways.

Windows OS & Discovery Queries

Software & Application Inventory Queries

Client Health & Compliance Queries

Hardware & Asset Reporting Queries

Site, Role & Database Queries

ConfigMgr SQL Query – ConfigMgr Clients Installed Per AD Site
SCCM SQL Query: Pulling Hierarchy Information from the Database
ConfigMgr SQL Query: Retrieving Site Roles from the Database
SQL Query Active Directory: Finding Security Group Members – not a ConfigMgr query, but included here since it’s the same SSMS/T-SQL skill set applied to an AD-integrated database

WQL Collection Queries (Device Targeting)

These are written specifically to drop into a Collection’s query rule so the membership stays live and self-updating – useful when you want a deployment to automatically pick up new matching devices.

One More Worth Bookmarking

If you want a single roundup to skim rather than browsing by category, 30 Useful SCCM SQL Queries covers a broad mix in one place – several overlap with the categorised list above, but it’s a handy quick-reference on its own.

Have a query you’d like to see covered, or found one of these needs updating for a newer ConfigMgr build? Let us know via the contact page and we’ll look at adding or refreshing it.

Discover more from TechyGeeksHome

Subscribe to get the latest posts sent to your email.

Andrew Armstrong

Andrew Armstrong is a UK-based IT professional with 26+ years of hands-on experience in Windows, Windows Server, SCCM/ConfigMgr, Active Directory, PowerShell, and enterprise infrastructure.

He founded TechyGeeksHome in 2010 and has published over 1,500 practical guides covering real-world IT problems and solutions. When not solving IT problems,

Andrew develops free Windows utilities including Ultimate Settings Panel, which has been downloaded over 850,000 times.