SCCM SQL Query to get Bit-locker Recovery Key

 

SELECT

cm.Name,

ck.RecoveryKeyId,

cv.VolumeGuid,

cvt.TypeName AS 'Volume Type',

RecoveryAndHardwareCore.DecryptString(ck.RecoveryKey, DEFAULT) AS RecoveryKey,

RecoveryAndHardwareCore.DecryptBinary(ck.RecoveryKeyPackage, DEFAULT) AS BitLockerRecoveryKeyPackage,

ck.LastUpdateTime

FROM RecoveryAndHardwareCore_Keys ck

INNER JOIN RecoveryAndHardwareCore_Volumes cv on ck.VolumeID = cv.ID

LEFT JOIN RecoveryAndHardwareCore_VolumeTypes cvt on cv.VolumeTypeId = cvt.Id

LEFT JOIN RecoveryAndHardwareCore_Machines_Volumes cmv on cv.Id = cmv.VolumeId

LEFT JOIN RecoveryAndHardwareCore_Machines cm on cmv.MachineId = cm.Id

SCCM SQL Query for Windows Build Version and Support State

 select 

VRS.name0 as [Computer Name],

OS.Caption0 as [Operating System],

VRS.User_Name0 , 

WSLN.Value as [Windows 10 build Version],

Case WSLN.Value

WHEN 'Windows 10 2015 LTSB' THEN 'Windows 10, version 2015'

WHEN 'Windows 10 2016 LTSB' THEN 'Windows 10, version 2016'

ELSE WSLN.Value

END as Value,

CASE WSS.Branch  

         WHEN '0' THEN 'Current Branch'  

         WHEN '1' THEN 'Current Branch for Business'  

         WHEN '2' THEN 'Long Term Servicing Branch'  

END as 'Branch',  

CASE WSS.State 

WHEN '1' THEN 'Release Ready'  

WHEN '2' THEN 'Supported'  

WHEN '3' THEN 'Expires Soon'  

WHEN '4' THEN 'End of Life'   

END as 'State',

CHS.ClientStateDescription , 

CHS.LastActiveTime

from v_R_System VRS

JOIN vSMS_WindowsServicingStates WSS on VRS.OSBranch01 = WSS.Branch and VRS.Build01 = WSS.Build

JOIN vSMS_WindowsServicingLocalizedNames WSLN on WSS.Name = WSLN.Name

join v_GS_OPERATING_SYSTEM OS on VRS.ResourceID = OS.ResourceID

join v_CH_ClientSummary CHS on VRS.ResourceID = CHS.ResourceID

PowerShell Script to add Operating System Install Date in Registry

 $Date = Get-date 

$Registry = "Registry::HKLM\SOFTWARE\CompanyName"

$Registry1 = "Registry::HKLM\SOFTWARE\CompanyName\OperatingSystem"

$name = "OperatingSystem"

if (!(Test-Path -Path $Registry))

 {

  

  New-Item -Path $Registry -Force | Out-Null

  New-Item -Path $Registry1 -Force | Out-Null

  New-ItemProperty -LiteralPath $Registry1 -Name $name -Value $Date -PropertyType 'String' -Force | Out-Null

 }

Elseif (!(Test-path -Path $Registry1))

 {

   New-Item -Path $Registry1 -Force | Out-Null

  New-ItemProperty -LiteralPath $Registry1 -Name $name -Value $Date -PropertyType 'String' -Force | Out-Null

 }

 Else

 {

 New-ItemProperty -LiteralPath $Registry1 -Name $name -Value $Date -PropertyType 'String' -Force | Out-Null

 }

Intune - AutoPilot Pre-Requites & Troubleshooting

 

SCCM Powershell Script to Force Hardware Inventory using Script Option

 $LogFile = "$ENV:TEMP\HwInventRepair.log"

# Invoke a full (resync) HWI report

$Instance = Get-CimInstance -NameSpace ROOT\ccm\InvAgt -Query "SELECT * FROM InventoryActionStatus WHERE InventoryActionID='{00000000-0000-0000-0000-000000000001}'"

Set-Content -Path $LogFile -Value $Instance

$Instance | Remove-CimInstance

Add-Content -Path $LogFile -Value "Instance removed."

Invoke-CimMethod -Namespace ROOT\ccm -ClassName SMS_Client -MethodName TriggerSchedule -Arguments @{ sScheduleID = "{00000000-0000-0000-0000-000000000001}"}

Add-Content -Path $LogFile -Value "Trigger Hardware Inventory"

Start-Sleep -Seconds 5

 # Check InventoryAgent log for ignored message

$Log = "$env:SystemRoot\CCM\Logs\InventoryAgent.Log"

$LogEntries = Select-String –Path $Log –SimpleMatch "{00000000-0000-0000-0000-000000000001}" | Select -Last 1

If ($LogEntries -match "already in queue. Message ignored.")

{

    # Clear the message queue

    # WARNING: This restarts the SMS Agent host service

    Add-Content -Path $LogFile -Value "Hardware Inventory already triggered, may be hung."

    Stop-Service -Name CcmExec -Force

    Add-Content -Path $LogFile -Value "CcmExec service stopped."

    Remove-Item -Path C:\Windows\CCM\ServiceData\Messaging\EndpointQueues\InventoryAgent -Recurse -Force -Confirm:$false

    Add-Content -Path $LogFile -Value "Inventory Agent files deleted."

    Start-Service -Name CcmExec

    Add-Content -Path $LogFile -Value "CcmExec service started."

    # Invoke a full (resync) HWI report

    Start-Sleep -Seconds 5

    $Instance = Get-CimInstance -NameSpace ROOT\ccm\InvAgt -Query "SELECT * FROM InventoryActionStatus WHERE InventoryActionID='{00000000-0000-0000-0000-000000000001}'"

    Add-Content -Path $LogFile -Value $Instance

    $Instance | Remove-CimInstance

    Add-Content -Path $LogFile -Value "Instance removed again."

    Invoke-CimMethod -Namespace ROOT\ccm -ClassName SMS_Client -MethodName TriggerSchedule -Arguments @{ sScheduleID = "{00000000-0000-0000-0000-000000000001}"}

    Add-Content -Path $LogFile -Value "Trigger Hardware Inventory"

SCCM PowerShell Script to Remediate Windows Auto Update / Upgrade Issue

 Monitor 

$RegistryPath = 'HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU'

$RegistryPath1 = 'HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU'

$RegistryPath2 = 'HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate'

$Name = 'NoAutoUpdate'

$Name1 = 'AUOptions'

$Name2 = 'DisableDualScan'

$value = $(Get-ItemProperty $RegistryPath -Name $Name -ErrorAction SilentlyContinue ).$Name

$value1 = $(Get-ItemProperty $RegistryPath1 -Name $Name1 -ErrorAction SilentlyContinue ).$Name1

$value2 = $(Get-ItemProperty $RegistryPath2 -Name $Name2 -ErrorAction SilentlyContinue ).$Name2

If ($value -eq 0 -or $value1 -eq 'AUOptions' -or $value2 -eq 0)

{Write-Host "Non-Compliant"}

Else

{Write-Host "Compliant"}

Remediation

$RegistryPath = 'HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU'

$RegistryPath1 = 'HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU'

$RegistryPath2 = 'HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\'

$Name = 'NoAutoUpdate'

$Name1 = 'AUOptions'

$Name2 = 'DisableDualScan'

$value = $(Get-ItemProperty $RegistryPath -Name $Name -ErrorAction SilentlyContinue ).$Name

$value1 = $(Get-ItemProperty $RegistryPath1 -Name $Name1 -ErrorAction SilentlyContinue ).$Name1

$value2 = $(Get-ItemProperty $RegistryPath2 -Name $Name2 -ErrorAction SilentlyContinue ).$Name2

If ($value -eq 0)

{

New-ItemProperty -Force -Path $RegistryPath -Name $Name -Value '1' -PropertyType "DWord"

}

If ($value1 -ne 'AUOptions')

{

Remove-ItemProperty -Path $RegistryPath1 -Name $Name1 -Force -Confirm:$false

}

If ($value2 -eq 0)

{

New-ItemProperty -Force -Path $RegistryPath2 -Name $Name2 -Value '1' -PropertyType "DWord"

}

Intune PowerShell script to remediate Windows device sync issue

 Monitor Script

$DmWapPushSvcState = Get-ItemPropertyValue -Path "HKLM:\SYSTEM\CurrentControlSet\Services\dmwappushservice" -Name "Start"

 if ($DmWapPushSvcState -eq '2')

{

    Write-Output "DmWapPushSvc is in Enabled state"        

    exit 0

}

else

{

    Write-Output "DmWapPushSvc is in Disabled state"

    exit 1

}

Remediation Script

Invoke-Command {reg import "C:\ProgramData\DmWapPushService\dmwappushsvc.reg" *>&1 | Out-Null}

 $DmWapPushSvcState = Get-ItemPropertyValue -Path "HKLM:\SYSTEM\CurrentControlSet\Services\dmwappushservice" -Name "Start"

 if ($DmWapPushSvcState -eq '2') 

{

        exit 0

} 

else

{

        Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\dmwappushservice" -Name "Start" -Value '2'

        exit 1

}

Powershell script to check HTTPS MP Health Status

This is because the SCCM client uses a computer certificate to communicate with the management point, but when you are using a normal user you don’t have access to this certificate.

We can specify a certificate to use by using PowerShell as an admin and running the command Invoke-WebRequest.

PowerShell

Copy

$cert = Get-ChildItem -Path "cert:\LocalMachine\My\<ThumbprintOfCert>"

Invoke-WebRequest -Uri "https://<mp_server_name>/sms_mp/.sms_aut?mplist" -Certificate $cert

 }

PowerShell Script for Intune Proactive Remediation (Google Chrome)

 Monitor Script

try

{  

$chromeInstalled = Test-Path 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\chrome.exe'

if ($chromeInstalled -eq 'True') {

    Write-Host "Google Chrome is installed"

    exit 1

    }

    else {

        #No remediation required    

        Write-Host "Google Chrome is not installed"

        exit 0

    }  

}

catch {

    $errMsg = $_.Exception.Message

    Write-Error $errMsg

    exit 1

}

Remediate Script

# Start-Process Installers and Arguments

$Installer = "$env:ProgramFiles\Google\Chrome\Application\$ChromeVersion\Installer\chrmstp.exe"

$InstallerX86 = "${env:ProgramFiles(x86)}\Google\Chrome\Application\$ChromeVersion\Installer\chrmstp.exe"

$Arguements = "--uninstall --chrome --system-level --multi-install --force-uninstall"

$chromeInstalled = (Get-Item (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\chrome.exe' -ErrorAction SilentlyContinue).'(Default)').VersionInfo

 $ChromeVersion = $chromeInstalled.ProductVersion

 # Check for x64 Chrome

 $Chromex64 = "C:\Program Files\Google\Chrome\Application\$ChromeVersion\Installer\chrmstp.exe"

 $FileExistsx64 = Test-Path $Chromex64

 # Check for x86 Chrome

 $Chromex86 = "C:\Program Files (x86)\Google\Chrome\Application\$ChromeVersion\Installer\chrmstp.exe"

 $FileExistsx86 = Test-Path $Chromex86

 # Remove x64 Chrome

 If ($FileExistsx64 -eq $True) {

    Start-Process $Installer $Arguements -Wait

 }

  

  # Remove x86 Chrome

 If ($FileExistsx86 -eq $True) {

    Start-Process $InstallerX86 $Arguements -Wait

SCCM Close application using PowerShell - Popup

Powershell Script

 # add the required .NET assembly:

Add-Type -AssemblyName System.Windows.Forms

# show the MsgBox:

$result = [System.Windows.Forms.MessageBox]::Show('Please Click "Yes"  to close Google Chrome, click No to dont close  ', 'Info', 'YesNo', 'Warning')

# check the result:

if ($result -eq 'Yes')

{

  # Call the SSMS app with silent/no restart switch with exit code  

  #.\SSMS-Setup-ENU.exe /install /quiet /norestart     actuall isntallation from CMD

  (Stop-Process -Name "chrome" )

  Write-Warning 'Chrome Closed'

}

else

{

  Write-Warning 'Chrome Still Open'

}

Application Deployment Type – Program

Powershell.exe -ExecutionPolicy ByPass -File Your-Scriptfilename.PS1

Package – Program CMD Line

"%Windir%\sysnative\WindowsPowerShell\v1.0\powershell.exe" -ExecutionPolicy Bypass -Command .\Your-Scriptfilename.ps1