Introduction

From a SharePoint administration perspective monitoring tasks are very important for maintaining a healthy Farm. Monitoring should be done at specific time intervals. You can then differentiate monitoring into the 3 categories daily, weekly and monthly. This article outlines how to automate some of the tasks for daily monitoring.

What the script does

The daily monitoring script automates the following tasks and captures the information into CSV files:

Manually doing these tasks consumes time and human error can occur. So the PowerShell script would become handy in this scenario.

There is one more function that is included with this script. This is the most interesting part. The script generates output in CSV files for each of the preceding tasks. There is a separate function that converts all the CSV files into an Excel sheet with tabs separated for each of the CSV file. This is done to consolidate all the reports into a single file and provide it to the customer or upper management for better readability.

Note: This functionality of consolidating files into Excel only works if you have an Excel client installed.

Functions

Function 1

The following piece of code automates the determination of the SharePoint services status:

  1. Function SharePointServices([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3. Write-Host ""
  4. write-host "Generating SharePoint services report" -fore Magenta
  5. $output = $scriptbase + "\" + "SharePointServices.csv" "ServiceName" + "," + "ServiceStatus" + "," + "MachineName" | Out-File -Encoding Default -FilePath $Output;
  6. foreach($server in $farm.Servers)
  7. {
  8. foreach($instance in $server.ServiceInstances)
  9. {
  10. # If the server has the timer service then stop the service
  11. if($instance.TypeName -eq $timerServiceInstanceName)
  12. {
  13. [string]$serverName = $server.Name
  14. write-host "Generating SP services report for server" $serverName -fore yellow
  15. $Monitor = "SPAdminV4" , "SPTimerV4" , "SPTraceV4" , "SPUserCodeV4" , "SPWriterV4" , "OSearch14" , "W3SVC" , "IISADMIN" , "C2WTS" , "FIMService" , "FIMSynchronizationService"
  16. $services = Get-Service -ComputerName $serverName -Name $Monitor -ea silentlycontinue
  17. foreach($service in $services)
  18. {
  19. $service.displayname + "," + $service.status + "," + $service.MachineName | Out-File -Encoding Default -Append -FilePath $Output;
  20. }
  21. write-host "SP services report generated" -fore green
  22. }
  23. }
  24. }
  25. }

Function 2

The following piece of code automates the determination of the IIS website details:

  1. Function IISWebsite([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3. Write-Host ""
  4. write-host "Generating IIS website report" -fore Magenta
  5. $output = $scriptbase + "\" + "IISWebsite.csv"
  6. "WebSiteName" + "," + "WebsiteID" + "," + "WebSiteState" + "," + "Server" | Out-File -Encoding Default -FilePath $Output;
  7. foreach($server in $farm.Servers)
  8. {
  9. foreach($instance in $server.ServiceInstances)
  10. {
  11. # If the server has the timer service then stop the service
  12. if($instance.TypeName -eq $timerServiceInstanceName)
  13. {
  14. [string]$serverName = $server.Name
  15. write-host "Generating IIS website report for server" $serverName -fore yellow
  16. $status = ""
  17. $Sites = gwmi -namespace "root\webadministration" -Class site -ComputerName $serverName -Authentication PacketPrivacy -Impersonation Impersonate
  18. foreach($site in $sites)
  19. {
  20. if($site.getstate().returnvalue -eq 1)
  21. {
  22. $status = "Started"
  23. }
  24. else
  25. {
  26. $status = "Not Started"
  27. }
  28. $site.name + "," + $site.ID + "," + $Status + "," + $serverName | Out-File -Encoding Default -Append -FilePath $Output;
  29. }
  30. write-host "IIS website report generated" -fore green
  31. }
  32. }
  33. }
  34. }

Function 3

The following piece of code automates the determination of the AppPool status:

  1. Function AppPoolStatus([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3. Write-Host ""
  4. write-host "Generating AppPool status report" -fore Magenta
  5. $output = $scriptbase + "\" + "AppPoolStatus.csv""AppPoolName" + "," + "Status" + "," + "Server" | Out-File -Encoding Default -FilePath $Output;
  6. foreach($server in $farm.Servers)
  7. {
  8. foreach($instance in $server.ServiceInstances)
  9. {
  10. # If the server has the timer service then stop the service
  11. if($instance.TypeName -eq $timerServiceInstanceName)
  12. {
  13. [string]$serverName = $server.Name
  14. write-host "Generating AppPool status report for server" $serverName -fore yellow
  15. $status = ""
  16. $AppPools = gwmi -namespace "root\webadministration" -Class applicationpool -ComputerName $serverName -Authentication PacketPrivacy -Impersonation Impersonate
  17. foreach($AppPool in $AppPools )
  18. {
  19. if($AppPool.getstate().returnvalue -eq 1)
  20. {
  21. $status = "Started"
  22. }
  23. else
  24. {
  25. $status = "Stopped"
  26. }
  27. $AppPool.name + "," + $Status + "," + $serverName| Out-File -Encoding Default -Append -FilePath $Output;
  28. }
  29. write-host "AppPool status report generated" -fore green
  30. }
  31. }
  32. }
  34. }
Function 4

The following piece of code monitors disk space:

  1. Function DiskSpace([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  4. Write-Host ""
  5. write-host "Generating Disk space report" -fore Magenta
  7. $output = $scriptbase + "\" + "DiskSpace.csv"
  8. "Computer Name" + "," + "Drive" + "," + "Size in (GB)" + "," + "Free Space in (GB)" + "," + "Critical (*)" | Out-File -Encoding Default -FilePath $Output;
  10. foreach($server in $farm.Servers)
  11. {
  12. foreach($instance in $server.ServiceInstances)
  13. {
  14. # If the server has the timer service then stop the service
  15. if($instance.TypeName -eq $timerServiceInstanceName)
  16. {
  17. [string]$serverName = $server.Name
  18. write-host "Generating disk space report for server" $serverName -fore yellow
  20. $drives = Get-WmiObject -ComputerName $serverName Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
  22. foreach($drive in $drives)
  23. {
  24. $id = $drive.DeviceID
  26. $size = [math]::round($drive.Size / 1073741824, 2)
  28. $free = [math]::round($drive.FreeSpace / 1073741824, 2)
  30. $pct = [math]::round($free / $size, 2) * 100
  32. if ($pct -lt 30)
  33. {
  34. $pct = $pct.ToString() + "% *** "
  35. }
  36. else
  37. {
  38. $pct = $pct.ToString() + " %"
  39. }
  41. $serverName + "," + $id + "," + $size + "," + $free + "," + $pct | Out-File -Encoding Default -Append -FilePath $Output;
  42. $pct = 0
  43. }
  44. write-host "Disk space report generated" -fore green
  45. }
  46. }
  47. }
  49. }

Function 5

The following piece of code automates the determination of the health analyzer reports:

  1. Function HealthAnalyserReports()
  2. {
  4. write-host ""
  5. write-host "Generating health analyser report" -fore magenta
  7. $output = $scriptbase + "\" + "HealthAnalyser.csv"
  8. "Severity" + "," + "Category" + "," + "Modified" + "," + "Failing servers" + "," + "Failing services" | Out-File -Encoding Default -FilePath $Output;
  10. $ReportsList = [Microsoft.SharePoint.Administration.Health.SPHealthReportsList]::Local
  11. $Items = $ReportsList.items | where {
  13. if($_['Severity'] -eq '1 - Error')
  14. {
  15. #write-host $_['Name']
  16. #write-host $_['Severity']
  17. $_['Severity'] + "," + $_['Category'] + "," + $_['Modified'] + "," + $_['Failing Servers'] + "," + $_['Failing Services'] | Out-File -Encoding Default -Append -FilePath $Output;
  18. }
  19. }
  20. write-host "Health analyser report generated" -fore green
  21. }

Function 6

The following piece of code automates the determination of the CPU utilization:

  1. Function CPUUtilization([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  4. Write-Host ""
  5. write-host "Generating CPU utilization report" -fore Magenta
  7. $output = $scriptbase + "\" + "CPUUtilization.csv"
  8. "ServerName" + "," + "DeviceID" + "," + "LoadPercentage" + "," + "Status" | Out-File -Encoding Default -FilePath $Output;
  10. foreach($server in $farm.Servers)
  11. {
  12. foreach($instance in $server.ServiceInstances)
  13. {
  14. # If the server has the timer service then stop the service
  15. if($instance.TypeName -eq $timerServiceInstanceName)
  16. {
  17. [string]$serverName = $server.Name
  18. write-host "Generating CPU utilization report for server" $serverName -fore yellow
  19. $CPUDataCol = Get-WmiObject -Class Win32_Processor -ComputerName $ServerName
  20. foreach($Data in $CPUDataCol)
  21. {
  22. $serverName + "," + $Data.DeviceID + "," + $Data.loadpercentage + "," + $Data.status | Out-File -Encoding Default -Append -FilePath $Output;
  23. }
  24. write-host "CPU utilization report generated" -fore green
  25. }
  26. }
  27. }
  29. }
Function 7

The following piece of code automates the determination of the memory utilization:

  1. Function MemoryUtilization([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  4. Write-Host ""
  5. write-host "Memory utilization report" -fore Magenta
  7. $output = $scriptbase + "\" + "MemoryUtilization.csv"
  8. "ServerName" + "," + "FreePhysicalMemory" + "," + "TotalVisibleMemorySize" + "," + "Status" | Out-File -Encoding Default -FilePath $Output;
  10. foreach($server in $farm.Servers)
  11. {
  12. foreach($instance in $server.ServiceInstances)
  13. {
  14. # If the server has the timer service then stop the service
  15. if($instance.TypeName -eq $timerServiceInstanceName)
  16. {
  17. [string]$serverName = $server.Name
  18. write-host "Generating memory utilization report for server" $serverName -fore yellow
  19. $MemoryCol = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $ServerName
  20. foreach($Data in $MemoryCol)
  21. {
  22. $serverName + "," + $Data.FreePhysicalMemory + "," + $Data.TotalVisibleMemorySize + "," + $Data.status | Out-File -Encoding Default -Append -FilePath $Output;
  23. }
  24. write-host "Memory utilization report generated" -fore green
  25. }
  26. }
  27. }
  29. }

Function 8

The following piece of code automates the determination of the SharePoint server status:

  1. Function SPServerStatus([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  4. Write-Host ""
  5. write-host "SharePoint server status report" -fore Magenta
  7. $output = $scriptbase + "\" + "SPServerStatus.csv"
  8. "ServerName" + "," + "Role" + "," + "Status" + "," + "CanUpgrade" + "," + "NeedsUpgrade" | Out-File -Encoding Default -FilePath $Output;
  10. foreach($server in $farm.Servers)
  11. {
  12. foreach($instance in $server.ServiceInstances)
  13. {
  14. # If the server has the timer service then stop the service
  15. if($instance.TypeName -eq $timerServiceInstanceName)
  16. {
  18. $server.Name + "," + $server.role + "," + $server.status + "," + $server.canupgrade + "," + $server.NeedsUpgrade | Out-File -Encoding Default -Append -FilePath $Output;
  19. write-host "SP server status report generated" -fore green
  20. }
  21. }
  22. }
  23. }

Function 9

The output of each of the preceding functions is captured into a CSV file.

This function converts all the CSV files into a single Excel sheet separated with tabs for each CSV file.

  1. Function Release-Ref ($ref)
  2. {
  3. ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
  4. [System.__ComObject]$ref) -gt 0)
  5. [System.GC]::Collect()
  6. [System.GC]::WaitForPendingFinalizers()
  7. }
  9. Function ConvertCSV-ToExcel
  10. {
  12. [CmdletBinding(
  13. SupportsShouldProcess = $True,
  14. ConfirmImpact = 'low',
  15. DefaultParameterSetName = 'file'
  16. )]
  17. Param (
  18. [Parameter(
  19. ValueFromPipeline=$True,
  20. Position=0,
  21. Mandatory=$True,
  22. HelpMessage="Name of CSV/s to import")]
  23. [ValidateNotNullOrEmpty()]
  24. [array]$inputfile,
  25. [Parameter(
  26. ValueFromPipeline=$False,
  27. Position=1,
  28. Mandatory=$True,
  29. HelpMessage="Name of excel file output")]
  30. [ValidateNotNullOrEmpty()]
  31. [string]$output
  32. )
  34. Begin {
  35. #Configure regular expression to match full path of each file
  36. [regex]$regex = "^\w\:\\"
  38. #Find the number of CSVs being imported
  39. $count = ($inputfile.count -1)
  41. #Create Excel Com Object
  42. $excel = new-object -com excel.application
  44. #Disable alerts
  45. $excel.DisplayAlerts = $False
  47. #Show Excel application
  48. $excel.Visible = $False
  50. #Add workbook
  51. $workbook = $excel.workbooks.Add()
  53. #Remove other worksheets
  54. $workbook.worksheets.Item(2).delete()
  55. #After the first worksheet is removed,the next one takes its place
  56. $workbook.worksheets.Item(2).delete()
  58. #Define initial worksheet number
  59. $i = 1
  60. }
  62. Process {
  63. ForEach ($input in $inputfile) {
  64. #If more than one file, create another worksheet for each file
  65. If ($i -gt 1) {
  66. $workbook.worksheets.Add() | Out-Null
  67. }
  68. #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
  69. $worksheet = $workbook.worksheets.Item(1)
  70. #Add name of CSV as worksheet name
  71. $worksheet.name = "$((GCI $input).basename)"
  73. #Open the CSV file in Excel, must be converted into complete path if no already done
  74. If ($regex.ismatch($input)) {
  75. $tempcsv = $excel.Workbooks.Open($input)
  76. }
  77. ElseIf ($regex.ismatch("$($input.fullname)")) {
  78. $tempcsv = $excel.Workbooks.Open("$($input.fullname)")
  79. }
  80. Else {
  81. $tempcsv = $excel.Workbooks.Open("$($pwd)\$input")
  82. }
  83. $tempsheet = $tempcsv.Worksheets.Item(1)
  84. #Copy contents of the CSV file
  85. $tempSheet.UsedRange.Copy() | Out-Null
  86. #Paste contents of CSV into existing workbook
  87. $worksheet.Paste()
  89. #Close temp workbook
  90. $tempcsv.close()
  92. #Select all used cells
  93. $range = $worksheet.UsedRange
  95. #Autofit the columns
  96. $range.EntireColumn.Autofit() | out-null
  97. $i++
  98. }
  99. }
  101. End {
  102. #Save spreadsheet
  103. $workbook.saveas("$pwd\$output")
  105. Write-Host -Fore Green "File saved to $pwd\$output"
  107. #Close Excel
  108. $excel.quit()
  110. #Release processes for Excel
  111. $a = Release-Ref($range)
  112. }
  113. }
Complete Code
  1. $LogTime = Get-Date -Format yyyy-MM-dd_hh-mm
  2. $LogFile = ".\DailyMonitoringPatch-$LogTime.rtf"
  4. # Add SharePoint PowerShell Snapin
  7. if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
  8. {
  9. Add-PSSnapin Microsoft.SharePoint.Powershell
  10. }
  11. import-module WebAdministration
  13. $scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent
  14. Set-Location $scriptBase
  16. write-host "TESTING FOR LOG FOLDER EXISTENCE" -fore yellow
  17. $TestLogFolder = test-path -path $scriptbase\Logs
  18. if($TestLogFolder)
  19. {
  20. write-host "The log folder already exist in the script location" -fore yellow
  21. $clearlogfolder = read-host "Do you want to clear the log folder (y/n)"
  22. if($clearlogfolder -eq 'y')
  23. {
  24. write-host "The user choosen to clear the log folder" -fore yellow
  25. write-host "Clearing the log folder" -fore yellow
  26. remove-item $scriptbase\Logs\* -recurse -confirm:$false
  27. write-host "Log folder cleared" -fore yellow
  28. }
  29. else
  30. {
  31. write-host "The user choosen not to clear the log files" -fore yellow
  32. }
  33. }
  34. else
  35. {
  36. write-host "Log folder does not exist" -fore yellow
  37. write-host "Creating a log folder" -fore yellow
  38. New-Item $Scriptbase\Logs -type directory
  39. write-host "Log folder created" -fore yellow
  40. }
  42. #moving any .rtf files in the scriptbase location
  43. $FindRTFFile = Get-ChildItem $scriptBase\*.* -include *.rtf
  44. if($FindRTFFile)
  45. {
  46. write-host "Some old log files are found in the script location" -fore yellow
  47. write-host "Moving old log files into the Logs folder" -fore yellow
  48. foreach($file in $FindRTFFile)
  49. {
  50. move-item -path $file -destination $scriptbase\logs
  51. }
  52. write-host "Old log files moved successfully" -fore yellow
  53. }
  55. start-transcript $logfile
  57. $global:timerServiceName = "SharePoint 2010 Timer"
  58. $global:timerServiceInstanceName = "Microsoft SharePoint Foundation Timer"
  60. # Get the local farm instance
  61. [Microsoft.SharePoint.Administration.SPFarm]$farm = [Microsoft.SharePoint.Administration.SPFarm]::get_Local()
  63. Function SharePointServices([Microsoft.SharePoint.Administration.SPFarm]$farm)
  64. {
  66. Write-Host ""
  67. write-host "Generating SharePoint services report" -fore Magenta
  69. $output = $scriptbase + "\" + "SharePointServices.csv"
  70. "ServiceName" + "," + "ServiceStatus" + "," + "MachineName" | Out-File -Encoding Default -FilePath $Output;
  72. foreach($server in $farm.Servers)
  73. {
  74. foreach($instance in $server.ServiceInstances)
  75. {
  76. # If the server has the timer service then stop the service
  77. if($instance.TypeName -eq $timerServiceInstanceName)
  78. {
  79. [string]$serverName = $server.Name
  80. write-host "Generating SP services report for server" $serverName -fore yellow
  81. $Monitor = "SPAdminV4" , "SPTimerV4" , "SPTraceV4" , "SPUserCodeV4" , "SPWriterV4" , "OSearch14" , "W3SVC" , "IISADMIN" , "C2WTS" , "FIMService" , "FIMSynchronizationService"
  82. $services = Get-Service -ComputerName $serverName -Name $Monitor -ea silentlycontinue
  83. foreach($service in $services)
  84. {
  85. $service.displayname + "," + $service.status + "," + $service.MachineName | Out-File -Encoding Default -Append -FilePath $Output;
  86. }
  87. write-host "SP services report generated" -fore green
  88. }
  89. }
  90. }
  92. }
  94. Function IISWebsite([Microsoft.SharePoint.Administration.SPFarm]$farm)
  95. {
  97. Write-Host ""
  98. write-host "Generating IIS website report" -fore Magenta
  100. $output = $scriptbase + "\" + "IISWebsite.csv"
  101. "WebSiteName" + "," + "WebsiteID" + "," + "WebSiteState" + "," + "Server" | Out-File -Encoding Default -FilePath $Output;
  103. foreach($server in $farm.Servers)
  104. {
  105. foreach($instance in $server.ServiceInstances)
  106. {
  107. # If the server has the timer service then stop the service
  108. if($instance.TypeName -eq $timerServiceInstanceName)
  109. {
  110. [string]$serverName = $server.Name
  111. write-host "Generating IIS website report for server" $serverName -fore yellow
  113. $status = ""
  114. $Sites = gwmi -namespace "root\webadministration" -Class site -ComputerName $serverName -Authentication PacketPrivacy -Impersonation Impersonate
  115. foreach($site in $sites)
  116. {
  117. if($site.getstate().returnvalue -eq 1)
  118. {
  119. $status = "Started"
  120. }
  121. else
  122. {
  123. $status = "Not Started"
  124. }
  127. $site.name + "," + $site.ID + "," + $Status + "," + $serverName | Out-File -Encoding Default -Append -FilePath $Output;
  128. }
  129. write-host "IIS website report generated" -fore green
  130. }
  131. }
  132. }
  134. }
  137. Function AppPoolStatus([Microsoft.SharePoint.Administration.SPFarm]$farm)
  138. {
  140. Write-Host ""
  141. write-host "Generating AppPool status report" -fore Magenta
  143. $output = $scriptbase + "\" + "AppPoolStatus.csv"
  144. "AppPoolName" + "," + "Status" + "," + "Server" | Out-File -Encoding Default -FilePath $Output;
  146. foreach($server in $farm.Servers)
  147. {
  148. foreach($instance in $server.ServiceInstances)
  149. {
  150. # If the server has the timer service then stop the service
  151. if($instance.TypeName -eq $timerServiceInstanceName)
  152. {
  153. [string]$serverName = $server.Name
  154. write-host "Generating AppPool status report for server" $serverName -fore yellow
  156. $status = ""
  157. $AppPools = gwmi -namespace "root\webadministration" -Class applicationpool -ComputerName $serverName -Authentication PacketPrivacy -Impersonation Impersonate
  158. foreach($AppPool in $AppPools )
  159. {
  160. if($AppPool.getstate().returnvalue -eq 1)
  161. {
  162. $status = "Started"
  163. }
  164. else
  165. {
  166. $status = "Stopped"
  167. }
  170. $AppPool.name + "," + $Status + "," + $serverName| Out-File -Encoding Default -Append -FilePath $Output;
  171. }
  172. write-host "AppPool status report generated" -fore green
  173. }
  174. }
  175. }
  177. }
  180. Function DiskSpace([Microsoft.SharePoint.Administration.SPFarm]$farm)
  181. {
  183. Write-Host ""
  184. write-host "Generating Disk space report" -fore Magenta
  186. $output = $scriptbase + "\" + "DiskSpace.csv"
  187. "Computer Name" + "," + "Drive" + "," + "Size in (GB)" + "," + "Free Space in (GB)" + "," + "Critical (*)" | Out-File -Encoding Default -FilePath $Output;
  189. foreach($server in $farm.Servers)
  190. {
  191. foreach($instance in $server.ServiceInstances)
  192. {
  193. # If the server has the timer service then stop the service
  194. if($instance.TypeName -eq $timerServiceInstanceName)
  195. {
  196. [string]$serverName = $server.Name
  197. write-host "Generating disk space report for server" $serverName -fore yellow
  199. $drives = Get-WmiObject -ComputerName $serverName Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
  201. foreach($drive in $drives)
  202. {
  203. $id = $drive.DeviceID
  205. $size = [math]::round($drive.Size / 1073741824, 2)
  207. $free = [math]::round($drive.FreeSpace / 1073741824, 2)
  209. $pct = [math]::round($free / $size, 2) * 100
  211. if ($pct -lt 30)
  212. {
  213. $pct = $pct.ToString() + "% *** "
  214. }
  215. else
  216. {
  217. $pct = $pct.ToString() + " %"
  218. }
  220. $serverName + "," + $id + "," + $size + "," + $free + "," + $pct | Out-File -Encoding Default -Append -FilePath $Output;
  221. $pct = 0
  222. }
  223. write-host "Disk space report generated" -fore green
  224. }
  225. }
  226. }
  228. }
  231. Function HealthAnalyserReports()
  232. {
  234. write-host ""
  235. write-host "Generating health analyser report" -fore magenta
  237. $output = $scriptbase + "\" + "HealthAnalyser.csv"
  238. "Severity" + "," + "Category" + "," + "Modified" + "," + "Failing servers" + "," + "Failing services" | Out-File -Encoding Default -FilePath $Output;
  240. $ReportsList = [Microsoft.SharePoint.Administration.Health.SPHealthReportsList]::Local
  241. $Items = $ReportsList.items | where {
  243. if($_['Severity'] -eq '1 - Error')
  244. {
  245. #write-host $_['Name']
  246. #write-host $_['Severity']
  247. $_['Severity'] + "," + $_['Category'] + "," + $_['Modified'] + "," + $_['Failing Servers'] + "," + $_['Failing Services'] | Out-File -Encoding Default -Append -FilePath $Output;
  248. }
  249. }
  250. write-host "Health analyser report generated" -fore green
  251. }
  255. Function CPUUtilization([Microsoft.SharePoint.Administration.SPFarm]$farm)
  256. {
  258. Write-Host ""
  259. write-host "Generating CPU utilization report" -fore Magenta
  261. $output = $scriptbase + "\" + "CPUUtilization.csv"
  262. "ServerName" + "," + "DeviceID" + "," + "LoadPercentage" + "," + "Status" | Out-File -Encoding Default -FilePath $Output;
  264. foreach($server in $farm.Servers)
  265. {
  266. foreach($instance in $server.ServiceInstances)
  267. {
  268. # If the server has the timer service then stop the service
  269. if($instance.TypeName -eq $timerServiceInstanceName)
  270. {
  271. [string]$serverName = $server.Name
  272. write-host "Generating CPU utilization report for server" $serverName -fore yellow
  273. $CPUDataCol = Get-WmiObject -Class Win32_Processor -ComputerName $ServerName
  274. foreach($Data in $CPUDataCol)
  275. {
  276. $serverName + "," + $Data.DeviceID + "," + $Data.loadpercentage + "," + $Data.status | Out-File -Encoding Default -Append -FilePath $Output;
  277. }
  278. write-host "CPU utilization report generated" -fore green
  279. }
  280. }
  281. }
  283. }
  286. Function MemoryUtilization([Microsoft.SharePoint.Administration.SPFarm]$farm)
  287. {
  289. Write-Host ""
  290. write-host "Memory utilization report" -fore Magenta
  292. $output = $scriptbase + "\" + "MemoryUtilization.csv"
  293. "ServerName" + "," + "FreePhysicalMemory" + "," + "TotalVisibleMemorySize" + "," + "Status" | Out-File -Encoding Default -FilePath $Output;
  295. foreach($server in $farm.Servers)
  296. {
  297. foreach($instance in $server.ServiceInstances)
  298. {
  299. # If the server has the timer service then stop the service
  300. if($instance.TypeName -eq $timerServiceInstanceName)
  301. {
  302. [string]$serverName = $server.Name
  303. write-host "Generating memory utilization report for server" $serverName -fore yellow
  304. $MemoryCol = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $ServerName
  305. foreach($Data in $MemoryCol)
  306. {
  307. $serverName + "," + $Data.FreePhysicalMemory + "," + $Data.TotalVisibleMemorySize + "," + $Data.status | Out-File -Encoding Default -Append -FilePath $Output;
  308. }
  309. write-host "Memory utilization report generated" -fore green
  310. }
  311. }
  312. }
  314. }
  317. Function SPServerStatus([Microsoft.SharePoint.Administration.SPFarm]$farm)
  318. {
  320. Write-Host ""
  321. write-host "SharePoint server status report" -fore Magenta
  323. $output = $scriptbase + "\" + "SPServerStatus.csv"
  324. "ServerName" + "," + "Role" + "," + "Status" + "," + "CanUpgrade" + "," + "NeedsUpgrade" | Out-File -Encoding Default -FilePath $Output;
  326. foreach($server in $farm.Servers)
  327. {
  328. foreach($instance in $server.ServiceInstances)
  329. {
  330. # If the server has the timer service then stop the service
  331. if($instance.TypeName -eq $timerServiceInstanceName)
  332. {
  334. $server.Name + "," + $server.role + "," + $server.status + "," + $server.canupgrade + "," + $server.NeedsUpgrade | Out-File -Encoding Default -Append -FilePath $Output;
  335. write-host "SP server status report generated" -fore green
  336. }
  337. }
  338. }
  340. }
  344. #######################Function to combine multiple CSV files into single excel sheet with seperated tabs for each CSV#########################
  346. Function Release-Ref ($ref)
  347. {
  348. ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
  349. [System.__ComObject]$ref) -gt 0)
  350. [System.GC]::Collect()
  351. [System.GC]::WaitForPendingFinalizers()
  352. }
  354. Function ConvertCSV-ToExcel
  355. {
  357. [CmdletBinding(
  358. SupportsShouldProcess = $True,
  359. ConfirmImpact = 'low',
  360. DefaultParameterSetName = 'file'
  361. )]
  362. Param (
  363. [Parameter(
  364. ValueFromPipeline=$True,
  365. Position=0,
  366. Mandatory=$True,
  367. HelpMessage="Name of CSV/s to import")]
  368. [ValidateNotNullOrEmpty()]
  369. [array]$inputfile,
  370. [Parameter(
  371. ValueFromPipeline=$False,
  372. Position=1,
  373. Mandatory=$True,
  374. HelpMessage="Name of excel file output")]
  375. [ValidateNotNullOrEmpty()]
  376. [string]$output
  377. )
  379. Begin {
  380. #Configure regular expression to match full path of each file
  381. [regex]$regex = "^\w\:\\"
  383. #Find the number of CSVs being imported
  384. $count = ($inputfile.count -1)
  386. #Create Excel Com Object
  387. $excel = new-object -com excel.application
  389. #Disable alerts
  390. $excel.DisplayAlerts = $False
  392. #Show Excel application
  393. $excel.Visible = $False
  395. #Add workbook
  396. $workbook = $excel.workbooks.Add()
  398. #Remove other worksheets
  399. $workbook.worksheets.Item(2).delete()
  400. #After the first worksheet is removed,the next one takes its place
  401. $workbook.worksheets.Item(2).delete()
  403. #Define initial worksheet number
  404. $i = 1
  405. }
  407. Process {
  408. ForEach ($input in $inputfile) {
  409. #If more than one file, create another worksheet for each file
  410. If ($i -gt 1) {
  411. $workbook.worksheets.Add() | Out-Null
  412. }
  413. #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
  414. $worksheet = $workbook.worksheets.Item(1)
  415. #Add name of CSV as worksheet name
  416. $worksheet.name = "$((GCI $input).basename)"
  418. #Open the CSV file in Excel, must be converted into complete path if no already done
  419. If ($regex.ismatch($input)) {
  420. $tempcsv = $excel.Workbooks.Open($input)
  421. }
  422. ElseIf ($regex.ismatch("$($input.fullname)")) {
  423. $tempcsv = $excel.Workbooks.Open("$($input.fullname)")
  424. }
  425. Else {
  426. $tempcsv = $excel.Workbooks.Open("$($pwd)\$input")
  427. }
  428. $tempsheet = $tempcsv.Worksheets.Item(1)
  429. #Copy contents of the CSV file
  430. $tempSheet.UsedRange.Copy() | Out-Null
  431. #Paste contents of CSV into existing workbook
  432. $worksheet.Paste()
  434. #Close temp workbook
  435. $tempcsv.close()
  437. #Select all used cells
  438. $range = $worksheet.UsedRange
  440. #Autofit the columns
  441. $range.EntireColumn.Autofit() | out-null
  442. $i++
  443. }
  444. }
  446. End {
  447. #Save spreadsheet
  448. $workbook.saveas("$pwd\$output")
  450. Write-Host -Fore Green "File saved to $pwd\$output"
  452. #Close Excel
  453. $excel.quit()
  455. #Release processes for Excel
  456. $a = Release-Ref($range)
  457. }
  458. }
  460. #################################################################################################################################################
  463. ##########Calling Functions#################
  464. SharePointServices $farm
  465. IISWebsite $farm
  466. AppPoolStatus $farm
  467. DiskSpace $farm
  468. HealthAnalyserReports
  469. CPUUtilization $farm
  470. MemoryUtilization $farm
  471. SPServerStatus $farm
  473. write-host ""
  474. write-host "Combining all CSV files into single file" -fore yellow
  475. Get-Item $scriptbase\*.csv | ConvertCSV-ToExcel -output "DailyMonitoringReports.xlsx"
  478. write-host ""
  479. write-host "SCRIPT COMPLETED" -fore green
  481. stop-transcript
Execution Procedure

Conclusion

Thus this article outlines how to automate daily monitoring tasks for SharePoint 2010 using a PowerShell script.