Private/Kinds/Odbc.ps1
|
# The Odbc Kind: which database servers this machine is actually configured to talk to. # # A business application that "hangs at startup" is usually waiting on a database server # it cannot reach, and the machine already knows which one - it is in the data source. So # this Kind discovers the Servers and then hands them to the Server Kind rather than # re-implementing reachability, which is the same composition a Check Definition would # express by naming both Kinds, done here because only the machine knows the names. # Ports SQL Server answers on when a data source does not say. 1433 is the default # instance; a named instance negotiates a dynamic port through the browser service and # cannot be TCP-tested without being told which. $script:OdbcDefaultSqlPort = 1433 # Where ODBC keeps drivers and data sources, per architecture and per user. $script:OdbcDriverKeys = @( @{ Path = 'HKLM:\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers'; Suffix = '' } @{ Path = 'HKLM:\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI\ODBC Drivers'; Suffix = ' (32-bit)' } ) $script:OdbcDataSourceKeys = @( 'HKLM:\SOFTWARE\ODBC\ODBC.INI' 'HKLM:\SOFTWARE\WOW6432Node\ODBC\ODBC.INI' 'HKCU:\Software\ODBC\ODBC.INI' ) function ConvertTo-OdbcServerTarget { <# .SYNOPSIS Turns a data source's Server value into a Server entry, or nothing. Pure. .DESCRIPTION A SQL Server address is written several ways - "tcp:host,1433", "host\INSTANCE", a bare host - and each means something different for what can be tested. Parsing it is separate from probing so the interpretation can be tested without a network, and so the named-instance case can say why it is only half tested. #> [CmdletBinding()] [OutputType([psobject])] param([AllowNull()][AllowEmptyString()][string]$Server) if (-not $Server -or -not "$Server".Trim()) { return } $value = "$Server".Trim() -replace '^tcp:', '' $port = $null if ($value -match ',(\d+)\s*$') { $port = $Matches[1]; $value = $value -replace ',\s*\d+\s*$', '' } $instance = $null if ($value -match '\\(.+)$') { $instance = $Matches[1]; $value = $value -replace '\\.+$', '' } $value = $value.Trim() if (-not $value) { return } # A data source pointing at this machine is not a network dependency, and testing it # would report on a loopback nobody is asking about. if ($value -in '.', '(local)', 'localhost', '127.0.0.1', '(localdb)') { return } # A named instance without an explicit port negotiates one at connect time, so there # is no port to test; the host is still worth resolving and pinging. $entry = $value if ($port) { $entry = '{0}:{1}' -f $value, $port } elseif (-not $instance) { $entry = '{0}:{1}' -f $value, $script:OdbcDefaultSqlPort } [pscustomobject]@{ PSTypeName = 'Gutcheck.OdbcTarget' HostName = $value Instance = $instance Port = $port Entry = $entry DynamicPort = [bool]($instance -and -not $port) } } function Get-OdbcData { <# .PARAMETER Observed What earlier Checks gathered. Used only to notice that another Check Definition already performed this Kind: several applications legitimately want ODBC looked at and the answer is machine-wide, so it is discovered once and referred to after. #> [CmdletBinding()] [OutputType([psobject])] param( [hashtable]$Parameters = @{}, [AllowNull()][hashtable]$Observed = @{} ) if ($Observed -and $Observed.ContainsKey('Odbc')) { $first = $Observed['Odbc'] return [pscustomobject]@{ PSTypeName = 'Gutcheck.Data.Odbc' AlreadyChecked = $true # Carried so the Report can name where the answer is rather than repeating it. PerformedBy = Get-DataProperty $first 'CheckName' Drivers = @() DataSources = @() Server = $null } } $drivers = @(foreach ($key in $script:OdbcDriverKeys) { $properties = Get-ItemProperty $key.Path -ErrorAction SilentlyContinue if (-not $properties) { continue } $properties.PSObject.Properties | Where-Object { $_.Name -match 'SQL' } | ForEach-Object { '{0}{1}' -f $_.Name, $key.Suffix } }) $dataSources = @(foreach ($key in $script:OdbcDataSourceKeys) { if (-not (Test-Path $key)) { continue } Get-ChildItem $key -ErrorAction SilentlyContinue | Where-Object { $_.PSChildName -ne 'ODBC Data Sources' } | ForEach-Object { $properties = Get-ItemProperty $_.PSPath -ErrorAction SilentlyContinue if (-not $properties.Server) { return } [pscustomobject]@{ Dsn = $_.PSChildName Server = "$($properties.Server)" Database = "$($properties.Database)" Driver = "$($properties.Driver)" Scope = $key } } }) $targets = @($dataSources | ForEach-Object { ConvertTo-OdbcServerTarget -Server $_.Server } | Where-Object { $_ }) # Reachability is the Server Kind's job, so it does it. Composition rather than a # second implementation of ping and connect that could drift from the first. $server = $null $entries = @($targets | ForEach-Object { $_.Entry } | Select-Object -Unique) if ($entries.Count) { $server = Get-ServerData -Parameters @{ Servers = $entries } } [pscustomobject]@{ PSTypeName = 'Gutcheck.Data.Odbc' AlreadyChecked = $false PerformedBy = $null Drivers = @($drivers | Sort-Object -Unique) DataSources = $dataSources Targets = $targets Server = $server } } function ConvertTo-OdbcFinding { [CmdletBinding()] [OutputType([psobject])] param( [AllowNull()]$Data, [hashtable]$Parameters = @{} ) if (Get-DataProperty $Data 'AlreadyChecked') { # Visible in the Report rather than silently dropped: a Technician who selected # two applications that both use ODBC has to be able to see that the second one's # database configuration was considered, and where the answer is. $where = Get-DataProperty $Data 'PerformedBy' return New-Finding -Category Apps -Check (Get-Text 'Check.Odbc.ODBCSQL') -Severity INFO ` -Value $(if ($where) { ((Get-Text 'Value.Odbc.AlreadyCheckedSee') -f $where) } else { (Get-Text 'Value.Odbc.AlreadyCheckedEarlier') }) ` -Hint (Get-Text 'Hint.Odbc.ODBCIsConfiguredPerMachine') } New-OdbcDriverFinding -Data $Data -Parameters $Parameters New-OdbcDataSourceFinding -Data $Data -Parameters $Parameters New-OdbcInstanceFinding -Data $Data -Parameters $Parameters # The Servers the data sources named, judged by the Kind that judges Servers. $server = Get-DataProperty $Data 'Server' if ($server) { ConvertTo-ServerFinding -Data $server -Parameters $Parameters } } function New-OdbcDriverFinding { [CmdletBinding()] param([AllowNull()]$Data, [hashtable]$Parameters) $drivers = Get-DataCollection $Data 'Drivers' New-Finding -Category Apps -Check (Get-Text 'Check.Odbc.SQLClientDrivers') -Severity INFO ` -Value $(if ($drivers.Count) { $drivers -join ', ' } else { (Get-Text 'Value.Shared.None') }) } function New-OdbcDataSourceFinding { [CmdletBinding()] param([AllowNull()]$Data, [hashtable]$Parameters) $sources = Get-DataCollection $Data 'DataSources' if ($sources.Count) { return } New-Finding -Category Apps -Check (Get-Text 'Check.Odbc.ODBCDataSources') -Severity INFO ` -Value (Get-Text 'Value.Odbc.NoneWithAServer') ` -Hint (Get-Text 'Hint.Odbc.AddTheDatabaseServerTo') } function New-OdbcInstanceFinding { [CmdletBinding()] param([AllowNull()]$Data, [hashtable]$Parameters) foreach ($target in (Get-DataCollection $Data 'Targets')) { if (-not $target.DynamicPort) { continue } New-Finding -Category Apps -Check ((Get-Text 'Check.Odbc.SqlInstance') -f $target.HostName, $target.Instance) ` -Severity INFO -Value (Get-Text 'Value.Odbc.NamedInstance') ` -Hint (Get-Text 'Hint.Odbc.AddHostPortToThis') } } function ConvertTo-OdbcSection { [CmdletBinding()] [OutputType([psobject])] param([AllowNull()]$Data) if (Get-DataProperty $Data 'AlreadyChecked') { return } New-Section -Title (Get-Text 'Title.Odbc.ODBCDataSourcesWith') -Row (Get-DataCollection $Data 'DataSources') $server = Get-DataProperty $Data 'Server' if ($server) { ConvertTo-ServerSection -Data $server } } |