SCCM Collection Queries By Server Role
In this post I will be sharing the queries which can be used for SCCM collection query for Site System Server Roles. We can create different queries for specific system role installed on Windows Server.
SCCM Collection Queries by Server Role
For big infrastructures, there could be heaps of Site System Server installed, hence to look for list of all Servers with specific role is an important factor to consider. Below mentioned queries and the tip shared in the last will be helpful to see those lists.
Launch Configuration Manager Admin Console, navigate to \Administration\Overview\Site Configuration\Servers and Site System Roles, right click Servers and Site System Roles and select any of the option to display list of servers with specific Site System Server role installed:
To be proactive with operational maintenance, once a week you run CMPivot against a collection of servers that you manage, and select Query all on the AppCrash entity. You right-click the FileName column and select Sort Ascending. One device returns seven results for sqlsqm.exe with a timestamp about 03:00 every day. You select the file name in one of the rows, right-click it, and select Bing It. Browsing the search results in the web browser, you find a Microsoft support article for this issue with more information and resolution.
You need to temporarily store a large file on a network file server, but aren't sure which one has enough capacity. Start CMPivot against a collection of file servers, and query the Disk entity. Modify the query for CMPivot to quickly return a list of active servers with real-time storage data:
CMPivot sends queries to clients using the Configuration Manager "fast channel". This communication channel from server to client is also used by other features such as client notification actions, client status, and Endpoint Protection. Clients return results via the similarly quick state message system. State messages are temporarily stored in the database. For more information about the ports used for client notification, see the Ports article.
This report will provide you with all the SCCM servers, and the site system roles enabled for all those remote site systems severs. Note that this is the reposting of the previous post. We have a lot of SQL queries available for the community, and some of them are listed below.
You can navigate to \Administration\Overview\Site Configuration\Servers and Site System Roles and select the site server or site system server to find out the roles enabled for that particular site system.
Recently, I got an email asking how to find all the DHCP servers and create a SCCM collection for it. The goal here is to find out all the DHCP servers existing in Active Directory setup and group them into a device collection.
A DHCP Server is a network server that automatically provides and assigns IP addresses, default gateways and other network parameters to client devices. It relies on the standard protocol known as Dynamic Host Configuration Protocol or DHCP to respond to broadcast queries by clients.
The procedure to create DHCP server collection in SCCM is very similar to creating device collection for Windows Server 2022. The only difference is the SCCM WQL query for DHCP server is unique here. Device Collections are nothing but a group of devices or users. Configuration Manager contains several built-in collections but in some cases you may need to create custom collections based on the requirements.
On the Query rule properties box, specify the name of the query and click Edit Query Statement. In the Query Language box enter the following query to create SCCM device collection for DHCP servers.
Wait for few minutes while the query runs in the background and finds all the DHCP servers in your network. To speed up things, right-click the DHCP server device collection and click Update Membership.
In this article, I will show you how to create SCCM device collections for Windows Server. We will use WQL queries to create the device collections for all the latest versions of Windows Servers, which include 2022, 2019, and 2016.
When you create device collections for Windows Server in ConfigMgr, the WQL query uses the build number to identify the server version. Hence, you must use the correct build number in the SCCM query to create collections for Windows Server. See also Windows Server Release info.
For all Workgroup Windows Servers and workstation I used this queries below, the key thing is I queried the variable found in the resource Explorer Domain Role and Operating system Name & version %server% or %Workstation%.
I have used SMS_G_System_COMPUTER_SYSTEM.DomainRole. The attribute class is Computer System and the attribute is Domain Role for Workgroup devices are Standalone Server but I also used SMS_R_System.OperatingSystemNameandVersion like to isolate the collection type with a %server% or %workstation% wildcard.
Excellent list, would like to have a query that combines user device affinity using display name, make and model of the hardware, and includes the last policy cycle of the device. I can find the separately but, unable to combine the queries to run in sccm query.
I will just be doing a basic query to check for a specific service. Highlight the entities you want to query and select insert. If you run the queries with just service it will return all services from every device in the collection.
One of the fundamental things you need to do with any SCCM installation and deployment, you need to get the basics right. Queries are those basic building blocks which everything else in SCCM is based on. Once you have all your custom queries setup, then you can setup collections based on the queries, once this is done, all other SCCM components is relatively strait forward.
Security Roles allows you to delegate permissions to various part of SCCM. So you can create role, that will grant just read permissions over devices in some particular collection, read Bitlocker Recovery keys etc.
There are a couple of really simple tweaks we can make to help reduce our overall collection query evaluation times. (NOTE: Making changes to existing collections or collection queries will immediately cause that collection to update its membership)
To recap, use CEViewer to keep an eye on your Collection Evaluations. In addition, when creating your collection queries make sure to use SELECT DISTINCT and split out your query rules to improve performance where possible.
Before we can create a collection based on a CMPivot query we need to create a CMPivot query useful and actually does something for us so lets start with something practical something like find me all servers where a specific service is in a stopped state it might look something like this.
Hi Cora, it depends on what the purpose of your collection is. All Systems is fine, unless you're looking to ensure that they're in a collection you've already setup, such as all laptops, all servers, etc.
Grants permissions to perform both the Application Deployment Manager role and the Application Author role. Administrative users who are associated with this role can also manage queries, view site settings, manage collections, and edit settings for user device affinity.
Grants permissions to deploy applications. Administrative users who are associated with this role can view a list of applications, and they can manage deployments for applications, alerts, templates and packages, and programs. Administrative users who are associated with this role can also view collections and their members, status messages, queries, and conditional delivery rules.
Grants permissions to define and monitor Compliance Settings. Administrative users associated with this role can create, modify, and delete configuration items and baselines. They can also deploy configuration baselines to collections, and initiate compliance evaluation, and initiate remediation for non-compliant computers.
Grants permissions to define and monitor security policies. Administrative Users who are associated with this role can create, modify and delete Endpoint Protection policies. They can also deploy Endpoint Protection policies to collections, create and modify Alerts and monitor Endpoint Protection status.
Grants all permissions in Configuration Manager. The administrative user who first creates a new Configuration Manager installation is associated with this security role, all scopes, and all collections.
Grants permissions to add and remove administrative users and to associate administrative users with security roles, collections, and security scopes. Administrative users who are associated with this role can also create, modify, and delete security roles and their assigned security scopes and collections.
Having a Change Log is a good thing. A quick and simple place to find out what has changed on a server and when. This can be invaluable when troubleshooting, matching a change to a symptom especially when assessed alongside your performance counter collection. Here is a simple way to make use of a change log and automate it
The role of the Patching & Monitoring Specialist provides Lallemand with technical assistance pertaining to the performance, operation, and maintenance of Lallemand servers and services on a 24 hour basis by being responsible for monitoring, diagnosing and analyzing incoming alarms following established protocols for troubleshooting issues, escalating unresolved issues and initiating service dispatch In Addition, we require the candidate to provide Windows Operating Support for troubleshooting and reporting on Monthly and Zero Day Patch Vulnerabilities.