Check your SQL Server using Windows PowerShell - Part 4August 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
Step 2 Type or copy and paste the following code to C:\CheckSQLServer\Checknet.ps1. #Function to check the Network netadapter information on the 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
Note: You can download the latest PART 3 code from here. ConclusionThis 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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
-computername $hostname -errorvariable errorvar
if (-not $errorvar)
{
foreach ($drive in $drives)
{
if ($drive.drivetype -eq "3" )
{
$message= "DeviceID="+$drive.Deviceid+" Size="+
