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.