Test SQL Server with Windows PowerShell - Part 4

Test SQL Server with Windows PowerShell - Part 1
Test SQL Server with Windows PowerShell - Part 2
Test SQL Server with Windows PowerShell - Part 3

Muthusamy

Network Administration - Part 1 of this series introduced the first check on SQL Server - ping a host. Part 2 is an introduction to checking all Windows services related to SQL Server and the third part is how to check hardware and software information. In this next article, I will show you how to collect network card and hard disk information from the server.

Step 1

Type or copy and paste the following code into C: CheckSQLServerCheckhd.ps1.

 #Function để kiểm tra thông tin HDD trên máy phục vụ 

 Function checkHD ([string] $ Hostname) 

 { 

 $ drives = get-wmiobject -class Win32_LogicalDisk 

    -computername $ hostname -errorvariable errorvar 

 if (-not $ errorvar) 

 { 

 choeach ($ drive trong $ 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 into C: CheckSQLServerChecknet.ps1.

 #Function để kiểm tra thông tin thông tin netadapter trên 

    host machine 

 Checknet function ([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

Assign the file C: CheckSQLServerCheckSQL_Lib.ps1 the code below.

 . ./checkhd.ps1 

 . ./checknet.ps1 

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

 #Source all the relate to functions CheckSQL 

 . ./PingHost.ps1 

 . ./checkservices.ps1 

 . ./checkhardware.ps1 

 . ./checkOS.ps1 

 . ./checkHD.ps1 

 . ./checknet.ps1 

Note: This CheckSQL_Lib.ps1 file will be updated with the source of new scripts such as checkhd.ps1 and checknet.ps1.

Step 4

Append to the file C: CheckSQLServerCheckSQLServer.ps1 code below.

 Write-host "Checking HDD Information ." 

 Write-host "....." 

 checkHD $ Hostname 

 Write-host "Checking Network Adapter Information ." 

 Write-host "......." 

 checknet $ Hostname 

Now C: CheckSQLServerCheckSQLServer.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 "Check 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: CheckSQLServer.ps1 will be updated with new conditions and new parameters in later sections of this series.

The source basically loads the rows listed in the script file and makes it available during the entire PowerShell session. In this case, we source a scenario, which is sourced from many other scenarios.

Step 5

Let's execute the script CheckSQLServer.ps1 by passing 'Powerpc' host as an argument, as shown below.

 ./CheckSQLServer.ps1 PowerServer2 

The results you get are as shown below (refer to Figure 1.0).

 Checking SQL Server . 

 .... 

 Arguments accepted: PowerServer2 

 .... 

 Pinging the host machine 

 .... 

 PowerServer2 is REACHABLE 

 Check windows services on the host related to SQL Server 

 ......... .. 

 Host = PowerServer2 MSSQLSERVER Running OK True .Administrator 

 Host = PowerServer2 MSSQLServerADHelper100 Stopped OK False 
 NT AUTHORITYNETWORK SERVICE 

 Host = PowerServer2 MSSQLServerOLAPService Stopped OK 
 False .Administrator 

 Host = PowerServer2 SQLBrowser Stopped OK False 
 NT AUTHORITYLOCAL 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

Let's make sure that the script on the computer doesn't have a hard drive or a network card.

 ./CheckSQLServer.ps1 TestMachine 

The results you get are shown below (refer to Figure 1.1).

Result

 Checking SQL Server . 

 .... 

 Arguments accepted: TestMachine 

 .... 

 Pinging the host machine 

 .... 

 TestMachine is NOT reachable 

Figure 1.1

Note: you can download the code for the latest part 3 here.

Conclude

Part 4 of this series has shown you how to access the hard drive and network card information using Windows PowerShell and WMI-Object.