Create a Custom Detection Script for Microsoft System CLR Types for SQL Server 2012 (PowerShell)

Microsoft Endpoint Manager Configuration Manager (MEMCM / SCCM) and Microsoft Intune use Detection Rules to determine the presence of Applications & Win32 Apps. The detection rules ensure that application installations only begin to run if the application is not already installed on the device. This article will serve as an informative guide and give you a clear understanding of how to create an updated custom detection script for each new version of Microsoft System CLR Types for SQL Server 2012 using PowerShell.

How to Create a Custom Detection Script for Microsoft System CLR Types for SQL Server 2012

Microsoft System CLR Types for SQL Server 2012 x86 (Registry Detection Method)

## Check for Microsoft System CLR Types for SQL Server 2012 (x86) (Registry Detection Method)
$SQLSysClrTypes = Get-ChildItem -Path "HKLM:\SOFTWARE\Classes\Installer\Products" -ErrorAction SilentlyContinue | Get-ItemProperty | Where-Object {$_.ProductName -like 'Microsoft System CLR Types for SQL Server 2012' -and $_.ProductName -notlike 'Microsoft System CLR Types for SQL Server 2012 (x64)' } | Select-Object -Property ProductName, PSChildName
$SQLSysClrTypes.ProductName
$SQLSysClrTypes.PSChildName

## Create Text File with Microsoft System CLR Types for SQL Server 2012 (x86) Registry Detection Method
If(Test-Path "HKLM:\SOFTWARE\Classes\Installer\Products\$($SQLSysClrTypes.PSChildName)") { 

$FilePath = "C:\Windows\Temp\SQLSysClrTypes_2012_x86_Detection_Method.txt"
New-Item -Path "$FilePath" -Force
Set-Content -Path "$FilePath" -Value "If((Get-ItemPropertyValue -Path 'HKLM:\SOFTWARE\Classes\Installer\Products\$($SQLSysClrTypes.PSChildName)' -Name ProductName -ea SilentlyContinue) -eq '$($SQLSysClrTypes.ProductName)') {"
Add-Content -Path "$FilePath" -Value "Write-Host `"Installed`""
Add-Content -Path "$FilePath" -Value "}"
Invoke-Item $FilePath 
}
  • Click Run Script (F5)
  • A text file will open with the Microsoft System CLR Types for SQL Server 2012 x86 Detection Method script required to detect the current version of Microsoft System CLR Types for SQL Server 2012 x86 that is installed on the device you are running the script from.

Example:

If((Get-ItemPropertyValue -Path 'HKLM:\SOFTWARE\Classes\Installer\Products\2E31B20B229A0D441B7E80F4C56EE1E2' -Name ProductName -ea SilentlyContinue) -eq 'Microsoft System CLR Types for SQL Server 2012') {
Write-Host "Installed"
}

Microsoft System CLR Types for SQL Server 2012 x64 (Registry Detection Method)

## Check for Microsoft System CLR Types for SQL Server 2012 (x64) (Registry Detection Method)
$SQLSysClrTypes = Get-ChildItem -Path "HKLM:\SOFTWARE\Classes\Installer\Products" -ErrorAction SilentlyContinue | Get-ItemProperty | Where-Object {$_.ProductName -like 'Microsoft System CLR Types for SQL Server 2012 (x64)' } | Select-Object -Property ProductName, PSChildName
$SQLSysClrTypes.ProductName
$SQLSysClrTypes.PSChildName

## Create Text File with Microsoft System CLR Types for SQL Server 2012 (x64) Registry Detection Method
If(Test-Path "HKLM:\SOFTWARE\Classes\Installer\Products\$($SQLSysClrTypes.PSChildName)") { 

$FilePath = "C:\Windows\Temp\SQLSysClrTypes_2012_x64_Detection_Method.txt"
New-Item -Path "$FilePath" -Force
Set-Content -Path "$FilePath" -Value "If((Get-ItemPropertyValue -Path 'HKLM:\SOFTWARE\Classes\Installer\Products\$($SQLSysClrTypes.PSChildName)' -Name ProductName -ea SilentlyContinue) -eq '$($SQLSysClrTypes.ProductName)') {"
Add-Content -Path "$FilePath" -Value "Write-Host `"Installed`""
Add-Content -Path "$FilePath" -Value "}"
Invoke-Item $FilePath 
}
  • Click Run Script (F5)
  • A text file will open with the Microsoft System CLR Types for SQL Server 2012 x64 Detection Method script required to detect the current version of Microsoft System CLR Types for SQL Server 2012 x64 that is installed on the device you are running the script from.

Example:

If((Get-ItemPropertyValue -Path 'HKLM:\SOFTWARE\Classes\Installer\Products\9EABCCEC0881E114D982E865F2D6AA2E' -Name ProductName -ea SilentlyContinue) -eq 'Microsoft System CLR Types for SQL Server 2012 (x64)') {
Write-Host "Installed"
}

Always make sure to test everything in a development environment prior to implementing anything into production. The information in this article is provided “As Is” without warranty of any kind.