Create a Custom Detection Script for SQL Server Management Studio (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 SQL Server Management Studio using PowerShell.

How to Create a Custom Detection Script for SQL Server Management Studio

SQL Server Management Studio (File Detection Method)

## Check for SQL Server Management Studio (File Detection Method)
$SSMSExe = (Get-ChildItem -Path "C:\Program Files\Microsoft SQL Server Management Studio*\*","C:\Program Files (x86)\Microsoft SQL Server Management Studio*\*" -Include Ssms.exe -Recurse -ErrorAction SilentlyContinue)
$SSMSExe.FullName
$SSMSPath = $($SSMSExe.FullName).Replace("C:\Program Files\","").Replace("C:\Program Files (x86)\","")
$ProductVersion = (Get-Item -Path "$($SSMSExe.FullName)" -ErrorAction SilentlyContinue).VersionInfo.ProductVersion

## Create Text File with SQL Server Management Studio File Detection Method
$FilePath = "C:\Windows\Temp\SQL_Server_Management_Studio_Detection_Method.txt"
New-Item -Path "$FilePath" -Force
Set-Content -Path "$FilePath" -Value "If([String](Get-Item -Path `"`$Env:ProgramFiles\$SSMSPath`",`"`${Env:ProgramFiles(x86)}\$SSMSPath`" -ErrorAction SilentlyContinue).VersionInfo.ProductVersion -ge `"$ProductVersion`"){"
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 SQL Server Management Studio Detection Method script required to detect the current version of SQL Server Management Studio that is installed on the device you are running the script from.

Example:

If([String](Get-Item -Path "$Env:ProgramFiles\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe","${Env:ProgramFiles(x86)}\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe" -ErrorAction SilentlyContinue).VersionInfo.ProductVersion -ge "15.0.18424.0"){
Write-Host "Installed"
}
Deploy Symantec DLP - 3. Install De...
Deploy Symantec DLP - 3. Install Detection Servers (Discovery and Endpoint Protection)

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.

Recent Posts