SQL Query logic - Report on Collections based on Non-compliance of Configuration Baselines

Here is a quick trick on how to create a report based on a Configuration Baseline's Non-compliance utilizing only a collection ID.  This assumes the Config Item and Baseline are already created, as well as the Collection based on Non-Compliance of the baseline.  The report can then be generated based on the below query, then subscribed to, to check results over time.  The only thing needing editing in the query is the CollectionID in bold.

select all dbo.v_Collection.Name AS Location,
collection.Name AS [Computer Name],
    dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number],
    dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],
    dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Computer Model],
"Client" = CASE
WHEN collection.ClientType = 1 THEN 'Yes'
WHEN collection.ClientType = 0 THEN 'No'
ELSE ''
END,
    "Operating_System" = CASE
    WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Workstation 6.1%' THEN 'Windows 7'
    WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Workstation 10.0%' THEN 'Windows 10'
    WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Workstation 6.3%' THEN 'Windows 8.1'
WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Advanced Server 6.0%' THEN 'Windows Server 2008'
WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Advanced Server 5.2%' THEN 'Windows Server 2003'
    WHEN collection.DeviceOS LIKE 'Microsoft Windows NT%Server 6.1%' THEN 'Windows Server 2008 R2'
    WHEN collection.DeviceOS LIKE 'Microsoft Windows NT%Server 6.3%' THEN 'Windows Server 2012 R2'
    WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Workstation 5.1%' THEN 'Windows XP'
    WHEN collection.DeviceOS LIKE 'Mac OS X%' THEN 'Mac OS X'
    end,
    collection.Domain AS [Domain],
    dbo.v_CH_ClientSummary.LastPolicyRequest AS [Last Checked-in],
    dbo.v_CH_ClientSummary.LastHW AS [Last HW Inventory],
    collection.UserName AS [Last Logged User],
    dbo.v_FullCollectionMembership.CollectionID AS [CollID]

from _RES_COLL_COL00001 AS collection
left JOIN dbo.v_GS_COMPUTER_SYSTEM ON collection.MachineID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
left JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON collection.MachineID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID
left JOIN dbo.v_FullCollectionMembership ON collection.MachineID = dbo.v_FullCollectionMembership.ResourceID
left JOIN dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID
left JOIN dbo.v_CH_ClientSummary ON collection.MachineID = dbo.v_CH_ClientSummary.ResourceID
where
(dbo.v_FullCollectionMembership.CollectionID IS null
or dbo.v_FullCollectionMembership.CollectionID = 'CAS00002'
or dbo.v_FullCollectionMembership.CollectionID = 'CAS00003')
ORDER BY [Computer Name]

The 'where' clause includes extra Collections to which the results can be further filtered down - this was required in my situation since we didn't want to have all endpoints included.

SQL Reports - Building custom expressions

Just another entry for my own personal reference, but here is how I built expressions to accumulate total counts of machines running Windows 10 based on their hostname naming format.

The report was custom built utilizing basic text boxes aligned with the fields I desired. 

Notice the differentiation between 'Records' and 'Clients'.  These were different expressions since at the time we had a somewhat different count of clients vs actual records in SCCM since we had just finished the migration over the summer. 

The 'Records' expression was built like so:

=Sum(IIF(Fields!Computer_name.Value LIKE "L*",1,0),"DataSet1")

Whereas Clients were built like:

=Sum(IIF(Fields!Computer_name.Value LIKE "L*" AND Fields!Client0.Value = 1,1,0),"DataSet1")

The query for the dataset:

select  all SMS_R_System.DiscArchKey,
SMS_R_System.Name0 as 'Computer Name',
SMS_R_System.Operating_System_Name_and0 as 'Operating System',
SMS_R_System.Resource_Domain_OR_Workgr0,
SMS_R_System.Client0
from vSMS_R_System AS SMS_R_System where SMS_R_System.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 10%'

Powershellist - Using Config Baselines with Certificate Detection

This is mainly a post to jog my memory, so I won't go into too much detail, but here is how I handled a recent issue with a certificate that was accidentally deployed to the entire enterprise (not by me 😛).  It was up to me to remedy this utilizing a combination of a Configuration Baseline and Application deployment.  I decided Powershell would best handle this since there was not a way to remedy it in the Config Baseline with certutil.exe.

The Configuration Item's Powershell script was setup as follows:

$str1 = Get-ChildItem Cert:\LocalMachine\My | ? {$_.Extensions | ? {$_.oid.friendlyname -match "Template" -and $_.Format(0) -match "1.3.6.1.4.1.311.21.8.9698164.1945666.12076471.14939724.7091849.139.2709251.9725632"}}
if ($str1) { write-host 'Found'}
else {write-host 'Not found'}

 The Compliance Rule was then configured to be 'Equals: Not found', since we wanted the cert to be removed (and thus would be compliant if so).

I then created a collection based on the Config Baseline, and deployed the 'Cert Removal' application to the collection as Required.  The Cert Removal application was setup as follows:

RemoveCert.bat as command line.

RemoveCert.bat contents:

certutil -delstore MY "1.3.6.1.4.1.311.21.8.9698164.1945666.12076471.14939724.7091849.139.2709251.9725632"

'Cert Removal' Application Detection Method (Powershell):

$str1 = Get-ChildItem Cert:\LocalMachine\My | ? {$_.Extensions | ? {$_.oid.friendlyname -match "Template" -and $_.Format(0) -match "1.3.6.1.4.1.311.21.8.9698164.1945666.12076471.14939724.7091849.139.2709251.9725632"}}
if ($str1) { }
else{write-host 'Installed'}

Bookmark-worthy: Allow normal users to run a specific program as admin using ACT

This post from the Performance Team Blog depicts how to utilize Microsoft's Application Compatibility Toolkit to enable a standard user to run a pre-specified program as an Administrator.  This is useful for those programs that assume all users have local admin rights.  This can be deployed in SCCM either via a batch command after the program is installed, or as a separate application with the application itself as a dependency (since the program must be installed first).

SQL Query logic - avoiding duplicates with a multi-table query

Here's how to avoid duplicates in a SQL query/report utilizing multiple table joins.  The GROUP BY does the trick, and I also had to ensure I was only using the table columns I was choosing from the original 'system' table, as shown below:

select DISTINCT MAX(dbo.v_Collection.Name) AS Location,
system.Name0 AS [Computer Name],
max(dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0) AS [Serial Number],
max(dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0) AS [Manufacturer],
max(dbo.v_GS_COMPUTER_SYSTEM.Model0) AS [Computer Model],
"Operating_System" = CASE
WHEN system.Operating_System_Name_and0 LIKE 'Microsoft Windows NT Workstation 6.1%' THEN 'Windows 7'
WHEN system.Operating_System_Name_and0 LIKE 'Microsoft Windows NT Workstation 10.0%' THEN 'Windows 10'
WHEN system.Operating_System_Name_and0 LIKE 'Microsoft Windows NT Workstation 6.3%' THEN 'Windows 8.1'
WHEN system.Operating_System_Name_and0 LIKE 'Microsoft Windows NT Workstation 5.1%' THEN 'Windows XP'
end,
system.Resource_Domain_OR_Workgr0 AS [Domain],
max(dbo.v_CH_ClientSummary.LastPolicyRequest) AS [Last Checked-in],
max(v_RA_System_IPSubnets.IP_Subnets0) AS [IP Subnet],
max(dbo.v_CH_ClientSummary.LastHW) AS [Last HW Inventory],
system.User_Name0 AS [Last Logged User]
from vSMS_R_System AS system
left JOIN dbo.v_GS_COMPUTER_SYSTEM ON system.ItemKey = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
left JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON system.ItemKey = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID
left JOIN dbo.v_FullCollectionMembership ON system.ItemKey = dbo.v_FullCollectionMembership.ResourceID
left JOIN dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID
left JOIN dbo.v_CH_ClientSummary ON system.ItemKey = dbo.v_CH_ClientSummary.ResourceID
inner join v_RA_System_IPSubnets on v_RA_System_IPSubnets.ResourceID = System.ItemKey
where
(dbo.v_FullCollectionMembership.CollectionID = 'COL11111'
or dbo.v_FullCollectionMembership.CollectionID = 'COL22222') AND system.Client0 = '1' AND
(system.Operating_System_Name_and0 LIKE 'Microsoft Windows NT Workstation 6.1%' OR
system.Operating_System_Name_and0 LIKE 'Microsoft Windows NT Workstation 10.0%' OR
system.Operating_System_Name_and0 LIKE 'Microsoft Windows NT Workstation 6.3%' OR
system.Operating_System_Name_and0 LIKE 'Microsoft Windows NT Workstation 5.1%')
GROUP BY system.Name0, system.Operating_System_Name_and0, system.Resource_Domain_OR_Workgr0, system.User_Name0
ORDER BY [Computer Name]

FYI, the query grabs all clients in the SCCM system table, based on workstation OS (Windows XP through 10), shows all IP Subnets they are assigned to, as well as specifying the collections.

SQL query - List of all Applications referenced in Task Sequences

Since I was unable to find this query online, I decided to make it myself.  This SQL query lists all referenced applications in all Task Sequences in SCCM.

select app.DisplayName [Application],TS.Name [TS Name],TS.Description [TSDescription] From dbo.fn_ListLatestApplicationCIs(1033) app
inner join v_TaskSequenceAppReferencesInfo TSApp on app.ModelName=TSApp.RefAppModelName
inner join v_TaskSequencePackage TS on TS.PackageID=TSApp.PackageID
order by [Application]

This can be useful when cleaning up old applications and you want a quick way to show what Task Sequences are using what applications.

Powershellist - Remove a package from all distribution points

DexterPOSH originally created this script - however I feel it wasn't quite explained correctly (not to mention typed out for an easy copy/paste), so I'm re-blogging it here.  This Powershell script removes a package from all Distribution Points easily.  This is much easier than removing the package content from each individual distribution point from the Content Locations tab of the Package Properties (especially since I am currently working with 250 DP's):

Get-CimInstance -ClassName SMS_DistributionPoint -Namespace root/sms/site_A01 -ComputerName SCCMA01 | Where {$_.PackageID -eq "A01002C9"} | Remove-CimInstance -Verbose

 Be sure to edit the values in bold to fit your own environment (Site Code, Primary Site Server, and PackageID).

I have found this helpful when removing expired/old packages to free up space on all DP's.