Tag: Query

Reporting on Office 365 Channels with SQL Query
System Center / Microsoft

Reporting on Office 365 Channels with SQL Query

by Andrew Armstrong - Leave a Comment

If you’re responsible for managing Office 365 deployments within your organization, having visibility into which channels your devices are using is crucial for effective administration and maintenance. With the help of SQL queries, you can extract pertinent information from Configuration Manager data, allowing you to generate insightful reports or visualize data using tools like Power BI. In this blog post, …

Reporting on Office 365 Channels with SQL Query Read More
System Center Configuration Manager SCCM
System Center / Guide

SCCM – SQL Queries for Make, Model and Architectures of Devices

by Andrew Armstrong - Leave a Comment

These simple SQL queries can be used to pull data from the Configuration Manager database to provide you with a count of all make, model and architecture devices in your business. Instructions To get this information, open a new query within SQL Server Management Studio and copy and paste the query that you wish to run from the options below: …

SCCM – SQL Queries for Make, Model and Architectures of Devices Read More
SCCM Collection Query for Devices with specific software installed and version or below
System Center / Guide

SCCM Collection Query for Devices with specific software installed and version or below

by Andrew Armstrong - 4 Comments.

If you need to identify machines with specific software installed on a device but would also like to know whether they have a certain version of the stated software or below, then you can use the below query. This can help with software upgrades to identify machines that have not yet been upgraded. SCCM Collection Query You can change the …

SCCM Collection Query for Devices with specific software installed and version or below Read More
sql2016
Guide / System Center

SQL Query to find members of an Active Directory Security Group

by Andrew Armstrong - 1 Comment

We recently helped out with a data migration which included creating a completely new set of Active Directory security groups. They wanted all the members to have the same level of access but wanted all the new security groups to have a matching standard. So we needed to create all the new security groups and then add all the existing …

SQL Query to find members of an Active Directory Security Group Read More
microsoft black logo
Guide / System Center

SCCM – Active Directory Security Group Query for User Collection

by Andrew Armstrong - Leave a Comment

If you are looking at setting up a SCCM user collection based on membership of an Active Directory Security Group, then you can use this WQL query for the collection. WQL Script To set this up, create a new collection and copy and paste this as its query: Variables You can add or remove any of the SMS_R_USER columns from …

SCCM – Active Directory Security Group Query for User Collection Read More
sccm logo
Guide / System Center

SCCM – Create a device collection based on Exchange Server role

by Andrew Armstrong - 2 Comments.

Following on from our recent posts for Configuration Manager collection queries, we have another one here for all devices that are Microsoft Exchange servers. Go ahead and create a new device collection and then use this query: select * from SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name like “Microsoft Exchange%” Save the query and you should now …

SCCM – Create a device collection based on Exchange Server role Read More
microsoft black logo
Guide / System Center

SCCM – Create a device collection based on Domain Controller role

by Andrew Armstrong - 1 Comment

We have another WQL query here for you today to enable you to create a Configuration Manager collection based on whether a device is used as a domain controller. Go ahead and create a new device collection and then use this query: Save the query and you should now see a collection of all your domain controllers. Just be careful …

SCCM – Create a device collection based on Domain Controller role Read More
sql2016
Guide / System Center

SCCM – Hardware and Client Version SQL Query

by Andrew Armstrong - Leave a Comment

If you are looking for a SQL query to pull back hardware inventory information and the SCCM client version, then you can use the below to pull all this information together. SELECT S.Name0 as Name, S.Client_Version0 as [Cient Version], OS.Caption0 as [Operating System], CS.Manufacturer0 as Manufacturer, CS.Model0 as Model, PB.SMBIOSBIOSVersion0 as BIOS, PR.Name0 as CPU, SUM(PM.Capacity0) as Memory, SUM(LD.Size0)as [Disc …

SCCM – Hardware and Client Version SQL Query Read More