Powershell - Query SQL Servers Operating system details

Question

Powershell - Query SQL Servers Operating system details

Justin Manning SA

Hall of Fame

Points: 3190
More actions
February 13, 2013 at 5:13 am

#274124

Comments posted to this topic are about the item Powershell - Query SQL Servers Operating system details

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

- Win Hall of Fame Points: 3006 More actions · Answer 1

Hi Justin,

good to see your post. I am new to powershell, and just the same thing I am looking for, to pull details from all the servers in the environment with server name, instance, version, patches updated, sp updated, os, edition etc.

Can you please help me in what / where, are the connection strings to be changed to run in my environment.

Thanks in advance.

Cheers,
- Win
"Dont Judge a Book by its Cover"

Justin Manning SA Hall of Fame Points: 3190 More actions · Answer 2

Hey there,

You can change the connection to your CMS server in the top function:

function GetServers

{

$ServerInstance = "vypdbmon01"

$Database = "msdb"

This is where you would put your details in.

I am currently working on the same script to actually query Active Directory and to search through ALL Server, whether they have SQL or not and to document the environment.

I will post the updated script shortly.

Let me know if you need any help

sqlhammer 72186 SSChasing Mays Points: 621 More actions · Answer 3

What version of SQL and powershell was this script meant for? I've been having some trouble with the script.

1. I had to change the query in GetServers to "SELECT server_name FROM msdb.dbo.sysmanagement_shared_registered_servers" because my systargetservers was empty even though I have over 170 registered servers.

2. I am getting a laundry list of errors at runtime to include:

Export-Csv : A parameter cannot be found that matches parameter name 'Append'.

At line:18 char:138

+ SystemType, @{Name="TotalPhysicalMemory (GB)"; Expression={[math]::round($($_.TotalPhysicalMemory/1gb), 2)}} | export-csv -Append <<<< -pa

th .\ProductionServers\ProductionSQLServer_ComputerSystem.csv -noType

+ CategoryInfo : InvalidArgument: (:) [Export-Csv], ParameterBindingException

+ FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.ExportCsvCommand

AND

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if

a path was included, verify that the path is correct and try again.

At line:2 char:18

+ Invoke-Sqlcmd <<<< -ServerInstance $SqlServer -Database master -Query "

+ CategoryInfo : ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoundException

+ FullyQualifiedErrorId : CommandNotFoundException

Regarding the Append error, I've looked up Export-CSV in MSDN and see no reference to that parameter. Regarding the Invoke-Sqlcmd I have found the article below to correct this error but I'm posting it anyways just in case others have issues with it.

How to use Invoke-Sqlcmd.[/url]

Best Regards,
Derik Hammer
www.sqlhammer.com

Justin Manning SA Hall of Fame Points: 3190 More actions · Answer 4

Hey SQLHammer,

Thank you for the feedback.

1. Are you making use of CMS in SQL Server? or merely querying for registered servers?

I am using a Central Management Server for my environment, that has all my production servers registered in it. I have the CMS registered as a master servers and then all my SQL Servers in the environment registered as target servers, where i push maintenance jobs out to.

These SQL Servers are all SQL 2008R2 and am using Windows PowerShell ISE, I think its version 1.

2. I am not sure if the -Append parameter is only specific to a particular version of Powershell? but if i run this script in my working directory, folders are created with csv files for each dump of information I am requesting.

3. Invoke-Sqlcmd - I think this step is failing because the script has failed at an earlier point of execution and the state in which script was running has become unstable. You may also need to install additional Powershell components in order to interface with SQL Server.

I am currently working on an update to this script whereby the record set of servers is actually pulled from active directory based on naming standards you use in your environment.

Thanks again for the feedback 🙂

sqlhammer 72186 SSChasing Mays Points: 621 More actions · Answer 5

Thank you for the reply. I realized that I didn't have any target servers setup at the time. This is why I my systargetservers table was empty.

I'm going to do more research when I have time about the Export-CSV cmdlet.

Best Regards,
Derik Hammer
www.sqlhammer.com

Justin Manning SA Hall of Fame Points: 3190 More actions · Answer 6

Improved script can be found at http://www.sqlservercentral.com/scripts/powershell/97305/