October 9, 2014 at 1:54 am
Hi,
I want to extract the list of good servers where the SQL Server services are running to execute a .SQL file across the list of servers using powershell.
Somehow the below script does not work.
Any suggestion, where does it error?
# Use the below script to only pull out the details of the GOOD servers.
import-module "SQLPS" -DisableNameChecking
set-location "C:\PS\check\"
$servers = get-content "C:\PS\check\list_servertest.txt";
#$servers.Count
$collection = $()
$newset = $servers.Replace("\XYZ1","")
foreach ($server in $newset)
{
$status = @{ "ServerName" = $server; "TimeStamp" = (Get-Date -f s) }
if (!(Test-Connection $server -Count 1 -ea 0 -Quiet))
{
#Server is DOWN
$status["Results"] = "DOWN"
}
else
{
#Server is UP
$status["Results"] = "UP"
$sqlservices = Get-Service -ComputerName $server | Where-Object {$_.ServiceName -like "MSSQL`$XYZ1"}
#Write-Host "SQL Services on Server [$($server)]"
$status += $sqlservices | Select-Object Name, DisplayName, Status
}
New-Object -TypeName PSObject -Property $status -OutVariable serverStatus
$collection += $serverStatus
}
$collection| Out-File -filepath "C:\PS\check\good_servers.txt" -append
$servers = Get-Content "C:\PS\check\good_servers.txt"
$filePathOutput = "C:\PS\check\execution_result.txt"
Thanks.
October 9, 2014 at 2:20 am
Also, what version of PowerShell?
Earlier versions of Get-Service did not have -ComputerName as a parameter. Version 1.0 certainly didn't.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 9, 2014 at 2:27 am
Where-Object {$_.ServiceName -like "MSSQL`$XYZ1"
Unless I am wrong, which is as likely as not, where is the wildcard?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 9, 2014 at 2:31 am
$status += $sqlservices | Select-Object Name, DisplayName, Status
You cannot add two hash tables like this.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 9, 2014 at 2:39 am
Did you write this script yourself or copy and paste from websites?
If the former then I recommend that you build up a script line by line, outputting to the console the results of each statement in turn so that you understand where you have got to and why it works or not (clue: Write-Output is very handy here).
If the latter then I recommend that you are VERY careful to understand what you have downloaded before execution. Many people could add in a malicious line that could ruin your day/current employment. We are all fortunate that most people choose not to behave in that manner. But we do have to cater for the few nastty individuals.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 10, 2014 at 8:05 am
I guess I helped but you didn't want to share.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 16, 2014 at 7:09 am
Talking to yourself again Gaz? 😉
Gaz
October 16, 2014 at 7:51 am
Gazareth (10/16/2014)
Talking to yourself again Gaz? 😉Gaz
I couldn't stand the silence!!! :crazy:
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 16, 2014 at 8:19 am
SQL-DBA-01 (10/9/2014)
Hi,I want to extract the list of good servers where the SQL Server services are running to execute a .SQL file across the list of servers using powershell.
I will assume you do not know what that script is doing because based on the errors I get when I run it, it is obvious as to why it is errors. You need to learn how to work with hash tables.
However, I would ditch that script altogether and use WMI to check services as it offers up more information an options.
function Get-ServiceStatus ([string[]]$server)
{
foreach ($s in $server)
{
if(Test-Connection $s -Count 2 -Quiet)
{
Get-WmiObject win32_Service -Computer $s |
where {$_.DisplayName -match "SQL Server"} |
select SystemName, DisplayName, Name, State, Status, StartMode, StartName
}
}
}
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 24, 2014 at 3:12 am
There isn't much wrong with the code actually. You were close. Actually, you need an array of hashtables, one of which needs an array to hold the services. This object, '$services' is defined in the beginning as an empty array, and we just add the properties and values to the empty 'status' hashtable as we find stuff out. It is rather neat. I might copy it myself. By the way, to take a good look at the object you've created, you need ConvertTo-JSON (or my own ConvertTo-PSON or ConvertTo-YAML). I've only included the relevant parts of the routine in the following snippet. I didn't test the rest, but it should be enough to get you up and running again.
$Services=@()
foreach ($server in $newset)
{
$status = @{ "ServerName" = $server; "TimeStamp" = (Get-Date -f s) }
if (!(Test-Connection $server -Count 1 -ea 0 -Quiet))
{
#Server is DOWN
$status.Results = "DOWN"
}
else
{
#Server is UP
$status.Results = "UP"
$status.Services=Get-Service -ComputerName $server |
Where-Object {$_.ServiceName -like "MSSQL`$*"}|
Select-Object Name, DisplayName, Status
$Services+=$Status
}
}
Best wishes,
Phil Factor
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply