PowerShell: Get list of registered SQL Servers

\ by

No reason to maintain more than one list of SQL Servers when you can query SSMS’s Registered Servers.


Import-Module SQLPS -DisableNameChecking 
$SQLInstanceProd = @()
            
$ServerGroup = 'PRODUCTION'
$SQLPath = "SQLSERVER:\sqlregistration\Database Engine Server Group\PROD*\" 
        
$SQLInstanceProd += @(get-childitem $SQLPath -Recurse).Name | ?{$_ -notmatch '\d\d\d\d'} | % {
    $MyPSObject = New-Object PSObject -Property @{ 
            SQLInstance  = $_ 
            ServerGroup  = $ServerGroup;
        }
    $MyPSObject
    }

$SQLInstanceProd

Putting that logic into Get-RegisteredServers I populate the following list in my Profile.

$AllServersList = (Get-RegisteredServers).SQLInstance
$AllComputersList = $AllServersList | %{  $_.split('\')[0] }
$SQLInstanceDev = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'DEV'}).SQLInstance
$SQLInstanceTest = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'TEST'}).SQLInstance
$SQLInstanceProd = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'PROD'}).SQLInstance
Posted in: Configuration, PowerShell, SQL Server

PowerShell: Get Windows Update scheduled date

\ by

Look for WU install dates for all servers.

$DayOfWeek = @{n='DayOfWeek';e={ ($_.LastRunTime).DayOfWeek }}
$Computer = @{n='Computer';e={$ComputerName}}
$TimeFromNow = @{n='HoursFromNow';e={"{0}" -f (New-TimeSpan -Start (Get-date) -End $_.NextRunTime) } }


$SQLInstanceDev + $SQLInstanceTEST + $SQLInstancePROD  | %{
    $ComputerName = $_.Split('\')[0]
    Write-Host $ComputerName
    # Actually get STATE for Windows Updates for each computer

    .\adhoc\Get-ScheduledTask.ps1 -ComputerName $ComputerName  |
        Where-Object {$_.Name -in 'ProgramDataUpdater','AUScheduledInstall' } |
            Select-Object $Computer, $TimeFromNow, Name, State, Enabled, LastRunTime, NextRunTime, ComputerName, $DayOfWeek

} | Out-GridView
Posted in: Coding, PowerShell, Windows