Get server hostnames on which MSSQLServer service is installed

With this simple script, you will be able to get the server names on which the MS SQL Server service is installed.

First, an array is built with the computer names and the following properties :

  • only the “Server” operating systems
  • only physical servers (virtual cluster nodes are excluded)
  • only enabled computer accounts

The cmdlet Get-service is used to query all the servers

$list = Get-ADComputer -ldapfilter "(&(objectCategory=computer)(operatingSystem=*server*)(!(servicePrincipalName=MSClusterVirtualServer*))(!(userAccountControl:1.2.840.113556.1.4.803:=8192)))"

$list | % {
	$hostname = $_.Name
	try {
		if (Get-Service -ComputerName $hostname | ? { ($_.name -cmatch 'MSSQL') -and ($_.DisplayName -cmatch 'SQL Server')}){
			Write-Host $hostname ": SQL Server is running"
		}
	}
	catch {
		Write-Host "Unable to request the server "$hostname
	}
}

References

Get-Service

Syntax
Parameter Set: Default
Get-Service [[-Name] <String[]> ] [-ComputerName <String[]> ] [-DependentServices] [-Exclude <String[]> ] [-Include <String[]> ] [-RequiredServices] [ <CommonParameters>]

Parameter Set: DisplayName
Get-Service -DisplayName <String[]> [-ComputerName <String[]> ] [-DependentServices] [-Exclude <String[]> ] [-Include <String[]> ] [-RequiredServices] [ <CommonParameters>]

Parameter Set: InputObject
Get-Service [-ComputerName <String[]> ] [-DependentServices] [-Exclude <String[]> ] [-Include <String[]> ] [-InputObject <ServiceController[]> ] [-RequiredServices] [ <CommonParameters>]


Detailed Description

The Get-Service cmdlet gets objects that represent the services on a local computer or on a remote computer, including running and stopped services.

You can direct Get-Service to get only particular services by specifying the service name or display name of the services, or you can pipe service objects to Get-Service.

Parameters
-ComputerName<String[]>

Gets the services running on the specified computers. The default is the local computer.

Type the NetBIOS name, an IP address, or a fully qualified domain name of a remote computer. To specify the local computer, type the computer name, a dot (.), or “localhost”.

This parameter does not rely on Windows PowerShell remoting. You can use the ComputerName parameter of Get-Service even if your computer is not configured to run remote commands.

Aliases none
Required? false
Position? named
Default Value Local computer
Accept Pipeline Input? true (ByPropertyName)
Accept Wildcard Characters? false
-DependentServices

Gets only the services that depend upon the specified service.

By default, Get-Service gets all services.

Aliases none
Required? false
Position? named
Default Value False
Accept Pipeline Input? false
Accept Wildcard Characters? false
-DisplayName<String[]>

Specifies the display names of services to be retrieved. Wildcards are permitted. By default, Get-Service gets all services on the computer.

Aliases none
Required? true
Position? named
Default Value All services
Accept Pipeline Input? false
Accept Wildcard Characters? true
-Exclude<String[]>

Omits the specified services. The value of this parameter qualifies the Name parameter. Enter a name element or pattern, such as “s*”. Wildcards are permitted.

Aliases none
Required? false
Position? named
Default Value none
Accept Pipeline Input? false
Accept Wildcard Characters? true
-Include<String[]>

Retrieves only the specified services. The value of this parameter qualifies the Name parameter. Enter a name element or pattern, such as “s*”. Wildcards are permitted.

Aliases none
Required? false
Position? named
Default Value none
Accept Pipeline Input? false
Accept Wildcard Characters? true
-InputObject<ServiceController[]>

Specifies ServiceController objects representing the services to be retrieved. Enter a variable that contains the objects, or type a command or expression that gets the objects. You can also pipe a service object to Get-Service.

Aliases none
Required? false
Position? named
Default Value none
Accept Pipeline Input? true (ByValue)
Accept Wildcard Characters? false
-Name<String[]>

Specifies the service names of services to be retrieved. Wildcards are permitted. By default, Get-Service gets all of the services on the computer.

Aliases none
Required? false
Position? 1
Default Value All services
Accept Pipeline Input? true (ByPropertyName, ByValue)
Accept Wildcard Characters? true
-RequiredServices

Gets only the services that this service requires.

This parameter gets the value of the ServicesDependedOn property of the service. By default, Get-Service gets all services.

Aliases none
Required? false
Position? named
Default Value False
Accept Pipeline Input? false
Accept Wildcard Characters? false
<CommonParameters>

This cmdlet supports the common parameters: -Verbose, -Debug, -ErrorAction, -ErrorVariable, -OutBuffer, and -OutVariable. For more information, see  about_CommonParameters (http://go.microsoft.com/fwlink/p/?LinkID=113216).

Inputs

The input type is the type of the objects that you can pipe to the cmdlet.

  • System.ServiceProcess.ServiceController, System.StringYou can pipe a service object or a service name to Get-Service.
Outputs

The output type is the type of the objects that the cmdlet emits.

  • System.ServiceProcess.ServiceControllerGet-Service returns objects that represent the services on the computer.
Notes
  • You can also refer to Get-Service by its built-in alias, “gsv”. For more information, see about_Aliases.Get-Service can display services only when the current user has permission to see them. If Get-Service does not display services, you might not have permission to see them.

    To find the service name and display name of each service on your system, type “get-service”. The service names appear in the Name column, and the display names appear in the DisplayName column.

    When you sort in ascending order by status value, “Stopped” services appear before “Running” services. The Status property of a service is an enumerated value in which the names of the statuses represent integer values. The sort is based on the integer value, not the name. “Running” appears before “Stopped” because “Stopped” has a value of “1”, and “Running” has a value of “4”.

Examples
————————– EXAMPLE 1 ————————–

This command retrieves all of the services on the system. It behaves as though you typed “get-service *”. The default display shows the status, service name, and display name of each service.

PS C:\> get-service
————————– EXAMPLE 2 ————————–

This command retrieves services with service names that begin with “WMI” (the acronym for Windows Management Instrumentation).

PS C:\> get-service wmi*
————————– EXAMPLE 3 ————————–

This command displays services with a display name that includes the word “network”. Searching the display name finds network-related services even when the service name does not include “Net”, such as xmlprov, the Network Provisioning Service.

PS C:\> get-service -displayname *network*
————————– EXAMPLE 4 ————————–

These commands get only the services with service names that begin with “win”, except for the WinRM service.

PS C:\> get-service -name win* -exclude winrm
————————– EXAMPLE 5 ————————–

This command displays only the services that are currently running. It uses the Get-Service cmdlet to get all of the services on the computer. The pipeline operator (|) passes the results to the Where-Object cmdlet, which selects only the services with a Status property that equals “Running”.

Status is only one property of service objects. To see all of the properties, type “get-service | get-member”.

PS C:\> get-service | where-object {$_.Status -eq "Running"}
————————– EXAMPLE 6 ————————–

This command gets the services on the Server02 remote computer.

Because the ComputerName parameter of Get-Service does not use Windows PowerShell remoting, you can use this parameter even if the computer is not configured for remoting in Windows PowerShell.

PS C:\> get-service -computername Server02
————————– EXAMPLE 7 ————————–

These commands list the services on the computer that have dependent services.

The first command uses the Get-Service cmdlet to get the services on the computer. A pipeline operator (|) sends the services to the Where-Object cmdlet, which selects the services whose DependentServices property is not null.

Another pipeline operator sends the results to the Format-List cmdlet. The command uses its Property parameter to display the name of the service, the name of the dependent services, and a calculated property that displays the number of dependent services that each service has.

PS C:\> get-service | where-object {$_.DependentServices} | format-list -property Name, DependentServices, @{Label="NoOfDependentServices"; Expression={$_.dependentservices.count}}

Name                  : AudioEndpointBuilder
DependentServices     : {AudioSrv}
NoOfDependentServices : 1
Name                  : Dhcp
DependentServices     : {WinHttpAutoProxySvc}
NoOfDependentServices : 1
...
————————– EXAMPLE 8 ————————–

This command shows that when you sort services in ascending order by the value of their Status property, stopped services appear before running services. This happens because the value of Status is an enumeration, in which “Stopped” has a value of “1”, and “Running” has a value of 4.

To list running services first, use the Descending parameter of the Sort-Object cmdlet.

PS C:\>get-service s* | sort-object status

Status   Name               DisplayName
------   ----               -----------
Stopped  stisvc             Windows Image Acquisition (WIA)
Stopped  SwPrv              MS Software Shadow Copy Provider
Stopped  SysmonLog          Performance Logs and Alerts
Running  Spooler            Print Spooler
Running  srservice          System Restore Service
Running  SSDPSRV            SSDP Discovery Service
Running  ShellHWDetection   Shell Hardware Detection
Running  Schedule           Task Scheduler
Running  SCardSvr           Smart Card
Running  SamSs              Security Accounts Manager
Running  SharedAccess       Windows Firewall/Internet Connectio...
Running  SENS               System Event Notification
Running  seclogon           Secondary Logon

PS C:\>get-service s* | sort-object status -descending

Status   Name               DisplayName
------   ----               -----------
Running  ShellHWDetection   Shell Hardware Detection
Running  SharedAccess       Windows Firewall/Internet Connectio...
Running  Spooler            Print Spooler
Running  SSDPSRV            SSDP Discovery Service
Running  srservice          System Restore Service
Running  SCardSvr           Smart Card
Running  SamSs              Security Accounts Manager
Running  Schedule           Task Scheduler
Running  SENS               System Event Notification
Running  seclogon           Secondary Logon
Stopped  SysmonLog          Performance Logs and Alerts
Stopped  SwPrv              MS Software Shadow Copy Provider
Stopped  stisvc             Windows Image Acquisition (WIA)
————————– EXAMPLE 9 ————————–

This command uses the Get-Service cmdlet to run a “Get-Service Winrm” command on two remote computers and the local computer (“localhost”).

The Get-Service command runs on the remote computers, and the results are returned to the local computer. A pipeline operator (|) sends the results to the Format-Table cmdlet, which formats the services as a table. The Format-Table command uses the Property parameter to specify the properties displayed in the table, including the MachineName property.

PS C:\> get-service -name winrm -computername localhost, Server01, Server02 | format-table -property MachineName, Status, Name, DisplayName -auto

MachineName    Status  Name  DisplayName
------------   ------  ----  -----------
localhost      Running WinRM Windows Remote Management (WS-Management)
Server01       Running WinRM Windows Remote Management (WS-Management)
Server02       Running WinRM Windows Remote Management (WS-Management)
————————– EXAMPLE 10 ————————–

This command gets the services that the WinRM service requires.

The command returns the value of the ServicesDependedOn property of the service.

PS C:\> get-service winrm -requiredServices
————————– EXAMPLE 11 ————————–

This command gets the WinRM service on the local computer. This example shows that you can pipe a service name string (enclosed in quotation marks) to Get-Service.

PS C:\> "winrm" | get-service
Get server hostnames on which MSSQLServer service is installed

Leave a Reply

Your email address will not be published.