Check your SQL Server using Windows PowerShell - Part 4

August 6, 2008

Part I of this series illustrated the first and foremost check on SQL Server–how to ping the host. Part 2 examined how to check all of the windows services related to SQL Server and part 3 discussed how to check the hardware and operating system information. part 4 of this series demonstrates how to get hard disk and network adapter information from the host machine.

Step 1

Type or copy and paste the following code to C:\CheckSQLServer\Checkhd.ps1.

#Function to check the HDD information on the host machine
Function checkHD([string] $Hostname )
{
$drives=get-wmiobject -class Win32_LogicalDisk 
  -computername $hostname -errorvariable errorvar
if (-not $errorvar)
{
foreach ($drive in $drives) 
{
if ($drive.drivetype -eq "3" )
{ 
$message= "DeviceID="+$drive.Deviceid+" Size="+ 
  $drive.size/1048576+"MB Free Space="+ 
  $drive.freespace/1048576 +"MB Percentage Used=" + 
  (($drive.Size/1048576)-($drive.freespace/1048576))/
  ($drive.Size/1048576) *100+"% "
write-host $message -background "GREEN" -foreground "BLACk"
}
}
}
}

Step 2

Type or copy and paste the following code to C:\CheckSQLServer\Checknet.ps1.

#Function to check the Network netadapter information on the 
  host machine
Function checknet([string] $Hostname )
{
$netadapter=get-wmiobject -class win32_networkadapter 
  -computername $hostname -errorvariable errorvar
if (-not $errorvar)
{
foreach ($netadapter in $netadapter)
{
write-host "---------------------------------------------------" 
  -background "Blue" -foreground "BLACk"
#$message= "netadapter Enabled="+$netadapter.Enable
#write-host $message -background "GREEN" -foreground "BLACk"
$message= "netadapterType="+$netadapter.netadapterType
write-host $message -background "GREEN" -foreground "BLACk"
$message= "Description="+$netadapter.Description
write-host $message -background "GREEN" -foreground "BLACk"
$message= "Manufacturer="+$netadapter.Manufacturer
write-host $message -background "GREEN" -foreground "BLACk"
$message= "NetworkAddresses="+$netadapter.NetworkAddresses
write-host $message -background "GREEN" -foreground "BLACk"
$message= "PermanentAddress="+$netadapter.PermanentAddress
write-host $message -background "GREEN" -foreground "BLACk"
$message= "Physicalnetadapter="+$netadapter.Physicalnetadapter
write-host $message -background "GREEN" -foreground "BLACk"
$message= "ProductName="+$netadapter.ProductName
write-host $message -background "GREEN" -foreground "BLACk"
$message= "ServiceName="+$netadapter.ServiceName
write-host $message -background "GREEN" -foreground "BLACk"
$message= "StatusInfo="+$netadapter.StatusInfo
write-host $message -background "GREEN" -foreground "BLACk"
$message= "Speed="+$netadapter.Speed
write-host $message -background "GREEN" -foreground "BLACk"
$message= "Status="+$netadapter.Status
write-host $message -background "GREEN" -foreground "BLACk"
}
}
}

Step 3

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

. ./checkhd.ps1
. ./checknet.ps1

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

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

Note: This CheckSQL_Lib.ps1 will be updated with sourcing of new scripts, such as checkhd.ps1 and checknet.ps1

Step 4

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

Write-host "Checking HDD Information....."
Write-host "............................."
checkHD $Hostname
Write-host "Checking Network Adapter Information....."
Write-host "........................................."
checknet $Hostname

Now C:\CheckSQLServer\CheckSQLServer.ps1 will have both checkhd and checknet as shown below. We added some write-host statements 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
)
$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
Write-host "Checking HDD Information....."
Write-host "............................."
checkHD $Hostname
Write-host "Checking Network Adapter Information....."
Write-host "........................................."
checknet $Hostname

}

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

Sourcing basically 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 us execute the script, CheckSQLServer.ps1, by passing “Powerpc” host as the argument as shown below.

./CheckSQLServer.ps1 PowerServer2

You will get the following results as shown below. [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=1492684
FreeSpaceInPagingFiles=2402648
FreeVirtualMemory=3948148
PAEEnabled=False
ServicePackMajorVersion=0
ServicePackMinorVersion=0
Checking HDD Information.....
.............................
DeviceID=C: Size=38143.99609375MB Free Space=23761.51953125MB Percentage Used=37.7057414937619
Checking Network Adapter Information.....
.........................................
---------------------------------------------------
netadapterType=
Description=WAN Miniport (SSTP)
Manufacturer=Microsoft
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=WAN Miniport (SSTP)
ServiceName=RasSstp
StatusInfo=
Speed=
Status=
---------------------------------------------------
netadapterType=
Description=WAN Miniport (L2TP)
Manufacturer=Microsoft
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=WAN Miniport (L2TP)
ServiceName=Rasl2tp
StatusInfo=
Speed=
Status=
---------------------------------------------------
netadapterType=
Description=WAN Miniport (PPTP)
Manufacturer=Microsoft
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=WAN Miniport (PPTP)
ServiceName=PptpMiniport
StatusInfo=
Speed=
Status=
---------------------------------------------------
netadapterType=
Description=WAN Miniport (PPPOE)
Manufacturer=Microsoft
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=WAN Miniport (PPPOE)
ServiceName=RasPppoe
StatusInfo=
Speed=
Status=
---------------------------------------------------
netadapterType=
Description=WAN Miniport (IPv6)
Manufacturer=Microsoft
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=WAN Miniport (IPv6)
ServiceName=NdisWan
StatusInfo=
Speed=
Status=
---------------------------------------------------
netadapterType=
Description=WAN Miniport (Network Monitor)
Manufacturer=Microsoft
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=WAN Miniport (Network Monitor)
ServiceName=NdisWan
StatusInfo=
Speed=
Status=
---------------------------------------------------
netadapterType=
Description=Intel(R) PRO/1000 MT Network Connection
Manufacturer=Intel
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=Intel(R) PRO/1000 MT Network Connection
ServiceName=E1G60
StatusInfo=
Speed=100000000
Status=
---------------------------------------------------
netadapterType=
Description=Microsoft ISATAP Adapter
Manufacturer=Microsoft
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=Microsoft ISATAP Adapter
ServiceName=tunnel
StatusInfo=
Speed=100000
Status=
---------------------------------------------------
netadapterType=
Description=WAN Miniport (IP)
Manufacturer=Microsoft
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=WAN Miniport (IP)
ServiceName=NdisWan
StatusInfo=
Speed=
Status=
---------------------------------------------------
netadapterType=
Description=Microsoft Tun Miniport Adapter
Manufacturer=Microsoft
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=Microsoft Tun Miniport Adapter
ServiceName=tunmp
StatusInfo=
Speed=1073741824
Status=
---------------------------------------------------
netadapterType=
Description=RAS Async Adapter
Manufacturer=Microsoft
NetworkAddresses=
PermanentAddress=
Physicalnetadapter=
ProductName=RAS Async Adapter
ServiceName=AsyncMac
StatusInfo=
Speed=
Status=

Figure 1.0

From the result, you can clearly see the hard disk and network card information.

Step 6

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

./CheckSQLServer.ps1 TestMachine

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

Results

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


Figure 1.1

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

Conclusion

This is the fourth part of the article series “Check your SQL Server using Windows PowerShell”. The article illustrated how to access the hard disk and network card adapter information using Windows PowerShell and WMI-Object.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers