Open Powershell_ISE and copy and paste the code.
Change the location of the serverlist and the output file location accordingly.
Open Powershell_ISE and copy and paste the code.
Change the location of the serverlist and the output file location accordingly.
import-module "SQLPS" -DisableNameChecking
set-location "C:\Powershell\SQL_Server_Key_details\";
$ErrorActionPreference = "SilentlyContinue";
$servers = get-content "C:\Powershell\SQL_Server_Key_details\serverlist.txt";
#$servers.Count
$newset = $servers.Replace("\MongoMongo","") #Pass the instance name (given an arbirtrary name as \MongoMongo)
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";
Write-Host "SQL Services on Server [$($server)]" -foregroundcolor "RED"
}
else
{
#Server is UP
$status["Results"] = "UP"
$sqlservices = Get-Service -ComputerName $server | Where-Object {$_.ServiceName -like "MSSQL`$MongoMongo"}
Write-Host "SQL Services on Server [$($server)]" -foregroundcolor "Green" -backgroundcolor pink;
$status += $sqlservices | Select-Object Name, DisplayName, Status;
write-host "Results from $server” -foregroundcolor "Green";
$sqldbserviceInfo = gwmi -class win32_service -ComputerName $server -Namespace "root\cimv2" -Filter "Name='MSSQL$`MongoMongo' OR Name='SQLAGENT$`MongoMongo'"
$sqlserviceresult = New-Object PSObject -Property @{
'ServerName' = $server
‘ServiceName’ = $sqldbserviceInfo.DisplayName
‘ServiceAccount’ = $sqldbserviceInfo.StartName
‘StartMode’ = $sqldbserviceInfo.StartMode
‘ServiceState’ = $sqldbserviceInfo.State
}
$sqlserviceresult | select ServerName, ServiceName, ServiceAccount, StartMode, ServiceState
$allserviceInfo = gwmi -class win32_service -ComputerName $server -Namespace "root\cimv2""
#$sqldbsvcinfo = $allserviceinfo | select-object DisplayName, StartName, StartMode, State #| where-object {$_.Name -eq "MSSQL$`MongoMongo"}
$sqlsbsvcinfo
#$serviceInfo #|Format-Table -AutoSize
#New-Object -TypeName PSObject -Property $status -OutVariable serverStatus
#$collection += $serverStatus
#$sqlserviceresult | select ServerName, ServiceName, ServiceAccount, StartMode, ServiceState | Out-File -filepath "
$sqlserviceresult | select ServerName, ServiceName, ServiceAccount, StartMode, ServiceState | Out-File -filepath "C:\Powershell\SQL_Server_Key_details\good_servers.txt" -append
}
}
$output | Out-File -filepath "C:\Powershell\SQL_Server_Key_details\good_servers.txt" -append