Tag Archives: SQL

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

By | April 17, 2023

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 »

SCCM Collection Query for Devices with specific software installed and version or below [2023 Updated]

By | April 17, 2023

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 »

Migrate a System Center Orchestrator database to a different SQL Server

By | March 30, 2023

We were running through our list of servers and looking at what each one was doing in terms of Domain Controllers, Exchange servers, SQL servers, System Center servers etc when we noticed that we had quite a few servers that were using pretty small databases but had a large amount of host resource allocated to… Read More »

SQL Query to find members of an Active Directory Security Group

By | March 30, 2023

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 »

SCCM – Create a device collection based on Exchange Server role

By | April 17, 2023

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… Read More »

SCCM – Hardware and Client Version SQL Query

By | April 17, 2023

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 »

SCCM – Differences between Add Remove Programs Database Tables

By | March 30, 2023

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… Read More »

System Center Orchestrator – SQL Query to show log file data

By | June 10, 2023

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… Read More »

SCCM SQL Query – Google Earth Installs

By | March 30, 2023

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… Read More »

SQL Server Management Studio (SSMS) 2016 Released

By | March 30, 2023

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… Read More »

SQL Server Version Numbers

By | March 30, 2023

Below is a full list of SQL Server version numbers dating from version 7.0 through to the current 2016 RTM release. Codename RTM Service Pack 1 Service Pack 2 Service Pack 3 Service Pack 4 SQL Server 2016 NA 13.0.1601.5 SQL Server 2014 SQL14 12.0.2000.8 12.0.4100.1 12.0.5000.0 SQL Server 2012 Denali 11.0.2100.60 11.0.3000.0 11.0.5058.0 11.0.6020.0… Read More »