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 creating a custom report is to ensure that you get your SQL query correct first. We have written hundreds of […]
Query
SCCM SQL Query – Google Earth Installs
![SCCM SQL Query - Google Earth Installs 2 google earth pro MSI logo](https://techygeekshome.info/wp-content/uploads/2016/11/google-earth-12-700x406.jpg)
We have recently been asked to report on Google Earth installations within our SCCM environment. So, as we were already collecting the EXE file information within our hardware inventory, we could make use of the SoftwareFile data in the SCCM SQL database. Also, having spent some time Googling a query for Google Earth, we found that […]
SCCM WQL Query – All Windows 10 Installs
![SCCM WQL Query - All Windows 10 Installs 3 ssc 0](https://techygeekshome.info/wp-content/uploads/2016/08/ssc_0-e1529097015847.jpg)
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 […]
SCCM SQL Query – Count Computers by Model
![SCCM SQL Query - Count Computers by Model 4 sql](https://techygeekshome.info/wp-content/uploads/2016/08/sql.png)
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. […]
SCCM WQL Query – No ConfigMgr Client Installed
![SCCM WQL Query - No ConfigMgr Client Installed 5 ssc 0](https://techygeekshome.info/wp-content/uploads/2016/08/ssc_0-e1529097015847.jpg)
If you are using Configuration Manager and you want to create a collection of all devices that do not have the Configuration Manager Client installed, then you can use this WQL query: More Queries Our full range of SQL and WQL Collection queries are available here. Feedback If you have any questions or feedback about this post, or […]
SCCM WQL Query – Clients without latest version installed
![SCCM WQL Query - Clients without latest version installed 6 sccm logo](https://techygeekshome.info/wp-content/uploads/2016/02/sccm.jpg)
If you want to create a collection in Configuration Manager to show you all the devices that are not up to a specified version, then you can use the 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.ClientVersion != “5.00.8412.1007” Where “5.00.8412.1007” is the latest version number that you are […]
SCCM WQL Query – All Inactive Clients
![SCCM WQL Query - All Inactive Clients 7 ssc 0](https://techygeekshome.info/wp-content/uploads/2016/08/ssc_0-e1529097015847.jpg)
If you want to create a Configuration Manager Collection with all inactive devices in it, you can use the following WQL query. You can also change the last “0” to “1” to create a collection of active devices.
SCCM – 52 Operational Collections
![SCCM - 52 Operational Collections 8 sccm logo](https://techygeekshome.info/wp-content/uploads/2016/02/sccm.jpg)
There is an excellent Powershell download available giving you 52 operational collections within SCCM. You should go over to the link below and take a look: 52 SCCM Powershell Collections Full list of collections created using the Powershell script are: All Clients All Clients Active All Client Inactive All Clients R2 CU0 All Clients R2 […]
SCCM Collection Query – Virtual Machines
![SCCM Collection Query - Virtual Machines 9 sccm logo](https://techygeekshome.info/wp-content/uploads/2016/02/sccm.jpg)
If you are looking for a SCCM collection query for virtual machines in your environment, then you can use this query below:
SCCM Collection based on Active Directory Organisation Unit (OU) Membership
![SCCM Collection based on Active Directory Organisation Unit (OU) Membership 10 sccm logo 600x400](https://techygeekshome.info/wp-content/uploads/2017/11/sccm-600x400.jpg)
You may want to create a Configuration Manager Collection based upon an Active Directory Organisation Unit. To do this you can use the following WQL code and then simply amend the last part to be the Organisation Unit that you want to base the Collection on: SELECT SMS_R_USER.ResourceID, SMS_R_USER.ResourceType, SMS_R_USER.Name, SMS_R_USER.UniqueUserName, SMS_R_USER.WindowsNTDomain FROM SMS_R_User WHERE […]
SCCM – Report on Outlook Archive PST files on Devices
![SCCM - Report on Outlook Archive PST files on Devices 11 sccm logo 600x400](https://techygeekshome.info/wp-content/uploads/2017/11/sccm-600x400.jpg)
If you want to report on machines that have Outlook archive (PST’s) on them, where they are and how big they are, then you can use this guide to create a report that will do this for you using Configuration Manager. First of all, you will need to tell your Software Inventory to look for […]
SCCM – WQL Query for Devices with Configuration Manager Client Installed and no System Center Endpoint Protection Installed
![SCCM - WQL Query for Devices with Configuration Manager Client Installed and no System Center Endpoint Protection Installed 16 sql2016](https://techygeekshome.info/wp-content/uploads/2016/10/sql2016.jpg)
We have had a report that one of our WQL queries has not been working correctly, this is the one for devices that have Configuration Manager client installed but do not have System Center Endpoint Protection installed on them. The original post is here: WQL Query for Devices without Endpoint Installed So, we have looked […]