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.