A PowerShell Script to Discover all SQL Servers on my Domain and SQL Service Pack Level

I need a PowerShell script to Discover all SQL servers on my domain, plus determine the SQL Service Pack Levels for each.

If anyone has a script that you are willing to share, it would be very helpful.  If anyone can assist in writing a script to achieve this objective I would greatly appreciate your assistance.

Thank you,

Lipotech
lipotechSys EngAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

There are tools such as SQLping that can do the finding.
http://www.sqlsecurity.com/downloads

Or Dave Pinal writes that either osql or sqlcmd from the commandline can list instances on a server
http://blog.sqlauthority.com/2009/03/18/sql-server-find-all-servers-from-local-network-using-sqlcmd/

I just tested sqlcmd -L from my commandline, and it found all the instances of SQL that I was aware of on my network.

HTH
  David
0
lipotechSys EngAuthor Commented:
David,

I tried to launch sqlcmd -L from the command line and received an internal command not recognized error.  Should this command be launched under some special condition?

Lipotech
0
lipotechSys EngAuthor Commented:
I am seeking a PowerShell solution to this problem.  Does anyone have a PowerShell script or recommendation on how a script to solve this problem should be constructed?

Lipotech
0
David ToddSenior DBACommented:
Howdy,

What version of SQL tools are on your laptop? I have several versions of SQL installed on my desktop - 2008R2 - 2014.

Once you have the names of the SQL instances, then a simple query getting the serverproperties (https://msdn.microsoft.com/en-us/library/ms174396.aspx)

Look for Productversion and ProductLevel I also get collation.

Here is the body of my powershell script. Note I am getting a list of instances from a server.
#Main Body

$ss = Invoke-SqlCmd -Query "select FQDN + isnull( '\' + nullif( InstanceName, '' ), '' ) as ServerName, VersionNumber from _dba_logging.dbo.SQLServer where ( DateDecommissioned is null or DateDecommissioned > getdate()) and isSQL = 1" -ServerInstance myServer\myInstance

foreach( $s in $ss ) {

 write-output $s.ServerName
$d = Invoke-SqlCmd -Query "
select 
	getdate() as LogDateTime
	, serverproperty( 'MachineName' ) as MachineName
	, serverproperty( 'ServerName' ) as ServerName
	, serverproperty( 'ProductVersion' ) as ProductVersion
	, serverproperty( 'ProductLevel' ) as ProductLevel
    , serverproperty( 'Collation' ) as ServerCollation
	, cast( serverproperty( 'Edition' ) as varchar( 50 ) ) as Edition
	, cast( sd.name as nvarchar( 128 ) ) as DatabaseName
	, sum( fi.size ) * 8 / 1024.0 / 1024.0 as DataSizeGB
	, sum( ti.size ) * 8 / 1024.0 / 1024.0 as LogSizeGB
    , databasepropertyex( sd.name, 'Collation' ) as DatabaseCollation          
from master.dbo.sysdatabases sd
left outer join (
	select
		fii.dbid
		, sum( fii.size ) as size
	from master.dbo.sysaltfiles fii
	where
		fii.groupid > 0 -- not logs
	group by
		fii.dbid
	) fi
	on fi.dbid = sd.dbid
left outer join (
	select	
		tii.dbid
		, sum( tii.size ) as size
	from master.dbo.sysaltfiles tii	
	where
		tii.groupid = 0 -- logs
	group by
		tii.dbid
	) ti
	on ti.dbid = sd.dbid
group by
    sd.name
" -Database tempdb -ServerInstance $s.ServerName | foreach { 
        add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd HH:mm:ss")) -passThru
        } | select-object UsageDT, MachineName, ServerName, ProductVersion, ProductLevel, Edition,  DatabaseName, DataSizeGB, LogSizeGB, ServerCollation, DatabaseCollation | Out-DataTable

Write-DataTable -ServerInstance "myServer\myInstance" -Database _dba_logging -TableName LogDatabase -Data $d

}

Open in new window


Yes its done in powershell, but its more a SQL query.

The Out-DataTable and Write-DataTable functions I got off the net.

Regards
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lipotechSys EngAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.