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.

Using Process Monitor

Sometimes a Google search on a complex issue just won't find the solution.  That's where Process Monitor comes in.  I have had to use Process Monitor a couple of times to fix issues that I knew weren't on the web (and maybe some that were).  In one case, one of my company's in-house apps wasn't allowing a user to use "Run as different user" for the app (which was a requirement to use it).  After using Process Monitor to pinpoint what exactly was happening at the point of failure, Process Monitor helped determine the issue where two files that resided in the Public profile needed to be moved/removed.  Without Process Monitor, I would have never figured this out (besides maybe weeks/months of trial and error) - and worse, even reimaging the machine wouldn't have fixed the issue.  Process Monitor can also help you quickly figure out where exactly in the registry a specific setting is in Windows, which helps with scripting/automation.

At first glance, Process Monitor is not an easy egg to crack.  You can easily get information overload from a couple of clicks in the program.  The power resides in how to filter and utilize this information at the point of failure and/or as you change settings in Windows.

We will disable the 'Location service' in Windows 10 settings as an example to find out where exactly this setting is in the registry.  First, obtain Process Monitor from here in case you missed the first link.  Open procmon.exe (no installation required) from the downloaded zip file.  The first thing you'll want to do is ensure Capture mode is off.  Click the magnifying glass icon (or press Ctrl-E) to disable the active process search.  The magnifying glass icon will show a red X over it when disabled.

Also, disable the AutoScroll option next to the Capture mode icon (shown disabled in the above picture).

Click the Clear icon (or Ctrl-X) to erase all processes on the list.

Open the desired settings window in Windows where you want to find the registry setting - in our case, it's under Settings - Location tab.  This can also be found by doing a Windows 10 search for Location privacy settings.  Look for the Location service option and ensure it's set to On.  (Because we want to turn it off, we want it in its original position.)

Here's the slightly tricky part - In Process Monitor, click the Capture (magnifying glass) icon to enable Capture mode, then, as quickly as you can, click the Location service option to Off, then switch to Process Monitor again and disable the Capture icon again.  You may notice other processes/functions appear besides the one we are looking for, but that's OK.  As it stands, we are looking at every little change on the system from the point we pressed the Capture icon on, then off.

This is a lot of information, even if only captured for a few seconds.  We want to filter this information by clicking Filter - Filter... (or Ctrl-L).  On the Filter menu, change the first dropdown box from Architecture to Operation, then the second dropdown to "is", then the third to "RegSetValue".  Make sure the last dropdown is set to 'Include'.  Click the Add button to add it to the list of Filters below, and ensure it's checked on the list, then click OK.

We now have a more manageable list of settings to pinpoint.  We know that one of these options is what we are looking for.

Generally we know that one registry change is only required, and since we see multiple "Explorer.EXE" values (under Process Name), we can rule those out.  This leaves us with svchost.exe and SystemSettings.exe (x2).  At this point, we can open RegEdit.exe to navigate to the shown key/data for the first SystemSettings.exe value.

The Value we are looking for is 'Deny', which looks like our winner.  We can further validate this by going back to the Location settings window and clicking it to the On position again.  Then we refresh the registry data (click another key, then click back to the original), At this point, we notice the Value field has changed to Allow (the original setting).  Therefore, we can now manually change the Value key to Deny (double-click it to change) in Regedit, then refresh the Location settings window.  The option is back to 'Off' again.  We have a winner!

Now, we can right-click the main key (on the left-hand side of Regedit) and click Export to save the file as a ".reg" file.  Give it a worthwhile name (such as LocationSetting.reg).

Since this exports the entire key, we want to remove any unneeded settings from the reg file we just saved.  Open the .reg file in Notepad, then delete the unneeded options.  Your .reg file should now show as the following:
Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\DeviceAccess\Global\{BFA794E4-F964-4FDB-90F6-51056BFE4B44}]
"Value"="Deny"

Save it, then test it by setting the Location Settings to On again, then open the reg file to import the info to the registry.  The setting should turn Off again.

That's it.  You are now one step closer to becoming a Windows troubleshooting master, despite how complex the issue at hand could be.