PoshCode Archive  Artifact [7ac6beb74f]

Artifact 7ac6beb74f6d4ae274778db15fb527ad774dfa88c54b8175513dc293b439f789:

• File Get-SqlWmi.ps1 — part of check-in [6ef5e27f76] at 2018-06-10 13:24:03 on branch trunk — The Get-SqlWmi function gets port, instance and service account wmi information for all SQL instances on a computer. (user: Chad Miller size: 4325)

# encoding: ascii
# api: powershell
# title: Get-SqlWmi
# description: The Get-SqlWmi function  gets port, instance and service account wmi information for all SQL instances on a computer.
# version: 1.0
# type: script
# author: Chad Miller
# license: CC0
# function: Get-SqlWmi
# x-poshcode-id: 3335
# x-archived: 2017-04-11T01:06:07
# x-published: 2012-04-10T09:23:00
#
#
try {add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement"}

#######################
<#
.SYNOPSIS
Gets SQL Server WMI information.
.DESCRIPTION
The Get-SqlWmi function  gets port, instance and service account wmi information for all SQL instances on a computer.
.EXAMPLE
Get-SqlWmi "Z002"
This command gets information for computername Z002.
.NOTES 
Version History 
v1.0   - Chad Miller - Initial release 
v1.1   - Chad Miller - Fixed instance parsing issue with pathname 
#>
function Get-SqlWmi
{
    [CmdletBinding()]
    param(
    [Parameter(Mandatory=$true,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
    [ValidateNotNullorEmpty()]
    [string[]]$ComputerName
    )

    #Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer only works on SQL 2005 and higher. If we fail to gather info at least output
    #null values and computername for 2000 servers in catch block
    BEGIN {}
    PROCESS {
        foreach ($computer in $computername) {
            try {
                $wmi = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $Computer -ErrorAction 'Stop'
                
                $ht = @{}
                $wmi.Services| where {$_.Type -eq 'SqlServer'} | foreach {$instance = $_.PathName -replace '[^-].+\s{1}-s',""; $ht.Add($instance,$_.ServiceAccount)}
                
                $wmi.ServerInstances | foreach { 
                        new-object psobject -property @{
                        ComputerName=$Computer;
                        Port=$_.ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value;
                        AccountName=$ht[$_.Name];
                        Instance=$_.Name }
                    }
            }
            catch {
                    #SQL Server 2000 Does not support the WMI.ManagedComputer class. Try and use SQL-DMO
                    try {   
                            $dmoServer = New-Object -comobject "SQLDMO.SQLServer"
                            $dmoServer.loginsecure = $true
                            $instances = $dmoServer.ListInstalledInstances($computer) | foreach {($_) }
                            $dmoServer = $null
                            #Clustered server will list server1\instance1 and server2\instance2
                            #Filter so just server1 is listed
                            $instances | where { $_ -like "$computer*"} | 
                                foreach {
                                            $dmoServer = New-Object -comobject "SQLDMO.SQLServer"
                                            $dmoServer.loginsecure = $true
                                            $dmoServer.connect($_)
                                            new-object psobject -property @{
                                                ComputerName=$Computer;
                                                Port=$dmoServer.registry.tcpport;
                                                AccountName=$dmoServer.StartupAccount;
                                                Instance = $dmoServer.ServiceName -replace 'MSSQL\$',''
                                            }
                                            $dmoServer.close()
                                            $dmoServer = $null
                                            
                                        }
                             
                                        
                    }
                    catch {
                            new-object psobject -property @{ComputerName=$Computer;Port=$null;AccountName=$null;Instance=$null}
                    }
            }
        }
    }
    END {}

} #Get-SqlWmi