July 18, 2014 at 8:36 am
How to check if I have a list of SQL Servers in a .TXT file and in the list, what are the SQL Servers are not accessible/stopped/not working?
$MachineList = get-content "C:\PS\list_server.txt";
$MachineList.Count
foreach ($Machine in $MachineList)
{
#Run ping test
$MachineName = $Machine
$result = Get-WMIObject -query “select StatusCode from Win32_PingStatus where Address = ‘$MachineName’”
}
The above query is throwing error....
Thanks.
July 18, 2014 at 8:40 am
Try this one instead
$servers = get-content "C:\PS\list_server.txt"
$collection = $()
foreach ($server in $servers)
{
$status = @{ "ServerName" = $server; "TimeStamp" = (Get-Date -f s) }
if (Test-Connection $server -Count 1 -ea 0 -Quiet)
{
$status["Results"] = "Up"
}
else
{
$status["Results"] = "Down"
}
New-Object -TypeName PSObject -Property $status -OutVariable serverStatus
$collection += $serverStatus
}
$collection | Export-Csv -LiteralPath .\ServerStatus.csv -NoTypeInformation
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 19, 2014 at 12:15 am
Thnx Jason..
Thanks.
July 21, 2014 at 8:46 am
Hello,
I wanted to use Multi-thread, as I need to run some scripts to multiple instances at the same time. Single time execution takes longer, so threading is the best choice. Can you please review the below code and suggest to make any changes to run it successfully..?
import-module "SQLPS" -DisableNameChecking
set-location "C:\PS\check\"
$servers = get-content "C:\PS\check\list_servertest.txt";
#$servers.Count
$collection = $()
$newset = $servers.Replace("\XYZ","")
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`$XYZ"}
#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"
<#
[ScriptBlock]$scriptblock = {
function Execute-SQLCommand {param( [string]$Server, #the host name of the SQL server
[string]$Database, #the name of the database
[System.Data.SqlClient.SqlCommand]$Command) #the command to execute (name of stored procedure)
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;User ID=user;Initial Catalog=$Database;Data Source=$Server"
$Command.CommandType = [System.Data.CommandType]::Text
$Command.Connection = $sqlConnection
$sqlConnection.Open()
$Result = $Command.ExecuteNonQuery()
$sqlConnection.Close()
if ($Result -gt 0)
{
{
Invoke-Sqlcmd -ServerInstance $instance -Connectiontimeout 10 -Database "master"
-InputFile "C:\PS\check\permissions_check.sql" -QueryTimeout 10 | Out-File -filePath "C:\PS\check\permissions_check_output.txt" -Append
Write-Host "Processing completed on Server: $($instance)" -Fore Green
}
# permissions_check.sql is to check the permission of an account.
return $TRUE
}
else
{
return $FALSE
}
}
#>
$MaxThreads = 20
$SleepTimer = 1000
foreach($server in $servers) {
Start-Job -ScriptBlock {"C:\PS\check\main_call.Ps1" }
While($(Get-Job -State 'Running').Count -ge $MaxThreads) {
Start-Sleep -Milliseconds $SleepTimer #Need this to wait until a job is complete and kick off a new one.
Write-Host "Processing completed on Server: $($instance)" -Fore Green
}
}
Get-Job | Wait-Job | Receive-Job
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply