Slow Wifi after Upgrading In-Place To Windows 10

Still don’t know the exact root cause, but a ticket is currently open with Microsoft.  It is somehow related to Bitlocker.  Turning off Bitlocker before upgrading eliminates the problem.  Microsoft is investigating, but since I’ve had so much trouble and time spent on this, I thought I’d post this in the hope I save some other sysadmin the headache.

[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',
summ.ClientStateDescription,
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',
summ.LastOnline,
summ.LastDDR,
summ.LastHW,
summ.LastSW,
summ.LastPolicyRequest,
summ.LastStatusMessage,
summ.LastHealthEvaluation,
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',
summ.ExpectedNextPolicyRequest
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

[SCCM] Deploy to Machines Based on User Distribution List Membership (Without Using User-Device Affinity)

I recently came across a unique situation where it was necessary to deploy applications to a machine, based on the owner’s membership in a particular DL in exchange, but without the use of user-device affinity.  Query is below.

SELECT
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client
FROM
SMS_R_System INNER JOIN SMS_G_System_COMPUTER_SYSTEM ON
SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
WHERE
SMS_G_System_COMPUTER_SYSTEM.UserName IN (SELECT UniqueUserName FROM SMS_R_User WHERE UserGroupName = "Domain\\MailingList")

[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 {
 
    #http://stackoverflow.com/questions/36761787/how-to-get-all-details-from-installed-updates-window
    $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) 169.254.0.0}
 
}
 
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
    $Downloader.Download()
   
    $Installer.Updates = $SearchResult
    $Installer.Install()
 
}
 
$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) {
 
    [System.Windows.MessageBox]::Show($message)
    #killAdapaters
    exit
 
}
 
if ($boolVulnerable) {
 
    runUpdates
 
}

JSS Bash Script to Determine Last Logged On User

Below is a small Bash script I wrote to determine the last logged on user on Mac machines and log it to the JSS server via an extension attribute.

#!/bin/bash
result=`last | grep -v -E '(reboot|shutdown|root)' | grep 'console' -m 1 | awk '{print $1}'`
if [ -z $result ]; then
   echo "<result>(No recent logons)</result>"
else
    echo "<result>$result</result>"
fi