Check your SQL Server using Windows PowerShell - Part 3

July 16, 2008

Part 1 of this series illustrated the first and foremost check on SQL Server--how to ping the host. Part 2 illustrated how to check all the windows services related to SQL Server.

In part 3 of this series, I am going to illustrate how to acquire some operating system and hardware related information from the host machine.

Step 1

Type or Copy and paste the following code to C:\CheckSQLServer\Checkhardware.ps1.

#Function to check Hardware information from a host
Function checkhardware([string] $Hostname )
{
$computer=get-wmiobject -class win32_computersystem -computername 
  $hostname -errorvariable errorvar
$errorvar.size
if (-not $errorvar)
{
$message="Host="+$Hostname
write-host $message -background "GREEN" -foreground "BLACk"
$message="Description=" +$computer.Description
write-host $message -background "GREEN" -foreground "BLACk"
$message="NumberOfLogicalProcessors="+ 
  $computer.NumberOfLogicalProcessors
write-host $message -background "GREEN" -foreground "BLACk"
$message="NumberOfProcessors="+ $computer.NumberOfProcessors
write-host $message -background "GREEN" -foreground "BLACk"
$message="TotalPhysicalMemory=" +$computer.TotalPhysicalMemory
write-host $message -background "GREEN" -foreground "BLACk"
$message="Model=" +$computer.Model
write-host $message -background "GREEN" -foreground "BLACk"
$message="Manufacturer=" +$computer.Manufacturer
write-host $message -background "GREEN" -foreground "BLACk"
$message="PartOfDomain="+ $computer.PartOfDomain
write-host $message -background "GREEN" -foreground "BLACk"
$message="CurrentTimeZone=" +$computer.CurrentTimeZone
write-host $message -background "GREEN" -foreground "BLACk"
$message="DaylightInEffect="+$computer.DaylightInEffect
write-host $message -background "GREEN" -foreground "BLACk"
}
} 

Step 2

Type or Copy and paste the following code to C:\CheckSQLServer\CheckOS.ps1.

#Function to check the OS information on the host machine
Function checkOS([string] $Hostname )
{
$os=get-wmiobject -class win32_operatingsystem -computername $hostname -errorvariable errorvar
if (-not $errorvar)
{
$message= "OSArchitecture="+$os.OSArchitecture
write-host $message -background "GREEN" -foreground "BLACk"
$message= "OSLanguage="+$os.OSLanguage
write-host $message -background "GREEN" -foreground "BLACk"
$message= "OSProductSuite="+$os.OSProductSuite
write-host $message -background "GREEN" -foreground "BLACk"
$message= "OSType="+$os.OSType
write-host $message -background "GREEN" -foreground "BLACk"
$message= "BuildNumber="+$os.BuildNumber
write-host $message -background "GREEN" -foreground "BLACk"
$message= "BuildType="+$os.BuildType
write-host $message -background "GREEN" -foreground "BLACk"
$message= "Version="+$os.Version
write-host $message -background "GREEN" -foreground "BLACk"
$message= "WindowsDirectory="+$os.WindowsDirectory
write-host $message -background "GREEN" -foreground "BLACk"
$message= "PlusVersionNumber="+$os.PlusVersionNumber
write-host $message -background "GREEN" -foreground "BLACk"
$message= "FreePhysicalMemory="+$os.FreePhysicalMemory
write-host $message -background "GREEN" -foreground "BLACk"
$message= "FreeSpaceInPagingFiles="+$os.FreeSpaceInPagingFiles
write-host $message -background "GREEN" -foreground "BLACk"
$message= "FreeVirtualMemory="+$os.FreeVirtualMemory
write-host $message -background "GREEN" -foreground "BLACk"
$message= "PAEEnabled="+$os.PAEEnabled
write-host $message -background "GREEN" -foreground "BLACk"
}
}

Step 3

Append C:\CheckSQLServer\CheckSQL_Lib.ps1 with the code shown below.

. ./checkhardware.ps1
. ./checkOS.ps1

Now C:\CheckSQLServer\CheckSQL_Lib.ps1 will have pinghost, checkservices, checkhardware and checkOS, as shown below.

#Source all the functions relate to CheckSQL
. ./PingHost.ps1
. ./checkservices.ps1
. ./checkhardware.ps1
. ./checkOS.ps1

Note: This CheckSQL_Lib.ps1 will be updated with sourcing of new scripts like checkhardware.ps1 and checkOS.ps1

Step 4

Append C:\CheckSQLServer\CheckSQLServer.ps1 with the following code.

checkhardware $Hostname
checkOS $Hostname

Now C:\CheckSQLServer\CheckSQLServer.ps1 will have both checkhardware and checkOS, as shown below. We added some write-host statement to show the entire process.

#Objective: To check various status of SQL Server 
#Host, instances and databases.
#Author: MAK
#Date Written: June 5, 2008
param (
  [string] $Hostname
)
. ./CheckSQL_Lib.ps1 
Write-host "Checking SQL Server....."
Write-host "........................"
Write-host " "
Write-host "Arguments accepted : $Hostname"
write-host "........................"
Write-host "Pinging the host machine"
write-host "........................"
PingHost $Hostname
Write-host "Checking windows services on the host related to SQL Server"
write-host "..........................................................."
checkservices $Hostname
Write-host "Checking hardware Information....."
Write-host "........................"
checkhardware $Hostname
Write-host "Checking OS Information....."
Write-host "........................"
checkOS $Hostname

Note: This CheckSQLServer.ps1 will be updated with new conditions and parameters in future installments of this article series as well.

Sourcing loads the functions listed in the script file and makes it available during the entire PowerShell session. In this case, we are sourcing a script, which in turn is going to source many scripts.

Step 5

Now let’s execute the script, CheckSQLServer.ps1, by passing “Powerpc” host as the argument as shown below.

./CheckSQLServer.ps1 PowerServer2

You get the following results. [Refer Fig 1.0]

Checking SQL Server.....
........................
Arguments accepted : PowerServer2
........................
Pinging the host machine
........................
PowerServer2 is REACHABLE
Checking windows services on the host related to SQL Server
...........................................................
Host=PowerServer2 MSSQLSERVER Running OK True .\Administrator
Host=PowerServer2 MSSQLServerADHelper100 Stopped OK False NT AUTHORITY\NETWORK SERVICE
Host=PowerServer2 MSSQLServerOLAPService Stopped OK False .\Administrator
Host=PowerServer2 SQLBrowser Stopped OK False NT AUTHORITY\LOCAL SERVICE
Host=PowerServer2 SQLSERVERAGENT Stopped OK False .\Administrator
Host=PowerServer2 SQLWriter Stopped OK False LocalSystem
Checking hardware Information.....
........................
Host=PowerServer2
Description=AT/AT COMPATIBLE
NumberOfLogicalProcessors=2
NumberOfProcessors=1
TotalPhysicalMemory=2145738752
Model=OptiPlex GX270
Manufacturer=Dell Computer Corporation
PartOfDomain=True
CurrentTimeZone=-240
DaylightInEffect=True
Checking OS Information.....
........................
OSArchitecture=32-bit
OSLanguage=1033
OSProductSuite=274
OSType=18
BuildNumber=6001
BuildType=Multiprocessor Free
Version=6.0.6001
WindowsDirectory=C:\Windows
PlusVersionNumber=
FreePhysicalMemory=1511144
FreeSpaceInPagingFiles=2402648
FreeVirtualMemory=3966452
PAEEnabled=False

Figure 1.0

From the result, you can clearly see the hardware and operating system information.

Step 6

Now let’s execute the script on the machine that doesn’t exist as shown below.

./CheckSQLServer.ps1 TestMachine

You get the results shown below. [Refer Fig 1.1]

Results

Checking SQL Server.....
........................
Arguments accepted : TestMachine
........................
Pinging the host machine
........................
TestMachine is NOT reachable
Checking windows services on the host related to SQL Server
...........................................................
Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At C:\checksqlserver\checkservices.ps1:5 char:24
+ $Services=get-wmiobject  <<<< -class win32_service -computername $hostname| where {$_.name -like '*SQL*'}| select-obj
ect Name,state,status,Started,Startname,Description
Host=TestMachine
Checking hardware Information.....
........................
Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At C:\checksqlserver\checkhardware.ps1:5 char:24
+ $computer=get-wmiobject  <<<< -class win32_computersystem -computername $hostname -errorvariable errorvar
Checking OS Information.....
........................
Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At C:\checksqlserver\checkOS.ps1:5 char:18
+ $os=get-wmiobject  <<<< -class win32_operatingsystem -computername $hostname -errorvariable errorvar

Step 7

We don’t have to continue with checkservices, checkhardware and checkos if the ping fails. So let’s update the pinghost.ps1, as shown below.

Function Pinghost  ([string] $Hostname )
{
$status=get-wmiobject win32_pingstatus -Filter "Address='$Hostname'" | Select-Object statuscode
if($status.statuscode -eq 0)
{
write-host $Hostname is REACHABLE -background "GREEN" -foreground "BLACk"
}
else
{
$global:errorvar="host not reachable" 
write-host $Hostname is NOT reachable -background "RED" -foreground "BLACk"
}
}

Let’s update checksqlserver.ps1, as shown below.

#Objective: To check various status of SQL Server 
#Host, instances and databases.
#Author: MAK
#Date Written: June 5, 2008
param (
  [string] $Hostname
)
$global:errorvar=0
. ./CheckSQL_Lib.ps1 
Write-host "Checking SQL Server....."
Write-host "........................"
Write-host " "
Write-host "Arguments accepted : $Hostname"
write-host "........................"
Write-host "Pinging the host machine"
write-host "........................"
pinghost $Hostname
if ($global:errorvar -ne "host not reachable")
{
Write-host "Checking windows services on the host related to SQL Server"
write-host "..........................................................."
checkservices $Hostname
Write-host "Checking hardware Information....."
Write-host "........................"
checkhardware $Hostname
Write-host "Checking OS Information....."
Write-host "........................"
checkOS $Hostname
}

Now let’s execute the script by passing a machinename “testmachine”, which actually doesn’t exist, as the argument.

./CheckSQLServer.ps1 TestMachine

Result

Checking SQL Server.....
........................
Arguments accepted : TestMachine
........................
Pinging the host machine
........................
TestMachine is NOT reachable

Note: You can download the latest PART III code from here.

Conclusion

This is the third part of the article series “Check your SQL Server using Windows PowerShell”. The article illustrated how to access the operating system and hardware information using Windows PowerShell and WMI-Object. In the next installment, we will add some additional checking, so that we don’t have to do all of the checking if the ping fails. In addition, we will see how to capture some useful operating and hardware information as well.

» See All Articles by Columnist MAK