SCCM SQL Queries

Here are queries I've used that 

Count of all operating systems in the environment

select caption0, Count (Caption0) from [dbo].[v_GS_OPERATING_SYSTEM] group by Caption0

Count of all SCCM client version

select Client_Version, Count(Client_Version) from [dbo].[v_ActiveClients] group by Client_Version

Count of Windows 10 Versions

select Caption0 As 'Operating System Name', 
Case When BuildNumber0 = '16299' Then '1709'
when BuildNumber0 = '17134' THEN '1803'
END AS 'Version',
Count(BuildNumber0 ) as '# of Systems'
 from [dbo].[v_GS_OPERATING_SYSTEM]
Where Caption0 not like '%server%' and Caption0 like '%Windows 10 enterprise%' and Caption0 not like '%LTSB%'
group by BuildNumber0, Caption0

List of all Windows 10 Enterprise by Version 
(Modify to add more Case items for the versions in your environment)

SELECT        v_R_System.Name0, v_GS_OPERATING_SYSTEM.Caption0, 

Case When BuildNumber0 = '16299' Then '1709'

when BuildNumber0 = '17134' THEN '1803'

END AS 'Version'

FROM            v_GS_OPERATING_SYSTEM INNER JOIN

                         v_R_System ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID

 Where Caption0 not like '%server%' and Caption0 like '%Windows 10 enterprise%' and Caption0 not like '%LTSB%'

List of Windows Update Agent Version

SELECT        v_R_System.Name0 AS 'Computer Name', v_GS_WINDOWSUPDATEAGENTVERSION.Version0 AS 'Windows Update Version', 
v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System', v_R_System.Client0, v_R_System.Obsolete0 
                         
FROM            v_GS_WINDOWSUPDATEAGENTVERSION INNER JOIN
                         v_R_System ON v_GS_WINDOWSUPDATEAGENTVERSION.ResourceID = v_R_System.ResourceID INNER JOIN
                         v_GS_OPERATING_SYSTEM ON v_GS_WINDOWSUPDATEAGENTVERSION.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
Where Client0 = '1' and Obsolete0 = '0'

ORDER BY 'Windows Update Version'

List of Failed Distributed Packages

SELECT        v_DistributionPointInfo.ServerName AS 'DP Name', v_ContentDistribution.PkgID AS 'Package ID', 
                         CASE WHEN v_ContentDistribution.State = '2' THEN 'Failed' WHEN v_ContentDistribution.State = '0' THEN 'Success' END AS 'Distribution State', v_Package.Name AS 'Package Name', 
                         v_DistributionPointInfo.SiteCode
FROM            v_ContentDistribution INNER JOIN
                         v_Package ON v_ContentDistribution.PkgID = v_Package.PackageID INNER JOIN
                         v_DistributionPointInfo ON v_ContentDistribution.DPID = v_DistributionPointInfo.ID
WHERE        (v_ContentDistribution.State != '0')

CMPivot Join Query Example - SCCM / Intune

I find there are lack of query with joins example and created this from a need to see what model had the new version installed.  We deployed an application to a location and thought the issue was system type related.

Replace the bolded items with the actual names.

The first query is based on a specific naming convention.   

InstalledSoftware | where ProductName contains 'ProductName' and ProductVersion == 'ProductVersion' | join  kind=inner (Device | where Device contains 'DeviceName') | project  Device, Model, ProductName, ProductVersion

This query will return all devices that matches the product conditions.

 InstalledSoftware | where ProductName contains 'ProductName' and ProductVersion == 'ProductVersion' | join  kind=inner (Device) | project  Device, Model, ProductName, ProductVersion

Get-ADComputer Powershell Custom Expression Pipeline

You may have to periodically run reports on where the computer is in AD but can't sort by the OU with the DistinguishedName containing the computer name.

$list = Get-content "C:\list\final.txt"

Foreach ($PC in $list){Get-ADComputer $PC | select Name, @{Name = 'AD OU'; Expression = {$_.DistinguishedName -replace "CN=$PC," } } }

Here's the same but for Windows 7 machines replacing it real time.

get-adcomputer -filter {Operatingsystem -like "Windows 7*"} -Properties * | select Name, OperatingSystem, @{Name = 'AD OU'; Expression = {$_.DistinguishedName -replace "CN=" + $_.Name + ","} }

Applications Doesn't Work on Windows 10 1809 builds

If you come across applications that communicate to a server or service that stopped working in newer Windows 10 1809 or newer and have that's not a firewall or proxy issue then check for the TLS setting.

If the app needs TLS 1.0 enabled, change the settings and test the app.  A reboot is not required.  

Apps like Cofense and FS Pro 2020 runs on TLS 1.0.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client

Set these keys to the value below.

DisabledByDefault to 0

Enabled to 1

Windows 10 and Dell 1600 MFP Printer

If you have an old Dell printer that has no Windows 10 drivers available but has a network interface, try installing the printer via TCP/IP and using the Windows 7 x64 drivers.  If you don't have a network switch, you should be able to use a crossover cable from PC to printer.

I had given up on making it work from all the posts on the internet.  I didn't really care if I used it via USB or not, just wanted something to print from that was reliable.  I had spent $80 on ink for a free HP inkjet printer which ended up drying out and was ready to toss that thing.

I only came across this because I used a Macbook for my kid's remote school learning and surprised it found the printer.   After that, I tried installing the Bonjour service to no avail and then installed it via TCP/IP and was able to get it to print.  

This was a printer from a company I use to work for 12+ years ago that went out of business and still has 21% toner left. Rather than throwing something that still has plenty of life, it is usable again!  The inkjet however will be given away or disposed of... 

LDAP Query for Active Windows Client Workstations

If you are using ADUC or a security scan engine tool, this is how you can limit to searches for active machines.

Copy and paste the text into a custom search in ADUC or whatever you need it for.

(&(&(&(objectCategory=computer)(objectClass=computer)(|(operatingSystem=Windows\2010*)(operatingSystem=Windows\207*))(!userAccountControl:1.2.840.113556.1.4.803:=2))))

The "\20" is a placeholder for a space.  If you need to add other operating systems, add another "(operatingSystem=Windows\207*)" after it. 

XP: 

(operatingSystem=Windows\20XP*)

SCCM - AutoPilot Hardware (HW) IDs Report Query

We're trialing Intune to either replace or co-manage with SCCM and testing out Autopilot.

The generic canned report doesn't give you the computer name and only serial information, it is located under Hardware  - General -> Windows AutoPilot Device Information

Here's a query to pull more information modifying the same query to include computer name so you can enroll specific computers.  

SELECT        v_R_System.Name0 AS [PC Name], v_GS_PC_BIOS.SerialNumber0 AS [Serial Number], v_GS_MDM_DEVDETAIL_EXT01.DeviceHardwareData0 AS [Device HW ID], v_GS_OPERATING_SYSTEM.SerialNumber0 as [OS Serial], v_GS_OPERATING_SYSTEM.Caption0 as [Operating System] 

                         

FROM            v_R_System INNER JOIN

                         v_GS_MDM_DEVDETAIL_EXT01 ON v_R_System.ResourceID = v_GS_MDM_DEVDETAIL_EXT01.ResourceID INNER JOIN

                         v_GS_COMPUTER_SYSTEM_PRODUCT ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID INNER JOIN

                         v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN

                         v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID

ORDER BY v_GS_COMPUTER_SYSTEM_PRODUCT.Version0