Technical Article

Extract SQL Server Key details

,

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

Rate

3.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

3.67 (3)

You rated this post out of 5. Change rating