SQL

SCCM – Report on PST File Locations

SCCM – Report on PST File Locations

Users love to keep their email for as long as they possibly can, but when IT Admins start telling them to archive their email because their mailbox is too big, sometimes things get worse and PST files end up being created all over your corporate Filestores. Even worse than that, users will create their PST […]

SCCM – Active Directory Security Group Query for User Collection

SCCM – Active Directory Security Group Query for User Collection

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 […]

SCCM – Create a device collection based on Exchange Server role

SCCM – Create a device collection based on Exchange Server role

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 […]

SCCM – Create a device collection based on Domain Controller role

SCCM – Create a device collection based on Domain Controller role

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 […]

SCCM – Hardware and Client Version SQL Query

SCCM – Hardware and Client Version SQL Query

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, […]

SCCM SQL Query for Software not in Add Remove Programs

SCCM SQL Query for Software not in Add Remove Programs

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. […]

SCCM – Differences between Add Remove Programs Database Tables

SCCM – Differences between Add Remove Programs Database Tables

We had seen a few posts around the internet where people are asking the differences between the various Add_Remove_Programs data within the System Center Configuration Manager database. So we thought we would post a quick reference here to confirm which table of data means what. There are five different tables of data available to you […]

SCCM SQL Query for all server CPU information

SCCM SQL Query for all server CPU information

If you are looking for an SQL query to use against your System Center Configuration Manager database to give you the ability to report on server CPU information, this is the query for you! You can use the below to get various information on the CPU or you can use SELECT * to bring back […]

SCCM – SQL Query for all Laptops

SCCM – SQL Query for all Laptops

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 […]

System Center Orchestrator – SQL Query to show log file data

System Center Orchestrator – SQL Query to show log file data

If you are using System Center Orchestrator (SCORCH)  Runbooks then you may have run into issues where the database is increasing in size due to the large amount of log files being created. This typically occurs when monitoring event logs on servers amongst some other runbook options in SCORCH. If you want to check how […]

SCCM SQL Query – Google Earth Installs

SCCM SQL Query – Google Earth Installs

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 […]

SQL Server Management Studio (SSMS) 2016 Released

SQL Server Management Studio (SSMS) 2016 Released

Microsoft have now released SQL Server Management Studio (SSMS) 2016 and is ready to download and use. You can get the download by clicking the button below: Download SSMS 2016 SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group […]

Scroll to top

×