Making IT Happen
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 … Read more
Just a quick post to add to our list of SCCM collection queries. We needed one today for a collection of virtual machines only. This query you can use to create a collection to do this: You can check out our other SCCM collection queries by following this link. 192
I’ve not done a post about SCCM queries for a while now so in addition to a load I did some time ago, I thought I’d bring things up to date with a few new queries starting with Microsoft Server 2019. I’ve been using this one for creating a Windows Server 2019 collection in SCCM … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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, … Read more
If you are looking for an SCCM SQL query to create a report to search for machines which do not have a particular software installed, you can use the following: Then you can use the variable @displayname to choose from a drop down of software that is in the SCCM database pulled from client machines. … Read more
If you are using Configuration Manager, you will be aware that you can create a number of custom reports using the SCCM SQL data and SSRS to produce the reports. The best way to start to create a custom report is to ensure that you get your SQL query correct first. We have written hundreds … Read more
If you are using Configuration Manager and you want to create a collection based on all Windows 10 installations, you can use this WQL query below: select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like “%Microsoft Windows NT Workstation 10%” You can change the “%Microsoft Windows NT Workstation 10%” to create collections … Read more
If you are using Configuration Manager and want to run an SQL query on your database to count the number of different models you have in your organisation, you can use this SQL query below using SSRS to create a custom report: More Queries Our full range of SQL and WQL Collection queries are available here. … Read more
