Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Powershell - Query SQL Servers Operating system details


Powershell - Query SQL Servers Operating system details

Author
Message
Justin Manning SA
Justin Manning SA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 942
Comments posted to this topic are about the item Powershell - Query SQL Servers Operating system details
- Win
- Win
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 605
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
Justin Manning SA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 942
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
sqlhammer 72186
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 147
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: (Smile [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.

Best Regards,
Derik Hammer
www.sqlhammer.com
Justin Manning SA
Justin Manning SA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 942
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
sqlhammer 72186
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 147
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
Justin Manning SA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 942
Improved script can be found at http://www.sqlservercentral.com/scripts/powershell/97305/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search