Tag Archives: Powershell

[Powershell] Querying ServiceNow via REST

Compiling reports for ServiceNow is a pain with its built-in report builder, since by default it will only return 10,000 records.  Fortunately, they provide a REST API.  This script will page 1000 records at a time, both for ServiceNow record limitations, and avoiding HTTP timeouts.

$snUrl = "https://your-company.service-now.com"
$table = "table_you_want"
[int]$limit = 1000
$snApi = "api/now/v1"
$sncred = Get-Credential
[int]$numRecords = (Invoke-RestMethod -Uri $("$snUrl/$snApi/stats/$table" + "?sysparm_count=true") -Credential $sncred).result.stats.count
$numIterations = [int][math]::Ceiling(($numRecords / $limit))
$jsonTable = $null
for ($i=0; $i -lt $numIterations; $i++){
    $offset = $i * $limit
    $jsonTable += (Invoke-RestMethod -Uri $("$snUrl/$snApi/table/$table" + "?sysparm_offset=$offset&sysparm_limit=$limit") -Credential $sncred).result

[Powershell] SCCM Client Health “Super Query”

I was asked to create a report on devices in AD and SCCM, and report on whether or not AD devices were in the SCCM database, and if so, what their health status was. 99% of the credit goes to Trevor Jones for the SQL portion, which I made a couple tweaks to.  The SQL query is part of his excellent client health spreadsheet, which can be found here.  Huge thank you to Trevor, who gave me permission to republish his query!

Simply define your domain(s), SCCM database server(s), and the time threshold that constitutes activity (when client was last active in AD).

For this script, you will need the RSAT tools, the SQL Server Powershell Provider, and Join-Object by RamblingCookieMonster.

$arrDomains = @("sub1.domain.com","sub2.domain.com","sub3.domain.com")
$arrSccmDbs = @("sccm1.domain.com","sccm2.domain.com")
$timeThreshold = 30

#Unfortunately Arraylists do not work with Join-Object. 
#$arrAD = New-Object -TypeName System.Collections.Arraylist
#$arrSccm = New-Object -TypeName System.Collections.Arraylist
$arrAD = @()
$arrSccm = @()
$daysAgo = $(get-date).AddDays($timeThreshold * -1)
foreach ($domain in $arrDomains) {
    $strSearchBase = 'DC=' + $($domain -replace '\.',',DC=')
    $arrAD += Get-ADComputer -Filter * -SearchBase $strSearchBase -Server $domain -Properties Name, SID, description, OperatingSystem, OperatingSystemVersion, lastLogonDate, lastLogonTimeStamp, lastLogon, whenChanged, Enabled `
    | Select Name, @{N='SID'; E={$_.SID.Value}}, description, OperatingSystem, OperatingSystemVersion, @{N='LastLogonTimeStamp'; E={[DateTime]::FromFileTime($_.LastLogonTimeStamp)}}, @{N='LastLogon'; E={[DateTime]::FromFileTime($_.LastLogon)}}, whenChanged, Enabled `
    | ? {($_.OperatingSystem -notlike "*Server*" -and $_.OperatingSystem -like "*Windows*") -and ($_.LastLogonTimeStamp -ge $daysAgo -or $_.LastLogon -ge $daysAgo -or $_.whenChanged -ge $daysAgo) -and $_.Enabled}
$query = "select
sys.SID0 as 'SID',
sys.Name0 as 'Computer Name',
bios.SerialNumber0 as 'Serial Number',
sys.User_Name0 as 'User Name',
case when summ.ClientActiveStatus = 0 then 'Inactive'
       when summ.ClientActiveStatus = 1 then 'Active'
       end as 'ClientActiveStatus',
max(summ.LastActiveTime) AS LastActiveTime,
case when summ.IsActiveDDR = 0 then 'Inactive'
       when summ.IsActiveDDR = 1 then 'Active'
       end as 'IsActiveDDR',
case when summ.IsActiveHW = 0 then 'Inactive'
       when summ.IsActiveHW = 1 then 'Active'
       end as 'IsActiveHW',
case when summ.IsActiveSW = 0 then 'Inactive'
       when summ.IsActiveSW = 1 then 'Active'
       end as 'IsActiveSW',
case when summ.ISActivePolicyRequest = 0 then 'Inactive'
       when summ.ISActivePolicyRequest = 1 then 'Active'
       end as 'ISActivePolicyRequest',
case when summ.IsActiveStatusMessages = 0 then 'Inactive'
       when summ.IsActiveStatusMessages = 1 then 'Active'
       end as 'IsActiveStatusMessages',
case when LastHealthEvaluationResult = 1 then 'Not Yet Evaluated'
       when LastHealthEvaluationResult = 2 then 'Not Applicable'
       when LastHealthEvaluationResult = 3 then 'Evaluation Failed'
       when LastHealthEvaluationResult = 4 then 'Evaluated Remediated Failed'
       when LastHealthEvaluationResult = 5 then 'Not Evaluated Dependency Failed'
       when LastHealthEvaluationResult = 6 then 'Evaluated Remediated Succeeded'
       when LastHealthEvaluationResult = 7 then 'Evaluation Succeeded'
       end as 'Last Health Evaluation Result',
case when LastEvaluationHealthy = 1 then 'Pass'
       when LastEvaluationHealthy = 2 then 'Fail'
       when LastEvaluationHealthy = 3 then 'Unknown'
       end as 'Last Evaluation Healthy',
case when summ.ClientRemediationSuccess = 1 then 'Pass'
       when summ.ClientRemediationSuccess = 2 then 'Fail'
       else ''
       end as 'ClientRemediationSuccess',
from v_CH_ClientSummary summ
inner join v_R_System sys on summ.ResourceID = sys.ResourceID
inner join v_GS_PC_BIOS bios on bios.ResourceID = sys.ResourceID
where sys.SID0 is not null --and summ.LastActiveTime > DATEADD(DAY, $("-"+$timeThreshold), GETDATE())
group by sys.SID0, sys.Name0, bios.SerialNumber0, sys.User_Name0, summ.ClientStateDescription, summ.ClientActiveStatus, summ.IsActiveDDR, summ.IsActiveHW, summ.IsActiveSW, summ.ISActivePolicyRequest, summ.IsActiveStatusMessages, summ.LastOnline,summ.LastDDR, summ.LastHW, summ.LastSW, summ.LastPolicyRequest, summ.LastStatusMessage, summ.LastHealthEvaluation, summ.LastHealthEvaluationResult, summ.LastEvaluationHealthy, summ.ClientRemediationSuccess, summ.ExpectedNextPolicyRequest"
foreach ($sccmDb in $arrSccmDbs) {
    $db = $null
    $db = (Invoke-Sqlcmd -query "select name from sys.databases" -ServerInstance $sccmDb -Database "master" | ? {$_.name -like "CM_*"}).name
    $arrSccm += Invoke-Sqlcmd -Query $query -ServerInstance $sccmDb -Database $db `
    | Select -Property SID, "Computer Name", "User Name", "Serial Number", ClientStateDescription, ClientActiveStatus, LastActiveTime, IsActiveDDR, IsActiveHW, IsActiveSW, ISActivePolicyRequest, IsActiveStatusMessages, LastOnline, LastDDR, LastHW, LastSW, LastPolicyRequest, LastStatusMessage, LastHealthEvaluation, LastHealthEvaluationResult, LastEvaluationHealthy, ClientRemediationSuccess, ExpectedNextPolicyRequest
$arrHealth = Join-Object -Left $arrAD -Right $arrSccm -LeftJoinProperty SID -RightJoinProperty SID -Type AllInLeft | Sort-Object -Property LastActiveTime -Descending | Sort-Object -Property SID -Unique

[Powershell] Detect and attempt to remediate/isolate WannaCry

*** This has NOT undergone thorough testing yet ***

*** UPDATE:  Upon further testing, I have found that this script MUST be run as NT/AUTHORITY SYSTEM, otherwise the Windows Updates will fail.

*** UPDATE 2:  It was found that unpatched clients would not update unless and until the CCM client was remediated.  Apparently, broken SCCM updating breaks Windows Updates, even when checking online.  That being the case, I recommend Jason Sandys’ excellent client health script.

This script is designed to be deployed via GPO to detect machines that are vulnerable to or infected by WannaCrypt.  If they are vulnerable, it will attempt to run online updates.  If they are infected, it will attempt to remove them from the network via setting NICs to APIPA address and alert the user to contact technical support.  I have commented out the APIPA portion for ease of testing.

This script was created at the request of management over concerns of machines with nonfunctional SCCM clients.  I hereby release this script to the public domain, and you may use this script in part or in whole.

This is a rough draft, and will likely undergo further revisions/improvements, but has passed minimal QA.  I have attempted to give credit where I borrowed portions of code via comments.

$message = “***WARNING***`n`nWe have disabled this computer to prevent the spread of a WannaCry malware infection, which has been detected on this machine.  Please contact Technical Support as soon as possible to correct this.”
$buildDate = (Get-Item c:\windows).CreationTime
$dateDiff = New-TimeSpan -Start $buildDate -End $(get-date)
if ($dateDiff.TotalDays -lt 2) {exit} # For freshly-imaged machines
function checkForInfection {
    if (test-path “c:\windows\mssecsvc.exe”) {return $true}
    if (test-path “c:\windows\tasksche.exe”) {return $true}
    if (test-path “c:\windows\qeriuwjhrf”) {return $true}
    if (test-path “HKLM:\SOFTWARE\WanaCrypt0r\”) {return $true}
    $arrVolumes = gwmi Win32_Volume | ? {$_.DriveLetter}
    foreach ($volume in $arrVolumes) {
        $driveletter = $volume.DriveLetter + “\”
        if (gci $driveletter -Recurse -ErrorAction SilentlyContinue | ? {($_.Extension -Match “wnry”) -or ($_.Extension -Match “wncry”)}) {return $true}
    return $false
function checkVuln {
    if ($strOsVer -eq “10.0.15063”) {return $false} # 1703 is NOT vulnerable, at least according to https://www.askwoody.com/2017/how-to-make-sure-you-wont-get-hit-by-wannacrywannacrypt/
    foreach ($kb in $arrKbs) {if ($installedPatches.Title -like “*$kb*”) {return $false}}
    return $true
function getInstalledPatches {
    $session = [activator]::CreateInstance([type]::GetTypeFromProgID(“Microsoft.Update.Session”))
    $us = $session.CreateUpdateSearcher()
    $qtd = $us.GetTotalHistoryCount()
    return $us.QueryHistory(0, $qtd)
function killAdapters {
    $netIndex = (gwmi win32_networkadapter | ? {($_.netconnectionid -like “*ethernet*”) -or ($_.netconnectionid -like “*wi*”)})
    foreach ($net in $netIndex) {netsh interface ip add address $($net.interfaceindex)}
function runUpdates {
    $Searcher = New-Object -ComObject Microsoft.Update.Searcher
    $Session = New-Object -ComObject Microsoft.Update.Session
    $Installer = New-Object -ComObject Microsoft.Update.Installer
    $Searcher.Online = $true
    $SearchResult = $Searcher.Search(“IsInstalled=0 and Type=’Software'”).Updates
    $Downloader = $Session.CreateUpdateDownloader()
    $Downloader.Updates = $SearchResult
    $Installer.Updates = $SearchResult
$boolVulnerable = $false
$boolInfected = $false
$strOS = (gwmi Win32_OperatingSystem).Caption
$strOsVer = (gwmi Win32_OperatingSystem).Version
$installedPatches = getInstalledPatches
if (($strOS -like “*Windows 10*”) -or ($strOS -like “*2016*”)) {$arrKbs =@(“KB4012606”,“KB4019474”,“KB4015221”,“KB4016637”,“KB4013198”,“KB4015219”,“KB4016636”,“KB4019473”,“KB4013429”,“KB4015217”,“KB4015438”,“KB4016635”,“KB4019472”)}
else {$arrKbs = @(“KB4012212”,“KB4012213”,“KB4012214”,“KB4012598”,“KB4015550”,“KB4019215”,“KB4015549”,“KB4019264”,“KB4012216”)} #For Windows 7, Server 2008 R2 SP1, Windows Server 2012, Server 2012 R2 and Windows 8.1, Windows Vista and Server 2008 SP2, and XP
$boolVulnerable = checkVuln
$boolInfected = checkForInfection
if ($boolInfected) {
if ($boolVulnerable) {

Exchange Online – Bulk Import SMTP Aliases

I recently moved my personal email to Exchange Online.  I had been using SMTP aliases associated with my mailbox at my old provider, and wanted to continue doing so.

I was able to create a script to /u/life_manager and /u/RampageUT on this thread on Reddit.  Thanks to their scripts, I was able to create a script that was able to migrate a list of SMTP aliases to my new mailbox, without even having to install the Exchange admin tools on my machine.

The script is as follows.  The only changes that you need to make are to the array of SMTP aliases, and the mailbox name, both of which are at the top.

$aliasArray = @( # put SMTP aliases here
$mailbox = "username" # put mailbox ID here

$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session

$user = Get-Mailbox -Identity $mailbox

foreach ($alias in $aliasArray) {

 Set-Mailbox -Identity $user.Identity -emailaddresses @{add=$alias}


Remove-PSSession $Session

(PowerShell) Check For Workstation with no Logged on Users Based on Prefix

This is a PowerShell script designed to pull a list of workstations from AD based on a site code prefix, ping them, and check if anyone is logged on.  It will prompt if you want to connect via RDP when it finds one with nobody logged on, and will also prompt if you want to continue searching.  This requires psloggedon to be installed and in a folder in your PATH variable.

Function yesNo([string]$Title, [string]$Message) { # Simple message box function that prompts for Yes/No.
 $boolChoice = $false
 $objYes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes", "Yes"
 $objNo = New-Object System.Management.Automation.Host.ChoiceDescription "&No", "No"
 $objOptions = [System.Management.Automation.Host.ChoiceDescription[]]($objYes, $objNo)
 $intChoice=$host.ui.PromptForChoice($Title, $Message, $objOptions, 1)
 If ($intChoice -eq 0) { # 0 is Yes, 1 is No
 $boolChoice = $true
 Return $boolChoice
Do { # Prompt for site code, prompt to ask again if no computers are found for site code.
 $boolRedo = $false
 $strSite = ""
 $strSite = Read-Host "Enter site code to search"
 Write-Host ("Checking for unused PCs at site " + $strSite + "...")
 $colADComputers = @()
 $colADComputers = Get-ADComputer -Filter {name -like ($strSite + "-*")}
 If ($colADComputers -eq $Null) {
 $boolRedo = yesNo -Title "Try again?" –Message ("No PCs found for '" + $strSite + "', please check your site code. Would you like to try again?")
} While ($boolRedo -eq $true)
foreach ($objComputer in $colADComputers) {
 $strComputer = $objComputer.Name
 If (Test-Connection -ComputerName $strComputer -Count 1 -Quiet) {
 $strOutput = psloggedon -x -l \\$strComputer 2>%err% | Out-String # Redirect stderr output to batch variable. The banner is output as stderr for some reason.
 $strOutput = $strOutput.ToString()
 $strOutput = $strOutput -replace ".*:" -replace "Connecting to Registry of \\\\" -replace $strComputer -replace "\.\.\." -replace "`n" -replace "[\s]{2,}",", " -replace "^, "
 $strOutput = $strOutput.Trim()
 If ($strOutput -like "No one is logged on locally.") {
 Write-Host $strComputer "is free." -ForegroundColor Green
 $boolConnect = yesNo -Title "Connect to PC?" -Message ("Free PC found: " + $strComputer + ". Connect via RDP?")
 If ($boolConnect) { mstsc.exe /v:$strComputer }
 $boolKeepSearching = yesNo -Title "Keep Searching?" -Message "Keep searching for free PCs?"
 If (!($boolKeepSearching)) { Break <# Exit the loop since we already found a free PC. #> }
 Else {
 Write-Host $strOutput ("is logged on to " + $strComputer + ".") -ForegroundColor Yellow
 Else {
 Write-Host $strComputer "is down." -ForegroundColor Red

Remove desktop.ini files from User Shares (Powershell)

This is a little script I wrote when I once had user Documents folders point to their user shares.  It made browsing the folders difficult, since they had all showed up as “Documents”.  I had since had folder redirection point to a subfolder, but the .ini files persisted.

$arrUsers = Get-ChildItem ("\\fileserver\users") | Where-Object {$_.PSIsContainer} | Foreach-Object {$_.FullName}
Foreach ($strUser in $arrUsers) {
	$strFile = $strUser + "\desktop.ini"
	Remove-Item -Path $strFile -Force