PoshCode Logo PowerShell Code Repository

Get-SqlWmi by Chad Miller 34 months ago
View followups from Chad Miller | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/3235"></script>download | new post

The Get-SqlWmi function gets port, instance and service account wmi information for all SQL instances on a computer.

  1. try {add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
  2. catch {add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement"}
  3.  
  4. #######################
  5. <#
  6. .SYNOPSIS
  7. Gets SQL Server WMI information.
  8. .DESCRIPTION
  9. The Get-SqlWmi function  gets port, instance and service account wmi information for all SQL instances on a computer.
  10. .EXAMPLE
  11. Get-SqlWmi "Z002"
  12. This command gets information for computername Z002.
  13. .NOTES
  14. Version History
  15. v1.0   - Chad Miller - Initial release
  16. #>
  17. function Get-SqlWmi
  18. {
  19.     [CmdletBinding()]
  20.     param(
  21.     [Parameter(Mandatory=$true,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
  22.     [ValidateNotNullorEmpty()]
  23.     [string[]]$ComputerName
  24.     )
  25.  
  26.     #Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer only works on SQL 2005 and higher. If we fail to gather info at least output
  27.     #null values and computername for 2000 servers in catch block
  28.     BEGIN {}
  29.     PROCESS {
  30.         foreach ($computer in $computername) {
  31.             try {
  32.                 $wmi = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $Computer -ErrorAction 'Stop'
  33.                
  34.                 $ht = @{}
  35.                 $wmi.Services| where {$_.Type -eq 'SqlServer'} | foreach {$instance = $_.PathName -replace '".+"\s{1}-s',""; $ht.Add($instance,$_.ServiceAccount)}
  36.                
  37.                 $wmi.ServerInstances | foreach {
  38.                         new-object psobject -property @{
  39.                         ComputerName=$Computer;
  40.                         Port=$_.ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value;
  41.                         AccountName=$ht[$_.Name];
  42.                         Instance=$_.Name }
  43.                     }
  44.             }
  45.             catch {
  46.                     #SQL Server 2000 Does not support the WMI.ManagedComputer class. Try and use SQL-DMO
  47.                     try {  
  48.                             $dmoServer = New-Object -comobject "SQLDMO.SQLServer"
  49.                             $dmoServer.loginsecure = $true
  50.                             $instances = $dmoServer.ListInstalledInstances($computer) | foreach {($_) }
  51.                             $dmoServer = $null
  52.                             #Clustered server will list server1\instance1 and server2\instance2
  53.                             #Filter so just server1 is listed
  54.                             $instances | where { $_ -like "$computer*"} |
  55.                                 foreach {
  56.                                             $dmoServer = New-Object -comobject "SQLDMO.SQLServer"
  57.                                             $dmoServer.loginsecure = $true
  58.                                             $dmoServer.connect($_)
  59.                                             new-object psobject -property @{
  60.                                                 ComputerName=$Computer;
  61.                                                 Port=$dmoServer.registry.tcpport;
  62.                                                 AccountName=$dmoServer.StartupAccount;
  63.                                                 Instance = $dmoServer.ServiceName -replace 'MSSQL\$',''
  64.                                             }
  65.                                             $dmoServer.close()
  66.                                             $dmoServer = $null
  67.                                            
  68.                                         }
  69.                              
  70.                                        
  71.                     }
  72.                     catch {
  73.                             new-object psobject -property @{ComputerName=$Computer;Port=$null;AccountName=$null;Instance=$null}
  74.                     }
  75.             }
  76.         }
  77.     }
  78.     END {}
  79.  
  80. } #Get-SqlWmi

Submit a correction or amendment below (
click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me