SHARE
TWEET

Untitled

a guest Oct 21st, 2019 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <#
  2. .SYNOPSIS
  3. Create Excel workbooks for Managers with Direct Reports.
  4.  
  5. .DESCRIPTION
  6. Used for auditing group membership for each Direct Report, and compiling the data into one excel document for each Manager.
  7. All Direct Reports have an individual Sheet. Each manager will have their own workbook.
  8.  
  9. This command can be run for all managers in an organization, or a single manager.
  10. By default, all AD Users with Direct Reports will be processed.
  11.  
  12. Use the -ManagerUsername parameter to specify an individual manager by username.
  13. Use the -Path parameter to specify a destination folder for generated Excel workbooks.
  14. Use the -ShowProgress parameter to display a progress bar during processing.
  15.  
  16. By default, the Excel workbooks will be saved in the current PowerShell Working Directory. ($pwd)
  17. To specify a location, use the -Path parameter.
  18.  
  19. The script can also attach and email the Excel workbook to the manager using the -EmailToManager parameter.
  20. IMPORTANT: Be sure to enter your Email Server information in the script.
  21.  
  22. Try 'Get-Help Get-ManagerReport -Examples' for more information.
  23. Use the -Verbose flag for progress information.
  24.  
  25. .EXAMPLE
  26. Get-ManagerReport
  27.  
  28. Queries Active Directory for all users with Direct Reports.
  29. Gathers all Groups for each Direct Report, and creates a workbook for each manager, saving at the default location.
  30.  
  31. .EXAMPLE
  32. Get-ManagerReport -ShowProgress
  33.  
  34. Queries Active Directory for all users with Direct Reports.
  35. Gathers all Groups for each Direct Report, and creates a workbook for each manager, saving at the default location.
  36. Displays a progress bar of current progress.
  37.  
  38. .EXAMPLE
  39. Get-ManagerReport -ManagerUsername jsmith
  40.  
  41. Queries Active Directory for all Direct Reports of John Smith (username jsmith).
  42. Gathers all Groups for each Direct Report, and creates a workbook for John Smith, saving at the default location.
  43.  
  44. .EXAMPLE
  45. Get-ManagerReport -ManagerUsername jsmith -Path C:\Temp\Reports
  46.  
  47. Queries Active Directory for all Direct Reports of John Smith (username jsmith).
  48. Gathers all Groups for each Direct Report, and creates a workbook for John Smith, saving in C:\Temp\Reports.
  49.  
  50. .EXAMPLE
  51. Get-ManagerReport -path "\\Server\Manager Reports" -EmailToManager
  52.  
  53. Queries Active Directory for all users with Direct Reports.
  54. Gathers all Groups for each Direct Report, and creates a workbook for each manager, saving in \\Server\Manager Reports.
  55. Emails each workbook to it's respective manager.
  56.  
  57. .EXAMPLE
  58. Get-ManagerReport -ManagerUsername jsmith -Path C:\Temp\Reports -EmailToManager
  59.  
  60. Queries Active Directory for all Direct Reports of John Smith (username jsmith).
  61. Gathers all Groups for each Direct Report, and creates a workbook for John Smith, saving in C:\Temp\Reports.
  62. Emails the created workbook to John Smith.
  63.  
  64. .NOTES
  65. Author: Dom Ruggeri, 2019
  66.  
  67. #>
  68.  
  69. function Get-ManagerReport {
  70.     [cmdletbinding()]
  71.    
  72.     Param (
  73.         [Parameter(Position = 0)]
  74.         [string]$ManagerUsername,
  75.        
  76.         [Parameter(Position = 1)]
  77.         [validatePattern("(^\\\\|[A-Z]\:\\|\.\\).*")]
  78.         [string]$Path = $PWD.Path,
  79.  
  80.         [Parameter()]
  81.         [switch]$EmailToManager,
  82.  
  83.         [Parameter()]
  84.         [switch]$ShowProgress
  85.     )
  86.  
  87.     ############################################################################
  88.  
  89.     # Enter your Email Settings here:
  90.     $smtpServer = "Relay.yourdomain.com"
  91.     $From = "Direct Reports <DirectReports@yourdomain.com>"
  92.     $Subject = "Direct Reports Group List - $(Get-date)"
  93.    
  94.     # Note: To CC an email address, you must also uncomment line 194
  95.     # $CC = "youremail@yourdomain.com"
  96.  
  97.     # Enter your preferred Excel Table Style here:
  98.     $TableStyle = "Medium2"
  99.  
  100.     ############################################################################
  101.  
  102.     If (!(Get-Module -ListAvailable -Name ImportExcel)){
  103.         try {
  104.             Write-Verbose "ImportExcel module not found. Attempting installation." -Verbose
  105.             Install-Module -Name ImportExcel -Force -ErrorAction Stop
  106.             Write-Verbose "ImportExcel successfully installed." -Verbose
  107.         }
  108.         catch {
  109.             Write-Verbose "ImportExcel could not be installed for the following reason:" -Verbose
  110.             Write-Error $_
  111.             break
  112.         }
  113.     }
  114.  
  115.     If ($ManagerUsername){
  116.         $ADUserParam = @{
  117.             Properties = "DirectReports","emailaddress"
  118.             Identity = $ManagerUsername
  119.         }
  120.     }
  121.     else{
  122.         $ADUserParam = @{
  123.             Properties = "DirectReports","emailaddress"
  124.             filter = {DirectReports -like '*'}
  125.         }
  126.     }
  127.  
  128.     $Path = $Path.TrimEnd("\")
  129.  
  130.     Write-Verbose "Gathering All Manager Information."
  131.     $Managers = Get-ADUser @ADUserParam
  132.     $ManagerCount = $managers.Count
  133.     $ManagerCurrent = 0
  134.     Write-Verbose "$ManagerCount Managers Found."
  135.  
  136.     foreach ($Manager in $Managers){
  137.         Write-Verbose "Processing $($Manager.Name)."
  138.  
  139.         $ReportName = "DirectReports-$($Manager.samaccountname).xlsx"
  140.  
  141.         $ManagerPath = "$Path\$ReportName"
  142.         $DRCount = ($Manager.DirectReports | measure).Count
  143.         Write-Verbose "[$($manager.name)] $DRCount Direct Reports Found."
  144.  
  145.         if ($ShowProgress -and $ManagerCount -gt 1){
  146.             $ManagerCurrent++
  147.             $ManagerPercent = [math]::Round(($ManagerCurrent/$ManagerCount)*100)
  148.             Write-Progress -Activity "Processing $($Manager.Name)..." -Status "$ManagerPercent% Complete - ($ManagerCurrent/$ManagerCount)" -Id 1 -PercentComplete $ManagerPercent
  149.         }
  150.  
  151.         $DRCurrent = 0
  152.  
  153.         Foreach ($DirectReport in $Manager.DirectReports){
  154.             $DR = Get-ADUser $DirectReport
  155.             Write-Verbose "[$($manager.name)] Processing Direct Report $($DR.Name)."
  156.  
  157.             if ($ShowProgress -and $DRCount -gt 1){
  158.                 $DRCurrent++
  159.                 $DRPercent = [math]::Round(($DRCurrent/$DRCount)*100)
  160.                 Write-Progress -Activity "Processing $($DR.Name)..." -Status "$DRPercent% Complete - ($DRCurrent/$DRCount)" -ParentId 1 -PercentComplete $DRPercent
  161.             }
  162.  
  163.             $DRGroups = Get-ADPrincipalGroupMembership $DR
  164.  
  165.             $SheetParams = @{
  166.                 Path = $ManagerPath
  167.                 WorksheetName = $DR.name
  168.                 TableName = $DR.Samaccountname
  169.                 TableStyle = $TableStyle
  170.                 FreezeTopRow = $true
  171.                 AutoSize = $true
  172.                 AutoFilter = $true
  173.                 WarningAction = "SilentlyContinue"
  174.             }
  175.  
  176.             Write-Verbose "[$($manager.name)] Exporting Excel Sheet for Direct Report $($DR.Name)."
  177.             $DRGroups | Select-Object Name,GroupCategory,DistinguishedName | Export-Excel @SheetParams
  178.             Write-Verbose "[$($manager.name)] Direct Report $($DR.Name) completed."
  179.         }
  180.  
  181.         Write-Verbose "[$($manager.name)] Report Completed."
  182.         Write-Verbose "[$($manager.name)] Report Path: $ManagerPath."
  183.  
  184.         if ($EmailToManager){
  185.             if ($Manager.emailaddress){
  186.                 Write-Verbose "[$($manager.name)] Emailing Report to $($Manager.emailaddress)"
  187.  
  188.                 $EmailParams = @{
  189.                     To = $Manager.emailaddress
  190.                     From = $From
  191.                     Subject = $Subject
  192.                     smtpserver = $smtpServer
  193.                     Attachments = $ManagerPath
  194.                     #cc = $cc
  195.                 }
  196.                 Send-MailMessage @EmailParams
  197.             }
  198.             else {
  199.                 Write-Verbose "[$($manager.name)] Could not find email address for $($manager.name)"
  200.             }
  201.         }
  202.     }
  203.     Write-Verbose "Complete: All Manager Direct Reports Processed."
  204. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top