Powershell Script to Automate Daily Monitoring in SharePoint 2010

Reference is from the site:

http://www.c-sharpcorner.com/uploadfile/fb1b0e/powershell-script-to-automate-daily-monitoring-in-sharepoint/

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:

  • SharePoint services status
  • IISWebsite status
  • AppPool status
  • Disk space info
  • Health analyser report
  • CPU utilization
  • Memory utilization
  • SharePoint server status

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.   }
  33. }
Function 4

The following piece of code monitors disk space:

  1. Function DiskSpace([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.     Write-Host “”
  4.     write-host “Generating Disk space report” -fore Magenta
  5.     $output = $scriptbase + “\” + “DiskSpace.csv”
  6.     “Computer Name” + “,” + “Drive” + “,” + “Size in (GB)” + “,” + “Free Space in (GB)” + “,” + “Critical (*)”  | 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 disk space report for server” $serverName -fore yellow
  16.                 $drives = Get-WmiObject -ComputerName $serverName Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
  17.                 foreach($drive in $drives)
  18.                 {
  19.                     $id = $drive.DeviceID
  20.                     $size = [math]::round($drive.Size / 1073741824, 2)
  21.                     $free = [math]::round($drive.FreeSpace  / 1073741824, 2)
  22.                     $pct = [math]::round($free / $size, 2) * 100
  23.                     if ($pct -lt 30)
  24.                     {
  25.                         $pct = $pct.ToString() + “% *** “
  26.                     }
  27.                     else
  28.                     {
  29.                         $pct = $pct.ToString() + ” %”
  30.                     }
  31.                     $serverName + “,” + $id + “,” + $size + “,” + $free + “,” + $pct  | Out-File -Encoding Default  -Append -FilePath $Output;
  32.                     $pct = 0
  33.                 }
  34.                 write-host “Disk space report generated” -fore green
  35.             }
  36.         }
  37.     }
  38. }

Function 5

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

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

Function 6

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

  1. Function CPUUtilization([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.     Write-Host “”
  4.     write-host “Generating CPU utilization report” -fore Magenta
  5.     $output = $scriptbase + “\” + “CPUUtilization.csv”
  6.     “ServerName” + “,” + “DeviceID” + “,” + “LoadPercentage” + “,” + “Status” | 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 CPU utilization report for server” $serverName -fore yellow
  16.                 $CPUDataCol = Get-WmiObject -Class Win32_Processor -ComputerName $ServerName
  17.                 foreach($Data in $CPUDataCol)
  18.                 {
  19.                     $serverName + “,” + $Data.DeviceID + “,” + $Data.loadpercentage + “,” + $Data.status | Out-File -Encoding Default  -Append -FilePath $Output;
  20.                 }
  21.                 write-host “CPU utilization report generated” -fore green
  22.             }
  23.         }
  24.     }
  25. }
Function 7

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

  1. Function MemoryUtilization([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.     Write-Host “”
  4.     write-host “Memory utilization report” -fore Magenta
  5.     $output = $scriptbase + “\” + “MemoryUtilization.csv”
  6.     “ServerName” + “,” + “FreePhysicalMemory” + “,” + “TotalVisibleMemorySize” + “,” + “Status” | 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 memory utilization report for server” $serverName -fore yellow
  16.                 $MemoryCol = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $ServerName
  17.                 foreach($Data in $MemoryCol)
  18.                 {
  19.                     $serverName + “,” + $Data.FreePhysicalMemory + “,” + $Data.TotalVisibleMemorySize + “,” + $Data.status | Out-File -Encoding Default  -Append -FilePath $Output;
  20.                 }
  21.                 write-host “Memory utilization report generated” -fore green
  22.             }
  23.         }
  24.     }
  25. }

Function 8

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

  1. Function SPServerStatus([Microsoft.SharePoint.Administration.SPFarm]$farm)
  2. {
  3.     Write-Host “”
  4.     write-host “SharePoint server status report” -fore Magenta
  5.     $output = $scriptbase + “\” + “SPServerStatus.csv”
  6.     “ServerName” + “,” + “Role” + “,” + “Status” + “,” + “CanUpgrade” + “,” + “NeedsUpgrade” | 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.                 $server.Name + “,” + $server.role + “,” + $server.status + “,” + $server.canupgrade + “,” + $server.NeedsUpgrade | Out-File -Encoding Default  -Append -FilePath $Output;
  15.                 write-host “SP server status report generated” -fore green
  16.             }
  17.         }
  18.     }
  19. }

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

Execution Procedure

  • Step 1: Download and copy the script to the SharePoint server.
  • Step 2: Navigate to the script path.
  • Step 3: Execute the script as in the following:

Conclusion

Thus this article outlines how to automate daily monitoring tasks for SharePoint 2010 using a PowerShell script. dailymonitoring – download the full script from here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s