- Log into the Carbon Black Cloud Portal
- Click the Investigate tab
- In the investigate search field at the top, enter the following:
- Enter process_username:<username> in the search field at the top. <username> needs to be changed to the actual username you are searching for.
- Change the time field to the right to within one day or less
- Click the magnifying glass on the far right to search.
- Under the filters field, scroll down to Device and it will show a list of devices the profile is currently logged into.
17 March 2022
Identify Machines a User is Logged Into using Carbon Black
16 March 2022
Last Server Reboot Reporting
Recently, we needed a report of the last boot time of all servers. I wrote this PowerShell script that queries AD for a list of all windows servers and then does a WMI query on each server for the LastBootUpTime. It then calculates the number of days and writes this to an object with the computer name and the number of days since the last reboot. It will write this info to a CSV file.
You can download the script from my GitHub site.
Import-Module -Name ActiveDirectory -Force
#Get list of windows based servers
$Servers = Get-ADComputer -Filter * -Properties * | Where-Object {$_.OperatingSystem -like '*windows server*'} | Select Name | Sort-Object -Property Name
#Create Report Array
$Report = @()
#Parse through server list
Foreach ($Server in $Servers) {
#Get the computer name
$ComputerName = ([String]$Server).Split("=")[1].Split("}")[0].Trim()
#Check if the system is online
If ((Test-Connection -ComputerName $ComputerName -Count 1 -Quiet) -eq $true) {
#Query last bootup time and use $Null if unobtainable
Try {
$LastBootTime = (Get-CimInstance -ClassName win32_operatingsystem -ComputerName $ComputerName -ErrorAction SilentlyContinue).LastBootUpTime
$LastBoot = (New-TimeSpan -Start $LastBootTime -End (Get-Date)).Days
} Catch {
$LastBoot = $null
}
#Add computername and last boot time to the object
If ($ComputerName -ne $null) {
$SystemObject = New-Object -TypeName psobject
$SystemObject | Add-Member -MemberType NoteProperty -Name ComputerName -Value $ComputerName
$SystemObject | Add-Member -MemberType NoteProperty -Name DaysSinceLastBoot -Value $LastBoot
$Report += $SystemObject
}
} else {
$SystemObject = New-Object -TypeName psobject
$SystemObject | Add-Member -MemberType NoteProperty -Name ComputerName -Value $ComputerName
$SystemObject | Add-Member -MemberType NoteProperty -Name DaysSinceLastBoot -Value 'OFFLINE'
$Report += $SystemObject
}
$ComputerName = $null
}
#Print report to screen
$Report
$OutFile = "C:\Users\Desktop\LastRebootReport.csv"
#Delete CSV file if it already exists
If ((Test-Path -Path $OutFile) -eq $true) {
Remove-Item -Path $OutFile -Force
}
#Export report to CSV file
$Report | Export-Csv -Path $OutFile -NoClobber -Encoding UTF8 -NoTypeInformation -Force
07 March 2022
Configure SQL Server Firewall Ports with PowerShell
I recently had to rebuild the Configuration Manager server. As I was running the prerequisite tool, it showed it could not communicate with the SQL server that is separate from the Configuration Manager Server. The issue ended up being ports needed to be opened up.
This PowerShell script will configure the correct ports. It also adds to the description as to what services the port is opened up for in Configuration Manager. If the rule is already present, it skips over. If you open up a rule after the script is executed, you will see it says This is a predefined rule and some of its properties cannot be modified. This was caused by me adding the rule to the group Configuration Manager. If -Group is removed from the cmdlet, this message disappears.
You can download the script from here.
If ((Get-NetFirewallRule -Name "ConfigMgr Port 135 UDP" -ErrorAction SilentlyContinue) -eq $null) {
New-NetFirewallRule -Name "ConfigMgr Port 135 UDP" -DisplayName "ConfigMgr Port 135 UDP" -Description "Site Server" -Group "Configuration Manager" -Profile "Domain" -Protocol UDP -LocalPort 135 -Enabled True
}
If ((Get-NetFirewallRule -Name "ConfigMgr Port 135 TCP" -ErrorAction SilentlyContinue) -eq $null) {
New-NetFirewallRule -Name "ConfigMgr Port 135 TCP" -DisplayName "ConfigMgr Port 135 TCP" -Description "Site Server" -Group "Configuration Manager" -Profile "Domain" -Protocol TCP -LocalPort 135 -Enabled True
}
If ((Get-NetFirewallRule -Name "ConfigMgr Port 1433 TCP" -ErrorAction SilentlyContinue) -eq $null) {
New-NetFirewallRule -Name "ConfigMgr Port 1433 TCP" -DisplayName "ConfigMgr Port 1433 TCP" -Description "Asset Intelligence Synchronization Point, App Catalog Web Service Point, Endpoint Protection, Enrollment Point, MP, Reporting point, Site Server, SMS Provider, SQL Server, SMP" -Group "Configuration Manager" -Profile "Domain" -Protocol TCP -LocalPort 1433 -Enabled True
}
If ((Get-NetFirewallRule -Name "ConfigMgr Port 4022 TCP" -ErrorAction SilentlyContinue) -eq $null) {
New-NetFirewallRule -Name "ConfigMgr Port 4022 TCP" -DisplayName "ConfigMgr Port 4022 TCP" -Description "SQL Server" -Group "Configuration Manager" -Profile "Domain" -Protocol TCP -LocalPort 4022 -Enabled True
}
If ((Get-NetFirewallRule -Name "ConfigMgr Port 445 TCP" -ErrorAction SilentlyContinue) -eq $null) {
New-NetFirewallRule -Name "ConfigMgr Port 445 TCP" -DisplayName "ConfigMgr Port 445 TCP" -Description "Site Server" -Group "Configuration Manager" -Profile "Domain" -Protocol TCP -LocalPort 445 -Enabled True
}
24 January 2022
Troubleshooting No Task Sequences are available (Tasksequence.xml does not exist, is empty, or is inaccessible)
I encountered this error when working with MDT right after deleting two old task sequences.
To troubleshoot this, I went to the logs directory in the MDT hierarchy. I specified in the customsettings.ini file to write all build logs to this specific directory:
SLShare=\\BUILD\PRODDeploymentShare$\Logs
SLShareDynamicLogging=\\BUILD\PRODDeploymentShare$\Logs\%ComputerName%
When I went into the logs, I opened up the only log there, BDD.log, and found that it could not find the task sequence specified in the TaskSequences.xml file. I then opened up the TaskSequences.xml file, which is located at %DEPLOYROOT%\Control\TaskSequences.xml. Once I opened up the XML, I searched for the task sequence no found and deleted it. That fixed the issue.
21 January 2022
How to effectively add Office updates to the update folder
If you are still using an on-prem version of office, you know the need to populate the Updates folder so updates get applied when Office is installed instead of having to wait for the updates to download and then be installed. The issue I have run into is some of the updates, once extracted, are named the same. To resolve this, I extract each update from the cab file. Next, I open up the msp file using ORCA. In the MsiPatchMetadata table, you will find the KB number in the Release property. I use that number and rename the msp file to KB4011634.msp for instance. This prevents files from being overwritten. I then copy it back to the Updates folder. Office is able to read the MSP file with no problems.
20 December 2021
Installing WinGet via PowerShell
I wanted to install Winget via PowerShell in an automated process. The first thing I did was to go to the Winget GitHub site and select the latest version of the Windows Package Manager. Under the latest version page, I clicked on the file with the extension of msixbundle and downloaded it to my machine.
Now that it is on your machine, you can automate the installation with a PowerShell one-liner script where the PS1 file resides in the same directory as the Winget installer. The script will search the current directory for a msixbundle file with the cmdlet listed below.
Add-AppPackage -Path ((Get-ChildItem -Path ((Get-Location).ProviderPath) -Filter *.msixbundle).FullName)
19 November 2021
Administrator Reporting Tool
This PowerShell tool queries AD for a list of machines in the specified administrative admin groups. The list is specified by modifying the script with the groups to be queried. The following example shows how to add groups to the query. When there are multiple groups, they can be divided off by using the pipe character between each group.
Where-Object {$_.MemberOf -match 'Admins|Domain Admins|System Admins|'}
There is the parameter called $Days. This specifies how many days old the account needs to be so it is not displayed in the report anymore.
I wrote this script so that it can easily be used with Orchestrator or as a scheduled task. It exits the script with an error code 0 if there is data to emailed along with the Write-Output statement that puts the list of users in the output of the program once exited. If there was no data to return, it exits with an error code 1 so that Orchestrator knows not to proceed with the email task.
You can download the script from my GiHub Site.
<#
.SYNOPSIS
Administrator Report
.DESCRIPTION
This tool is intended to keep staff informed of new administrator accounts. This script queries for a list of users in the specified administrator group(s). It then produces a list of the administrator users that got created within the specified number of days.
.PARAMETER Days
Number of days since the administrator account was created
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2021 v5.8.195
Created on: 11/9/2021 1:37 PM
Created by: Mick Pletcher
Filename: AdministratorReport.ps1
===========================================================================
#>
Param
(
[ValidateNotNullOrEmpty()][int]$Days = 1
)
#Retrieves a list of users from AD and filters them by association with the specied security groups. The match can be associated with multiple groups separated with a pipe
#Example: Where-Object {$_.MemberOf -match '|Domain Admins|System Admins|'}
$Users = Get-ADUser -Filter * -Properties MemberOf | Where-Object {$_.MemberOf -match 'Super Admins|Domain Admins'}
#Filter out all accounts that are older than the specified $Days
$Users | ForEach-Object {
If ((New-TimeSpan -Start ((Get-ADUser -Identity $_.SamAccountName -Properties whenCreated).whenCreated) -End (Get-Date)).Days -le $Days) {
$NewUsers += $_.Name
}
}
If (($NewUsers -ne $null) -and ($NewUsers -ne '')) {
Write-Output $NewUsers
} Else {
Exit 1
}
Exit 0
13 October 2021
Installing Printers via ConfigMgr for Non-Admin Users
KB5005652 resolved the "PrintNightMare" vulnerability, but it also brought many companies to a halt when it came to end-users installing printers if they did not have administrator privileges. During the time since the update, we had our help desk install printers for users on an as-needed basis. There was a workaround by setting the RestrictDriverInstallationToAdministrators to 0 to override the fix, but this would have thwarted the security vulnerability.
I came up with the fix to install printers using configuration manager since it can install them with a privileged account. The solution in my environment was to use PowerShell to query the print servers for a list of offices and printers in our scenario. After the office is selected, it displays a list of printers in that office for the user to select from. Once that printer is selected, it will check if the printer is already installed. If so, it will delete it and reinstall. If not, it will install the new printer. Finally, it will verify that the printer was successfully installed. I currently have the script read from a text file the list of print servers with the associated city location in CSV format. I did make a last-minute change where I hardcoded the locations into the object creation instead of using it from the text file. I will probably find a better way in the near future and update this with something like using Get-ADObject to get a list of the print servers, but will still need to find where the link to the associated city name is to use that method. For now, this is working great in our environment.
This has to be set up in configuration manager as a package advertisement with Allow users to interact with this program checked. This allows users to install a printer on-demand through Software Center and rerun the package as many times as needed.
NOTE: One last important thing. This script was written for our environment. You will need PowerShell knowledge to modify this script to work in your environment. This is more of a primer to show you how I overcame the limitation in this corporate environment. The script will likely need to be greatly modified for some environments. The size of the company will also make a lot of difference in how this script needs to be modified. The firm I am at is roughly 500 people. A GUI interface may be preferential to some, which I may come back later and implement with PowerShell Studio.
You can download the PowerShell script from my Github repository.
<#
.SYNOPSIS
PrinterInstaller
.DESCRIPTION
This script is intended to be used in ConfigMgr as an applicatio advertisement in Software Center. This allows non-admin users to install printers allowing companies to keep the Microsoft print server patch in place. It nwill retrieve all printers from all print servers. It then prompts the user for the office. At that point, it will display a list of printers in that office for the user to select from. Finally, it will check if the printer is already installed. If it is, it will uninstall the printer and proceed to install it, otherwise it will install the printer.
.PARAMETER PrintServersFile
Name of the file which contains a list of print servers
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2021 v5.8.194
Created on: 10/12/2021 7:40 PM
Created by: Mick Pletcher
Filename: PrinterInstaller.ps1
===========================================================================
#>
[CmdletBinding()]
Param
(
[ValidateNotNullOrEmpty()]$PrintServersFile = 'PrintServers.txt'
)
Clear-Host
Write-Host "Retrieving List of Offices..."
#Create Printers array
$Printers = @()
#Get list of print servers that includes the print server and printer location
$PrintServers = Get-Content -Path ($PSScriptRoot + '\' + $PrintServersFile)
#Get list of all printers from within each print server
$PrintServers | ForEach-Object {
#Test if the print server is online before querying it
If ((Test-Connection -ComputerName $_.Split(",")[0] -Count 1 -Quiet) -eq $true) {
#Add all printer from the specified print server
$Query += Get-Printer -ComputerName $_.Split(",")[0]
}
}
#Create the object for each printer and add it to the $Printers array
$Query | ForEach-Object {
$object = New-Object PSObject
$object | Add-Member Noteproperty -Name PrinterName -Value $_.Name
$object | Add-Member Noteproperty -Name PrinterPort -Value $_.PortName
$object | Add-Member Noteproperty -Name PrintServer -Value $_.ComputerName
$object | Add-Member Noteproperty -Name DriverName -Value $_.DriverName
Switch ($_.ComputerName) {
"Printer1" { $object | Add-Member Noteproperty -Name Office -Value "Austin" }
"Printer2" { $object | Add-Member Noteproperty -Name Office -Value "Birmingham" }
"Printer3" { $object | Add-Member Noteproperty -Name Office -Value "Chattanooga" }
"Printer4" { $object | Add-Member Noteproperty -Name Office -Value "Nashville" }
}
#Add a floor value to the Floor object if the print server is in Nashville
If ($_.ComputerName -eq 'Printer4') {
$object | Add-Member Noteproperty -Name Floor -Value ($_.Name.Split("-")[1])
} else {
#Leave the floor object blank if it is any office other than Nashville
$object | Add-Member Noteproperty -Name Floor -Value ""
}
#Add the object to the $Printers array
$Printers += $object
}
#Sort the array by Office and then Floor
$Printers = $Printers | Sort-Object -Property Office, Floor
#Counter for selecting the office
$Count = 1
#Display each office with a number selection
$PrintServers | ForEach-Object {Write-Host ([string]$Count + ' - ' + $_.Split(",")[1]);$Count++}
#Prompt for a user selection of the office
$Selection = Read-Host -Prompt "Select the office"
#Get list of printers for selected office
$PrintersSelection = $Printers | Where-Object {$_.PrintServer -eq ($PrintServers[$Selection - 1].Split(",")[0])}
#printer counter
$Count = 1
Clear-Host
Write-Host
Write-Host "Retrieving list of Printers..."
#Display list of printers in the selected office
$PrintersSelection | ForEach-Object {Write-Host ([string]$Count + ' - ' + $_.PrinterName);$Count++}
#Prompt the user to select the printer
$Selection = Read-Host -Prompt "Select the Printer"
#Display the selected printer
$PrintersSelection[$Selection - 1]
#Check if the printer is installed and uninstall it if true
If ((Get-Printer -Name $PrintersSelection[$Selection - 1].PrinterName -ErrorAction SilentlyContinue) -ne $null) {
Remove-Printer -Name $PrintersSelection[$Selection - 1].PrinterName
Remove-PrinterPort -Name $PrintersSelection[$Selection - 1].PrinterPort
}
Write-Host
Write-Host ('Installing Printer' + [char]32 + $PrintersSelection[$Selection - 1].PrinterName + '.....') -NoNewline
#Install the selected printer
Add-PrinterPort -Name $PrintersSelection[$Selection - 1].PrinterPort -PrinterHostAddress $PrintersSelection[$Selection - 1].PrinterPort
Add-Printer -Name $PrintersSelection[$Selection - 1].PrinterName -DriverName $PrintersSelection[$Selection - 1].DriverName -PortName $PrintersSelection[$Selection - 1].PrinterPort
#Verify the printer was installed
If ((Get-Printer -Name $PrintersSelection[$Selection - 1].PrinterName -ErrorAction SilentlyContinue) -ne $null) {
Write-Host 'success' -ForegroundColor Yellow
} Else {
Write-Host 'failed' -ForegroundColor Red
}
29 June 2021
PowerShell: Install Fonts
<#
.SYNOPSIS
Install Open Text and True Type Fonts
.DESCRIPTION
This script will install OTF and TTF fonts that exist in the same directory as the script.
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2021 v5.8.187
Created on: 6/24/2021 9:36 AM
Created by: Mick Pletcher
Filename: InstallFonts.ps1
===========================================================================
#>
<#
.SYNOPSIS
Install the font
.DESCRIPTION
This function will attempt to install the font by copying it to the c:\windows\fonts directory and then registering it in the registry. This also outputs the status of each step for easy tracking.
.PARAMETER FontFile
Name of the Font File to install
.EXAMPLE
PS C:\> Install-Font -FontFile $value1
.NOTES
Additional information about the function.
#>
function Install-Font {
param
(
[Parameter(Mandatory = $true)][ValidateNotNullOrEmpty()][System.IO.FileInfo]$FontFile
)
#Get Font Name from the File's Extended Attributes
$oShell = new-object -com shell.application
$Folder = $oShell.namespace($FontFile.DirectoryName)
$Item = $Folder.Items().Item($FontFile.Name)
$FontName = $Folder.GetDetailsOf($Item, 21)
try {
switch ($FontFile.Extension) {
".ttf" {$FontName = $FontName + [char]32 + '(TrueType)'}
".otf" {$FontName = $FontName + [char]32 + '(OpenType)'}
}
$Copy = $true
Write-Host ('Copying' + [char]32 + $FontFile.Name + '.....') -NoNewline
Copy-Item -Path $fontFile.FullName -Destination ("C:\Windows\Fonts\" + $FontFile.Name) -Force
#Test if font is copied over
If ((Test-Path ("C:\Windows\Fonts\" + $FontFile.Name)) -eq $true) {
Write-Host ('Success') -Foreground Yellow
} else {
Write-Host ('Failed') -ForegroundColor Red
}
$Copy = $false
#Test if font registry entry exists
If ((Get-ItemProperty -Name $FontName -Path "HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Fonts" -ErrorAction SilentlyContinue) -ne $null) {
#Test if the entry matches the font file name
If ((Get-ItemPropertyValue -Name $FontName -Path "HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Fonts") -eq $FontFile.Name) {
Write-Host ('Adding' + [char]32 + $FontName + [char]32 + 'to the registry.....') -NoNewline
Write-Host ('Success') -ForegroundColor Yellow
} else {
$AddKey = $true
Remove-ItemProperty -Name $FontName -Path "HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Fonts" -Force
Write-Host ('Adding' + [char]32 + $FontName + [char]32 + 'to the registry.....') -NoNewline
New-ItemProperty -Name $FontName -Path "HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Fonts" -PropertyType string -Value $FontFile.Name -Force -ErrorAction SilentlyContinue | Out-Null
If ((Get-ItemPropertyValue -Name $FontName -Path "HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Fonts") -eq $FontFile.Name) {
Write-Host ('Success') -ForegroundColor Yellow
} else {
Write-Host ('Failed') -ForegroundColor Red
}
$AddKey = $false
}
} else {
$AddKey = $true
Write-Host ('Adding' + [char]32 + $FontName + [char]32 + 'to the registry.....') -NoNewline
New-ItemProperty -Name $FontName -Path "HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Fonts" -PropertyType string -Value $FontFile.Name -Force -ErrorAction SilentlyContinue | Out-Null
If ((Get-ItemPropertyValue -Name $FontName -Path "HKLM:\Software\Microsoft\Windows NT\CurrentVersion\Fonts") -eq $FontFile.Name) {
Write-Host ('Success') -ForegroundColor Yellow
} else {
Write-Host ('Failed') -ForegroundColor Red
}
$AddKey = $false
}
} catch {
If ($Copy -eq $true) {
Write-Host ('Failed') -ForegroundColor Red
$Copy = $false
}
If ($AddKey -eq $true) {
Write-Host ('Failed') -ForegroundColor Red
$AddKey = $false
}
write-warning $_.exception.message
}
Write-Host
}
#Get a list of all font files relative to this script and parse through the list
foreach ($FontItem in (Get-ChildItem -Path $PSScriptRoot | Where-Object {
($_.Name -like '*.ttf') -or ($_.Name -like '*.OTF')
})) {
Install-Font -FontFile $FontItem
}
22 January 2021
Configuration Manager Message ID 11170 Error
11 January 2021
Automating the Deletion of Windows.old
It is the beginning of 2021 and my first project for the new year is upgrading all systems to Windows 10 20H2. At the end of the upgrades comes the cleanup and there is no clean way to do this for system admins. Cleanmgr.exe is now deprecated as of Windows 10 2004. There is not a PowerShell option for controlling storage sense. Cleanmgr.exe /AUTOCLEAN would open up cleanmgr.exe and then freeze. It never deleted the folder and was stuck at zero CPU usage. I also tried using PSEXEC to execute it with the same results. Another suggestion was using task scheduler. I also tried using Dism.exe /online /Cleanup-Image /StartComponentCleanup. The Windows.old folder was still present. The next option is to delete the folder. Here is a pic of the failure to clean up the Windows.old folder using a domain admin account and cmd.exe run as administrator.
NOTE: There is an issue we ran into when someone had a USB drive connected. The system connected to the wrong drive. and installed the SMSTaskSequence on that drive instead of the bootable C: drive. This caused the system to reboot constantly because it then did not see the Windows.old directory to delete. I fixed this by adding logic that looked for the Windows.old directory. The new code is below
NOTE: Once this folder has been deleted, Windows cannot be reverted back to the previous version.
Once the upgrade has taken place, the Windows.old folder is present. It typically takes up 15+ GB of space. The upgraded OS is still actively using some files in the directory as I learned while exploring how to delete them. I found the files being used were drivers. This prevents you from deleting it while the OS is in memory. The alternative is to load WinPE so the OS is not in memory thereby freeing up the directory for deletion. Once the directory is deleted in WinPE and the system reboots, the OS reassociates the drivers it was using in the Windows.old directory to the Windows directory.
I first tried using PowerShell to delete the directory and there was a constant problem. PowerShell could not delete all files and directories, no matter what I tried. I consistently got the message "No mapping between account names and security IDs was done." Next, I tried RMDIR and it worked perfectly. I was able to use PowerShell to both find the drive in the WinPE environment and then execute the RMDIR command to delete the Windows.old directory on that drive. I tried this as a one-liner, but it was hit and miss with the RMDIR accepting the piped output instead of a variable. I found storing the path to the Windows.old directory in a variable was much more reliable. Here is the two-line code below:
$Drive = (Get-Partition | Where-Object {((Test-Path ($_.DriveLetter + ':\Windows.old')) -eq $True)}).DriveLetter
If ((Test-Path ($Drive + ':\Windows.old')) -eq $true) {
$Directory = $Drive + ':\Windows.old'
cmd.exe /c rmdir /S /Q $Directory
}
Here is a screenshot of the task sequence I used to deploy to the systems. The first restart is to load WinPE, the second sequence is the above PowerShell script, and the third sequence reboots the system back into the installed OS.
25 September 2020
Check Boot Environment for BIOS or UEFI
One of my recent projects is to convert our remaining legacy systems from BIOS to UEFI. While setting up the task sequence, I needed to be able to test the system to make sure it was not already UEFI so the task sequence would end if it was.
The PowerShell script reads the setupact.log file and extracts if it is configured as BIOS or UEFI. I have included an unknown message in the event the log file does not exist or is inaccessible, which is what I encountered on one machine. For setting it up in a Configuration Manager task sequence, I set the sequence to look for a return code of 0, else it will return either a 1 or 2, which will fail the task sequence, and allow the admin to know why it failed from the console with the error code being returned.
You can download the script from my GitHub site.
<#
.SYNOPSIS
Check Boot Environment
.DESCRIPTION
This script reads the setupact.log file to determine if the system is configured for BIOS or UEFI.
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142
Created on: 9/25/2020 11:59 AM
Created by: Mick Pletcher
Filename: BootEnvironment.ps1
===========================================================================
#>
Try {
$Output = (Get-Content -Path (((Get-ChildItem -Path ($env:windir + '\Panther') -Recurse -Filter setupact.log -ErrorAction SilentlyContinue)[0]).FullName) -ErrorAction SilentlyContinue | Where-Object {$_ -like "*Detected boot environment*"}).Replace("Detected boot environment:", "~").Split("~")[1].Trim()
If ($Output -eq 'BIOS') {
Write-Output 'BIOS'
Exit 0
} elseif ($Output -eq 'UEFI') {
Write-Output 'UEFI'
Exit 1
}
} Catch {
Write-Output 'Unknown'
Exit 2
}
24 September 2020
Bitlocker Non-Compliance Reporting
As part of the suite of security tools I am writing, this will query the configuration manager SQL database for a list of machines that are not Bitlocker encrypted. There are reports in the configuration manager for this, but not everyone in my organization has access to the configuration manager console, and we wanted a detailed report sent out on a regular basis so that it is in their mailbox with high importance, which is also not available through ConfigMgr.
This tool was written to include the computer name, model, chassis, drive letter, bitlocker status, last hardware inventory scan, and last logon time. The last hardware scan and last logon time give the admins an idea as to the accuracy of the system being reported. The tool was written so that it can be used with Azure Automation or Orchestrator, and even with a scheduled task if needed. The output is formatted for a clean appearance in an outlook email.
You can download the script from my GitHub site.
<#
.SYNOPSIS
Bitlocker Encryption Reporting
.DESCRIPTION
This script queries the Configuration Manager SQL database for a list of machines that are not Bitlocker Encrypted. It is limited to non-desktop chassis, which can be changed by modifying the SQL query. The script is designed to output the data so that it can be used with Orchestrator, Azure Automation, or a scheduled task.
.PARAMETER SQLServer
Name of the SQL server
.PARAMETER SQLDatabase
Name of the SQL database
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142
Created on: 9/21/2020 3:35 PM
Created by: Mick Pletcher
Filename: BitlockerEncryptionReporting.ps1
===========================================================================
#>
[CmdletBinding()]
param
(
[ValidateNotNullOrEmpty()]
[string]$SQLServer,
[ValidateNotNullOrEmpty()]
[string]$SQLDatabase
)
$Query = "SELECT ResultTableName FROM dbo.v_Collections"
$Collection = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query $Query
#SQL query to retrieve the list of machines
$Query = "SELECT DISTINCT Name as ComputerName, dbo.Computer_System_DATA.Model00 AS Model, dbo.System_Enclosure_DATA.ChassisTypes00 AS Chassis, dbo.ENCRYPTABLE_VOLUME_DATA.DriveLetter00 AS DriveLetter, ProtectionStatus00 AS BitlockerStatus, LastHardwareScan, ADLastLogonTime AS LastLogonTime FROM dbo.Computer_System_DATA INNER JOIN dbo.ENCRYPTABLE_VOLUME_DATA ON dbo.Computer_System_DATA.MachineID = dbo.ENCRYPTABLE_VOLUME_DATA.MachineID INNER JOIN dbo.v_GS_ENCRYPTABLE_VOLUME ON dbo.v_GS_ENCRYPTABLE_VOLUME.ResourceID = dbo.ENCRYPTABLE_VOLUME_DATA.MachineID INNER JOIN dbo._RES_COLL_SMS00001 ON dbo.ENCRYPTABLE_VOLUME_DATA.MachineID = dbo._RES_COLL_SMS00001.MachineID INNER JOIN dbo.System_Enclosure_DATA ON dbo._RES_COLL_SMS00001.MachineID = dbo.System_Enclosure_DATA.MachineID INNER JOIN dbo.v_GS_VOLUME ON dbo.System_Enclosure_DATA.MachineID = dbo.v_GS_VOLUME.ResourceID WHERE (((dbo.System_Enclosure_DATA.ChassisTypes00 = 8) OR (dbo.System_Enclosure_DATA.ChassisTypes00 = 9) OR (dbo.System_Enclosure_DATA.ChassisTypes00 = 10) OR (dbo.System_Enclosure_DATA.ChassisTypes00 = 12) OR (dbo.System_Enclosure_DATA.ChassisTypes00 = 14) OR (dbo.System_Enclosure_DATA.ChassisTypes00 = 31)) AND (dbo.ENCRYPTABLE_VOLUME_DATA.DriveLetter00 = 'C:') AND (dbo.ENCRYPTABLE_VOLUME_DATA.ProtectionStatus00 = 0)) ORDER BY Name"
$Report = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query $Query
#If the report has no machines, then exit this script with an error code 1 so that the automation tool the link will not continue to the email task
If ($Report -ne $null) {
$Array = @()
foreach ($Item in $Report) {
$SysObj = New-Object -TypeName System.Management.Automation.PSObject
$SysObj | Add-Member -MemberType NoteProperty -Name ComputerName -Value $Item.ComputerName
$SysObj | Add-Member -MemberType NoteProperty -Name Model -Value $Item.Model
$SysObj | Add-Member -MemberType NoteProperty -Name Chassis -Value $Item.Chassis
$SysObj | Add-Member -MemberType NoteProperty -Name DriveLetter -Value $Item.DriveLetter
$SysObj | Add-Member -MemberType NoteProperty -Name BitlockerStatus -Value $Item.BitlockerStatus
$SysObj | Add-Member -MemberType NoteProperty -Name LastHardwareScan -Value $Item.LastHardwareScan
$SysObj | Add-Member -MemberType NoteProperty -Name LastLogonTime -Value $Item.LastLogonTime
$Array += $SysObj
}
#Bitlocker reporting fields from the query
$Fields = @("Computer Name", "Model", "Chassis", "Drive Letter", "Bitlocker Status", "Last Hardware Scan", "Last Logon Time")
#Title row
$Output = ($Fields[0] + [char]9 + [char]9 + $Fields[1] + [char]9 + [char]9 + [char]9 + [char]9 + $Fields[2] + [char]9 + [char]9 + $Fields[3] + [char]9 + $Fields[4] + [char]9 + [char]9 + $Fields[5] + [char]9 + $Fields[6] + [char]13)
#Add each entry while formatting the computername column as to the width of the computername
foreach ($Item in $Array) {
If ($Item.ComputerName.Length -le 3) {
$ComputerName = $Item.ComputerName + [char]9 + [char]9 + [char]9 + [char]9 + [char]9
} elseif ($Item.ComputerName.Length -le 7) {
$ComputerName = $Item.ComputerName + [char]9 + [char]9 + [char]9 + [char]9
} elseif ($Item.ComputerName.Length -le 11) {
$ComputerName = $Item.ComputerName + [char]9 + [char]9 + [char]9
} elseif ($Item.ComputerName.Length -le 15) {
$ComputerName = $Item.ComputerName + [char]9 + [char]9
} else {
$ComputerName = $Item.ComputerName + [char]9
}
$Output += $ComputerName + $Item.Model + [char]9 + [char]9 + [char]9 + $Item.Chassis + [char]9 + [char]9 + $Item.DriveLetter + [char]9 + [char]9 + $Item.BitlockerStatus + [char]9 + [char]9 + [char]9 + $Item.LastHardwareScan + [char]9 + $Item.LastLogonTime + [char]13
}
#Write the output so it can be collected from the automation tool
Write-Output -InputObject $Output
} else {
Exit 1
}
15 September 2020
Preventing Windows 10 Apps from Reappearing after an In-Place or Feature Upgrade
Get-AppxProvisionedPackage -Online | Select DisplayName, PackageName | Sort-Object -Property DisplayName | Export-Csv -Path Apps.txt -Force -Encoding UTF8 -NoTypeInformation
It is best to edit the CSV file in excel. Once you open the generated text file up, delete those items you do NOT want the OS to remove, and then delete the DisplayName column. The CSV file is now ready to be used with the PowerShell script. You will need to rerun the above PowerShell script each time an OS upgrade is available and has been installed to get any new additions.
<#
.SYNOPSIS
Deprovision Specified Windows 10 Apps
.DESCRIPTION
This script will add the appropriate registry keys to deprovision the built-in Windows 10 applications specified in the associated text file, or hardcoded in the script.
.PARAMETER AppListFile
File containing list of Windows 10 files to deprovision
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142
Created on: 9/15/2020 9:56 AM
Created by: Mick Pletcher
Filename: Windows10AppDeprovisioning.ps1
===========================================================================
#>
[CmdletBinding()]
param
(
[string]$AppListFile
)
#Get list of Windows 10 Applications to uninstall from text file
$Applications = Get-Content -Path $AppListFile
#Deprovisioned Registry Key
$RegKey = 'REGISTRY::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Appx\AppxAllUserStore\Deprovisioned'
#Create deprovisioned registry key if it does not exist
If (!(Test-Path $RegKey)) {
New-Item -Path $RegKey -Force | Out-Null
}
#Add list of Apps from the imported text file to the deprovisioned registry key
foreach ($App in $Applications) {
#Install registry key if it does not exist
If (!(Test-Path ($RegKey + '\' + $App))) {
New-Item -Path ($RegKey + '\' + $App) -Force | Out-Null
}
}
09 September 2020
Remotely Pushing Windows Updates via Command Line to Windows 10 Machines
Normally, windows updates are pushed to machines using Configuration Manager in an enterprise environment. There are occasions though when they must manually be pushed, such as when a system continues to fail via ConfigMgr and troubleshooting is required. The first tool I use is the PSWindowsUpdate PowerShell module. This allows me to remotely push updates via PowerShell and PSEXEC, in which I can watch the results.
To use the PSWindowsUpdate module, it must be installed first. I do so by installing the module on all machines during the build process, but it can also be pushed through ConfigMgr or you can use PSEXEC.exe to install it. The command line I use is:
powershell.exe -executionpolicy bypass -command "&{Install-Module -Name PSWindowsUpdate -Force -Confirm:$false}"
Install-WindowsUpdate -IgnoreReboot -AcceptAll;Start-Process -FilePath ($env:windir + '\system32\UsoClient.exe') -ArgumentList 'ScanInstallWait StartInstall'
The issue I have seen with just using the PSWindowsUpdate module with 1903 and later versions of Windows 10 is that it will trigger the updates to download, but will not always install them, especially feature updates. The UsoClient.exe will trigger the updates to install. This command-line executable is the same as going to the Check for Windows Updates screen and clicking the button to install updates. You can find more info on UsoClient here. 01 September 2020
AD Group Member Reporting
This tool queries specified AD groups for new users that have been added to the group within a specified number of days. The script is written so that it can be used with Azure Automation, Orchestrator, or even a scheduled task, with the addition of the send-mailmessage cmdlet. The intent of this is to track if say a user needs to be temporarily added to the domain admins group for software installation, but then the help desk forgets to remove them. It can also be a tracking tool in the event a cyber-incursion is happening and a compromised account gets added to that group. Obviously, if a user who is already in that group is compromised, then this script does not impact that type of event. This script was made possible by using the function posted over at PowerShellBros with a few modifications to it.
The script exits with a write-output statement if any new users appear in the report. The Write-Output allows for the data to be sent to the next step in say Orchestrator. If no new users appear, then the script exits with an error code 1. This is so the link in Azure Automation or Orchestrator can be set to only proceed to send an email if there was an error code 0, which would be the Write-Output statement.
You can download the script from my GitHub repository located here.
<#
.SYNOPSIS
AD Security Group User Additions
.DESCRIPTION
This script retrieves a list of users added to a specified AD security group, which includes the date it was last modified. This info can be used to track whether a new user has been recently added to a security group, especially a group that elevates priviledges. This can be used as a tool to help fight cyber-crime.
.PARAMETER NumberOfDays
Number of days to look back for users having been added to the designated AD security group
.PARAMETER SecurityGroup
Name of the AD security group
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142
Created on: 9/1/2020 11:03 AM
Created by: Mick Pletcher
Filename: ADGroupUserInfo.ps1
===========================================================================
#>
[CmdletBinding()]
param
(
[ValidateNotNullOrEmpty()]
[int]$NumberOfDays,
[ValidateNotNullOrEmpty()]
[string]$SecurityGroup
)
Function Get-ADGroupMemberDate {
[cmdletbinding()]
Param (
[parameter(ValueFromPipeline = $True, ValueFromPipelineByPropertyName = $True, Mandatory = $True)]
[string]$Group
)
Begin {
[regex]$pattern = '^(?<State>\w+)\s+member(?:\s(?<DateTime>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})\s+(?:.*\\)?(?<DC>\w+|(?:(?:\w{8}-(?:\w{4}-){3}\w{12})))\s+(?:\d+)\s+(?:\d+)\s+(?<Modified>\d+))?'
$DomainController = ($env:LOGONSERVER -replace "\\\\")
If (!$DomainController) {
Throw "Computer from which script is run is not joined to domain and domain controller can not be identified."
Break
}
}
Process {
Write-Verbose "Checking distinguished name of the group $Group"
Try {
$distinguishedName = (Get-ADGroup -Identity $Group).DistinguishedName
} Catch {
Write-Warning "$group can not be found!"
Break
}
$RepadminMetaData = (repadmin /showobjmeta $DomainController $distinguishedName | Select-String "^\w+\s+member" -Context 2)
$Array = @()
ForEach ($rep in $RepadminMetaData) {
If ($rep.line -match $pattern) {
$object = New-Object PSObject -Property @{
Username = [regex]::Matches($rep.context.postcontext, "CN=(?<Username>.*?),.*") | ForEach-Object {
$_.Groups['Username'].Value
}
LastModified = If ($matches.DateTime) {
[datetime]$matches.DateTime
} Else {
$Null
}
DomainController = $matches.dc
Group = $group
State = $matches.state
ModifiedCounter = $matches.modified
}
$Array += $object
}
}
Return $Array
}
End {
}
}
$Users = Get-ADGroupMemberDate -Group $SecurityGroup | Select Username, LastModified
$NewUsers = @()
#Find users added to the AD Group within the designated number of days
Foreach ($User in $Users) {
If ((New-TimeSpan -Start $User.LastModified -End (Get-Date)).Days -lt $NumberOfDays) {
$NewUsers += $User
}
}
If ($NewUsers.Count -gt 0) {
Write-Output $NewUsers
} else {
Exit 1
}
28 August 2020
Multiple Machine Logon Reporting
- Detect a cybercriminal who may have gotten one user credential and is now using it to probe machines across the domain
- Detect a disgruntled employee who may be logging into multiple machines for destructive reasons
SELECT UniqueUserName, COUNT(UniqueUserName) as Logins FROM dbo.v_UserMachineRelationship WHERE dbo.v_UserMachineRelationship.CreationTime >= DATEADD(day,-14, GETDATE()) GROUP BY UniqueUserName HAVING (COUNT(UniqueUserName) > 4) ORDER BY Logins DESC
<#
.SYNOPSIS
SCCM Endpoint Report
.DESCRIPTION
This script is intended to be used to detect if a user logs into multiple machines within a designated time period, which can be a sign of an intruder.This script will query Configuration Manager using the designated Number of machines and how many days back for a list of machines that have logged into more than the designated number. It will return a list of machines using Write-Output so the output can also be used in Orchestrator or SMA. The list it returns is a simple list that displays the username and the number of machines it was logged into. There is also a more detailed list generated that contains the list of all machines it was logged into. This list can be attached to an email to be sent with the simple list if desired.
.PARAMETER SQLServer
Name of the SQL server
.PARAMETER SQLDatabase
Name of the SQL database
.PARAMETER NumberOfMachines
Maximum number of machines a user can login to before appearing on this report
.PARAMETER NumberOfDays
This is a string value because it requires a negative value for the number of days you want to report to go back.
.PARAMETER Report
This is the specified name of the detailed user login report to be generated. This must include both the full path and .csv as part of the name.
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142
Created on: 8/19/2020 3:58 PM
Created by: Mick Pletcher
Filename: UserLoginReport.ps1
===========================================================================
#>
[CmdletBinding()]
param
(
[ValidateNotNullOrEmpty()]
[string]$SQLServer,
[ValidateNotNullOrEmpty()]
[string]$SQLDatabase,
[ValidateNotNullOrEmpty()]
[int]$NumberOfMachines,
[ValidateNotNullOrEmpty()]
[string]$NumberOfDays,
[ValidateNotNullOrEmpty()]
[string]$Report = 'c:\UserLoginDetailedReport.csv'
)
$SimpleReport = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query ("SELECT UniqueUserName, COUNT(UniqueUserName) as Logins FROM dbo.v_UserMachineRelationship WHERE dbo.v_UserMachineRelationship.CreationTime >= DATEADD(day," + $NumberOfDays + ", GETDATE()) GROUP BY UniqueUserName HAVING (COUNT(UniqueUserName) > " + $NumberOfMachines + ") ORDER BY Logins DESC")
If ($SimpleReport -ne $null) {
$DetailedReport = @()
foreach ($User in $SimpleReport.UniqueUserName) {
$DetailedReport += Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query ("SELECT UniqueUserName, MachineResourceName, CreationTime FROM dbo.v_UserMachineRelationship WHERE UniqueUserName = " + [char]39 + $User.Trim() + [char]39 + " AND dbo.v_UserMachineRelationship.CreationTime >= DATEADD(day," + $NumberOfDays + ", GETDATE()) ORDER BY CreationTime ASC")
}
$DetailedReport | Export-Csv -Path $Report -Force -Encoding UTF8 -NoTypeInformation
Write-Output $SimpleReport
} else {
Remove-Item -Path $Report -Force -ErrorAction SilentlyContinue
Exit 1
}
27 August 2020
Which Configuration Manager PowerShell Module should I use?
If you use PowerShell with Configuration Manager, you likely know that there are two PowerShell modules named ConfigurationManager.psd1. One is located at %PROGRAMFILES%\Microsoft Configuration Manager\AdminConsole\bin and the other one is located at %PROGRAMFILES%\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager. If you are trying to figure out which one to use, the %PROGRAMFILES%\Microsoft Configuration Manager\AdminConsole\bin is the most logical one. The other module just references the first one as a nested module with no additional benefits.
21 August 2020
Deleting the Windows 10 Recovery Partition
We use VMWare for our servers and sometimes virtualized desktops. One of the issues we ran into was expanding the disk space on a virtual machine. When disk space was added, the C: drive could not be expanded because of the reserve drive "being in the way". The additional space would have to be made into a separate drive.
To fix this issue, I deleted the Windows 10 recovery partition, which on all of our builds is the fourth partition. Our VMWare virtual machines are backed up in real-time, thereby doing away with the need for the recovery drive It is important to go into diskpart and make sure your machines are built and configured like ours. This will not work if they are not. The picture below is how all of our systems are partitioned during the build process. I have integrated the PowerShell one-liner below as a task sequence to run
The following PowerShell one-liner will delete the partition 4.
Remove-Partition -DiskNumber ((Get-Disk).Number) -PartitionNumber ((Get-Partition -DiskNumber ((Get-Disk).Number) | Where-Object {$_.Type -eq 'Recovery'}).PartitionNumber) -PassThru -Confirm:$false
12 June 2020
Legacy Distribution Point Cleanup
<#
.SYNOPSIS
ConfigMgr Distribution Point Cleanup
.DESCRIPTION
This script will cleanup the remnants of a distribution point after it has been deleted in Configuration Manager. This is sometimes necessary when needing to repush a distribution point to the same server.
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142
Created on: 6/12/2020 2:01 PM
Created by: Mick Pletcher
Filename: DPCleanup.ps1
===========================================================================
#>
[CmdletBinding()]
param ()
#Uninstall ConfigMgr Client
If ((Test-Path ($env:windir + '\ccmsetup\ccmsetup.exe')) -eq $true) {
Start-Process ($env:windir + '\ccmsetup\ccmsetup.exe') -ArgumentList "/uninstall" -Wait
}
#Retrieve the path to the distribution point directories
If ((Test-Path 'REGISTRY::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\DP') -eq $true) {
$ContentLibraryPath = ((Get-ItemProperty -Path 'REGISTRY::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\DP').ContentLibraryPath).split('\')[0] + '\'
} else {
Write-Host 'Cannot find path to distribution point content'
Exit 1
}
#Delete the distribution point directories
(Get-ChildItem -Path $ContentLibraryPath | Where-Object {($_.Name -like 'SMS*') -or ($_.Name -like 'SCCM*')}) | ForEach-Object {Remove-item -Path $_.FullName -Recurse -Force}
#Delete registry keys associated with ConfigMgr
If ((Test-Path "REGISTRY::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS") -eq $true) {
Remove-Item -Path "REGISTRY::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS" -Recurse -Force -ErrorAction SilentlyContinue
}
19 May 2020
Using PowerShell to list Zerto Unprotected Systems
You will need to install both the activedirectory and zertoapiwrapper modules before using this script.
You can download the script from my GitHub site.
<#
.SYNOPSIS
Unprotected Systems Report
.DESCRIPTION
This script will retrieve a list of systems from the Zerto server that are not being backed up. Desktop operating systems and systems not in AD are excluded, as Zerto is typically used for server backup.
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142
Created on: 5/19/2020 5:24 PM
Created by: Mick Pletcher
Filename: ZertoUnprotectedSystems.ps1
===========================================================================
#>
[CmdletBinding()]
param ()
Import-Module -Name zertoapiwrapper
Import-Module -Name activedirectory
$Cred = New-Object System.Management.Automation.PSCredential ("domain\username", $password)
Connect-ZertoServer -zertoServer prodzerto.wallerlaw.int -credential $Cred
$List = (Get-ZertoUnprotectedVm).VmName | Sort-Object
$UnprotectedServers = @()
Foreach ($System in $List) {
Try {
$ADComputer = Get-ADComputer $System -Properties OperatingSystem
If (($ADComputer.OperatingSystem -notlike '*Windows 7 Enterprise') -and ($ADComputer.OperatingSystem -notlike '*Windows 10 Enterprise')) {
$UnprotectedServers += $System
}
} catch {
}
}
#Use this if the script is being used in Azure Automation or Orchestrator
Write-Output $UnprotectedServers
#Use this if the script is being executed manually, or as a scheduled task.
#Send-MailMessage -From '' -To '' -Subject 'Zerto Unprotected Servers' -Body ($UnprotectedServers | Out-String) -Priority High -SmtpServer ''
12 May 2020
Populate VMWare Virtual Systems to a ConfigMgr Collection
NOTE: You will need to download and install the VMWare PowerShell module before executing this script. The following cmdlet will install it.
Find-Module -Name VMWare.PowerCLI | Install-Module -force -AllowClobber
You can download this script from my GitHub site.
<#
.SYNOPSIS
Add VMWare systems to specified ConfigMgr collection
.DESCRIPTION
This script will query the VMware server for a list of machines. It will then query Configuration Manager to verify which of those machines are present. Finally, it adds the list of machines to a collection, which is assigned to a specific distribution point.
.PARAMETER ServerCollection
List of servers in Configuration Manager to compare with the list of servers from VMWare.
.PARAMETER ConfigMgrSiteCode
Three letter Configuration Manager site code
.PARAMETER DistributionPointCollection
Name of the collection containing a list of systems on the
.PARAMETER VMWareServer
Name of the VMWare server
.PARAMETER ConfigMgrModule
UNC path including file name of the configuration manager module
.PARAMETER ConfigMgrServer
FQDN of SCCM Server
.PARAMETER ConfigMgrSiteDescription
Description of the ConfigMgr Server
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142
Created on: 5/11/2020 9:50 AM
Created by: Mick Pletcher
Filename: VMWareConfigMgr.ps1
===========================================================================
#>
[CmdletBinding()]
param
(
[ValidateNotNullOrEmpty()]
[string]$AllServersCollection = '',
[ValidateNotNullOrEmpty()]
[string]$ConfigMgrSiteCode = '',
[ValidateNotNullOrEmpty()]
[string]$DistributionPointCollection = '',
[ValidateNotNullOrEmpty()]
[string]$VMWareServer = '',
[ValidateNotNullOrEmpty()]
[string]$ConfigMgrModule = '',
[ValidateNotNullOrEmpty()]
[string]$ConfigMgrServer = '',
[ValidateNotNullOrEmpty()]
[string]$ConfigMgrSiteDescription = ''
)
#Import PowerCLI module
Import-Module -Name VMWare.PowerCLI -ErrorAction SilentlyContinue | Out-Null
#Collection ID of the collection containing a list of all servers
$CollectionID = (Get-WmiObject -Namespace ("root\SMS\site_" + $ConfigMgrSiteCode) -Query ("select * from SMS_Collection Where SMS_Collection.Name=" + [char]39 + $AllServersCollection + [char]39) -ComputerName PRODCM).MemberClassName
#Retrieve the list of all servers
$MEMCMServerList = Get-WmiObject -Namespace ("root\SMS\site_" + $ConfigMgrSiteCode) -Query "select * FROM $CollectionID" -ComputerName PRODCM | Sort-Object -Property Name
#Establish a connection with teh VMWare server
Connect-VIServer -Server $VMWareServer | Out-Null
$VerifiedServers = @()
#Get the list of servers from the VMWare server that are also in Configuration Manager
(Get-VM).Name | Sort-Object | ForEach-Object {
If ($_ -in $MEMCMServerList.Name) {
$VerifiedServers += $_
}
}
#CollectionID of the collection containing a list of all servers in VMWare
$CollectionID = (Get-WmiObject -Namespace ("root\SMS\site_" + $ConfigMgrSiteCode) -Query ("select * from SMS_Collection Where SMS_Collection.Name=" + [char]39 + $DistributionPointCollection + [char]39) -ComputerName PRODCM).MemberClassName
#Retrieve list of all servers in VMWare ConfigMgr collection
$MEMCMServerList = Get-WmiObject -Namespace ("root\SMS\site_" + $ConfigMgrSiteCode) -Query "select * FROM $CollectionID" -ComputerName PRODCM | Sort-Object -Property Name
$MissingSystems = @()
#Retrieve the list of servers that are not in the ConfigMgr collection
$VerifiedServers | ForEach-Object {
If ($_ -notin $MEMCMServerList.Name) {
$MissingSystems += $_
}
}
If ($MissingSystems -ne $null) {
#Import ConfigMgr PowerShell Module
Import-Module -Name $ConfigMgrModule -Force
#Map drive to ConfigMgr server
New-PSDrive -Name $ConfigMgrSiteCode -PSProvider 'AdminUI.PS.Provider\CMSite' -Root $ConfigMgrServer -Description $ConfigMgrSiteDescription | Out-Null
#Change current directory to ConfigMgr mapped drive
Set-Location -Path ($ConfigMgrSiteCode + ':')
#Add missing systems to the specified collection
$MissingSystems | ForEach-Object {
Add-CMDeviceCollectionDirectMembershipRule -CollectionName $DistributionPointCollection -ResourceID (Get-CMDevice -Name $_).ResourceID
}
}
05 May 2020
Configuration Manager 1910 Upgrade Tips and Issues I Encountered
- The first one was a warning that read 'configuration for SQL server memory usage'. I needed at least 8 GB of RAM allocated to the SQL database. The following SQL query executed on the ConfigMgr SQL server will rectify that issue.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'min server memory', 8192;
GO
RECONFIGURE;
GO
- I ran into the SQL server native client version warning. I fixed that by downloading the SQL Server 2012 Feature Pack which contains sqlncli.msi. That is the native client installer. SQL Server 2019 is the version being used for ConfigMgr 1910. The sqlncli.msi from the 2012 Feature Pack is compatible with SQL Server 2019.
- When I started using the job migration tool, it seemed to migrate the packages and applications without issue until I went to push them to the new distribution points. That is when I saw the distribution status was still present in the pie charts of the content status. I had to go back and delete the imported items, and then remove them from the distribution points of the old server before migrating them back to the new ConfigMgr server. Once I did that, the newly migrated objects no longer showed false pie charts.
- The final error I got was 'failed to create virtual directory' when I began building my new distribution points. I did not use the old ones, as I wanted them to all be newly built like the ConfigMgr and SQL servers were. The fix to this was to reboot the distribution point server. Once it came back up, the error was gone.
- I did not know whether I should trying and change the configuration manager clients to point to the new server, or just reinstall them. I ended up running the install client and checking the box to uninstall the old client. That worked perfectly. I chose this method because it would then have all new logs in the %windir%\ccm\logs directory for the new client and nothing left over from the old client.
01 May 2020
ConfigMgr Pending Reboot Report
I could not find any PowerShell cmdlets in the ConfigMgr module for viewing a pending restart. Thankfully, Eswar Koneti's blog shows the information is stored in sms_combineddeviceresources.
After learning that part, I decided it would be dramatically faster to query the SQL database directly. The table the information resides in is dbo.vSMS_CombinedDeviceResources and is under ClientState. ClientState will have a value of zero if no pending reboot, and a value between 2 and 15 if there is a pending reboot. The list of those values is in the above blog link.
In the PowerShell script below, there are three parameters you will need to populate. The first is $Collection that contains the name of the collection you want to query. $SQLServer is the name of the SQL server. Finally, $SQLDatabase is the name of the ConfigMgr SQL database. You can populate them either at the command line, or hard code the data in the parameter field of the script.
I wrote the script in a way that it can be easily implemented into Azure Automation, SMA, or Orchestrator. The script will output the list of machines waiting for a reboot using the Write-Output and Exit with a return code of 1 if there is nothing to report. The exit code 1 is used with Orchestrator or SMA for the Link between the PowerShell script and the email. The link would not continue to the email task if there were an exit code of 1, as shown below.
NOTE: For this to access the SQL server, the script must be executed on the SQL server, or on a machine that has the SQL console. This is required, so PowerShell has access to the SQL PowerShell module.
You can download this script from my GitHub site.
<#
.SYNOPSIS
ConfigMgr Reboot Report
.DESCRIPTION
This script will query ConfigMgr for a list of machines that are waiting for a reboot.
.PARAMETER Collection
Name of the collection to query
.PARAMETER SQLServer
Name of the SQL server
.PARAMETER SQLDatabase
A description of the SQLDatabase parameter.
.PARAMETER SQLInstance
Name of the SQL Database
.PARAMETER SCCMFQDN
Fully Qualified Domain Name of the ConfigMgr server
.NOTES
===========================================================================
Created with: SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142
Created on: 05/01/2020 09:39 AM
Created by: Mick Pletcher
Filename: ConfigMgrRebootReport.ps1
===========================================================================
#>
[CmdletBinding()]
param
(
[ValidateNotNullOrEmpty()]
[string]$Collection = '',
[ValidateNotNullOrEmpty()]
[string]$SQLServer = '',
[ValidateNotNullOrEmpty()]
[string]$SQLDatabase = ''
)
$RebootListQuery = 'SELECT * FROM dbo.vSMS_CombinedDeviceResources WHERE ClientState <> 0'
$RebootList = (Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query $RebootListQuery).Name | Sort-Object
$CollectionQuery = 'SELECT * FROM' + [char]32 + 'dbo.' + ((Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query ('SELECT ResultTableName FROM dbo.v_Collections WHERE CollectionName = ' + [char]39 + $Collection + [char]39)).ResultTableName)
$CollectionList = (Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query $CollectionQuery).Name | Sort-Object
$List = @()
$RebootList | ForEach-Object { If ($_ -in $CollectionList) { $List += $_ } }
If ($List -ne '') {
Write-Output $List
} else {
Exit 1
}