Need to get all server names in the environment

by Markos at 2012-10-17 13:02:30

Hi all,

I am relatively new to Powershell, so excuse me if my questions sound too primitive.

What I need is to get a list of all our servers in the environment (of course those which I have access to):

1. All boxes and cluster names even if they don’t have SQL Server installations.
2. All SQL Server database engine installations, including multiple instances.
3. SSRS’s and SSIS’s.
4. Their versions.

by Klaas at 2012-10-18 02:26:57
What is "the environment"? Do you mean an Active Directory Domain?
If so you can try Get-ADComputer -filter * | select -expandproperty name if you have the Active Directory Module (on a domain controller or a computer with RSAT installed).
If you have remoting configured on all machines, you can use something like Invoke-Command -computername (Get-ADComputer -filter * | select -expandproperty name) -scriptblock {Get-Service -Name Mssqlserver} or other filters to find which services are running, or you could use Test-Path to find installation folders.
If you have SQLcmdlets available (usually on a sql server) you can try Invoke-sqlcmd -ServerInstance YourSQLserver -query ‘SELECT @@Version or you can use the SMO objects.
by Markos at 2012-10-18 07:04:18
Thanks Klaas for code, but unfortunately it does not work for me, I get a message "The term ‘Get-ADComputer’ is not recognized as the name of a cmdlet, function, script file…"

I guess I have old version of PS. My version is 2.0. And get-command does not list "get-ADcomputer".

What I mean under "Environment" is all (or part of it, like sub-network) all our corporate network. I am new DBA here, and I was given just some SQL Server names, but I suspect not all. So I wanted to discover all servers here to make a list for myself, to know what to manage.
by Klaas at 2012-10-18 07:29:13
Get-ADcomputer will be available if you’re on a domain controller or you have RSAT installed. In PowerShell V2 you have to
Import-Module ActiveDirectory first.
You can discover SQLServers by finding SQL files, services or ports, maybe with Nmap or System Center.
sqlcmd -L is an easy command to try.
If you have machines outside AD, you probably have to sweep through an IP subnet.