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
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