<#
.SYNOPSIS
Generates a one tab report of the members of groups pulled from the specified file

.DESCRIPTION
Generates a member report based on the list of groups within the specified file. The resulting data is output onto one worksheet within an Excel workbook.

.PARAMETER GroupFile
A simple text file with the list of groups formatted either as DOMAIN\GROUPNAME or just GROUPNAME. If just GROUPNAME, you will be prompted for a domain name to be used with all groups listed within the text file.

.OUTPUTS
Excel workbook with all data on one worksheet.
#>

  # This is the path the report will be generated upon, enclosed in quote marks. Example: “\\server\folder”.
Param ($dirpath)

  # This pulls data from an updated csv file. It contains a list of group and the tickets that
  # maintain those groups.
$serveradmingroups = Import-Csv -Path "\\ccew-fileprint\data\Corporate\AIT Compliance and Audit\AGT_Regulatory_Audit\COMPLIANCE\Server Admin Groups Entitled.csv"
$servername = $dirpath.Split("\")[2]

  # The function “convertDN2DomainName” takes the Distinguished Name and parses it to generate the
  # Domain Name of the user (eg: (CN=Janssen\, Scott,OU=users,OU=CR,OU=AIT,DC=us,DC=aegon,DC=com is
  # parsed to just US)
function convertDN2DomainName([string]$fulldn)
{
  # Split the string on a comma and then only return the elements that have a DC=
  $tmp = $fulldn.split(",") | ? {$_ -like "DC=*"}
  $retval = "";
  foreach ($t in $tmp)
  {
      # Remove "dc=" from each element
      $retval= $retval + "." + $t -replace "DC=", "";

  }
  # Remove the first char since it will be a period
  $retval = $retval.toString().substring(1);
  return $retval;
}

  # Function that takes the individual paths fed through it and pulls the ACL data from each.
  # Applies the data to the BrowsePerms sheet.
function GetACLData($filepath)
{
  $tempacl = get-acl $filepath;

  foreach ($acc in $tempacl.access)
  {
    $acctname = $acc.FileSystemRights.ToString()
    $domain = $acctname.Split("\")[0]
    $user = $acctname.Split("\")[1]
    
    $permacctname = $acc.IdentityReference.ToString();
    $permacctdmn = $permacctname.Split("\")[0]
    $permacctusr = $permacctname.Split("\")[1]

    If ($permacctname -eq "Everyone" -or $permacctname -eq "CREATOR OWNER" -or $permacctname -eq "NT AUTHORITY\SYSTEM" -or $permacctname -eq "BUILTIN\Administrators" -or $permacctname -eq "BUILTIN\Users") {
        $permacctdesc = ""}
    ElseIf ((Get-ADObject -Filter {samaccountname -eq $permacctusr} -Server $permacctdmn).objectclass -eq "group") {
    $permacctdesc = (Get-ADGroup -Server $permacctdmn -Identity $permacctusr -Properties Description).Description}
    Else {$permacctdesc = (Get-ADUser -Server $permacctdmn -Identity $permacctusr -Properties Description).Description}

    $applyTo = ConvertSecurityFlags $acc.InheritanceFlags $acc.PropagationFlags;
    $excelrowcount = $PermSheet.UsedRange.Rows.Count + 1
    $PermSheet.Cells.Item($excelrowcount,1).Font.Size=9
    $PermSheet.Cells.Item($excelrowcount,2) = "Please Select Action"
    $PermSheet.Cells.Item($excelrowcount,2).Font.Size=9
    $PermSheet.Cells.Item($excelrowcount,3) = "Please Select Choice"
    $PermSheet.Cells.Item($excelrowcount,3).Font.Size=9
    $PermSheet.Cells.Item($excelrowcount,4) = "Please Select Choice"
    $PermSheet.Cells.Item($excelrowcount,4).Font.Size=9
    $PermSheet.cells.item($excelrowcount,5) = $permacctname;
    $PermSheet.Cells.Item($excelrowcount,5).Font.Size=9
    $PermSheet.Cells.Item($excelrowcount,6).Font.Size=9
    $PermSheet.Cells.Item($excelrowcount,7) = $permacctdesc;
    $PermSheet.Cells.Item($excelrowcount,7).Font.Size=9
    $PermSheet.cells.item($excelrowcount,8) = $acctname;
    $PermSheet.Cells.Item($excelrowcount,8).Font.Size=9
    $PermSheet.cells.item($excelrowcount,9) = $applyTo;
    $PermSheet.Cells.Item($excelrowcount,9).Font.Size=9
    $PermSheet.Cells.Item($excelrowcount,10).Font.Size=9
    $PermSheet.cells.item($excelrowcount,11) = $tempacl.owner;
    $PermSheet.Cells.Item($excelrowcount,11).Font.Size=9
    $PermSheet.cells.item($excelrowcount,12) = $filepath.ToString();
    $PermSheet.Cells.Item($excelrowcount,12).Font.Size=9
    $PermSheet.Cells.Item($excelrowcount,13).Font.Size=9
    $PermSheet.Cells.Item($excelrowcount,14).Font.Size=9
    $PermSheet.cells.item($excelrowcount,15) = ($serveradmingroups | where {$_.'AD or MF Group' -eq $user}).SRC
    $PermSheet.Cells.Item($excelrowcount,15).Font.Size=9
  }
}

  # This takes the data from the get-acl cmdlet and converts them into string values that you would
  # actually see if you went to Advanced Security from the Security tab when viewing a Folder's 
  # properties.
function ConvertSecurityFlags($inheritanceFlag, $propagateFlag)
{
  # This function accepts two parameters that we get when using the get-acl cmdlet and then
  # converts those values into the string value that you would actually see if you went to Advanced
  # Security from the Security tab when viewing a Folder's properties
  
  # The data for this function came from here: http://camillelemouellic.blog.com/2011/07/22/powershell-security-descriptors-inheritance-and-propagation-flag-possibilities/
  
  $retval="";
  if ($inheritanceFlag -eq "None" -And $propagateFlag -eq "InheritOnly"){$retval = "This folder only"}
  elseif ($inheritanceFlag -eq "None" -And $propagateFlag -eq "NoPropagateInherit"){$retval = "This folder only";}
  elseif ($inheritanceFlag -eq "None" -And $propagateFlag -eq "None"){$retval = "This folder only";}
  elseif ($inheritanceFlag -eq "ContainerInherit, ObjectInherit" -And $propagateFlag -eq "NoPropagateInherit"){$retval = "This folder, subfolders and files";}
  elseif ($inheritanceFlag -eq "ContainerInherit, ObjectInherit" -And $propagateFlag -eq "None"){$retval = "This folder, subfolders and files";}
  elseif ($inheritanceFlag -eq "ContainerInherit" -And $propagateFlag -eq "NoPropagateInherit"){$retval = "This folder and subfolders";}
  elseif ($inheritanceFlag -eq "ContainerInherit" -And $propagateFlag -eq "None"){$retval = "This folder and subfolders";}
  elseif ($inheritanceFlag -eq "ObjectInherit" -And $propagateFlag -eq "NoPropagateInherit"){$retval = "This folder and files";}
  elseif ($inheritanceFlag -eq "ObjectInherit" -And $propagateFlag -eq "None"){$retval = "This folder and files";}
  elseif ($inheritanceFlag -eq "ContainerInherit, ObjectInherit" -And $propagateFlag -eq "NoPropagateInherit, InheritOnly"){$retval = "Subfolders and files only";}
  elseif ($inheritanceFlag -eq "ContainerInherit, ObjectInherit" -And $propagateFlag -eq "InheritOnly"){$retval = "Subfolders and files only";}
  elseif ($inheritanceFlag -eq "ContainerInherit" -And $propagateFlag -eq "NoPropagateInherit, InheritOnly"){$retval = "Subfolders only";}
  elseif ($inheritanceFlag -eq "ContainerInherit" -And $propagateFlag -eq "InheritOnly"){$retval = "Subfolders only";}
  elseif ($inheritanceFlag -eq "ObjectInherit" -And $propagateFlag -eq "NoPropagateInherit, InheritOnly"){$retval = "Files Only";}
  elseif ($inheritanceFlag -eq "ObjectInherit" -And $propagateFlag -eq "InheritOnly"){$retval = "Files Only";}
  else {$retval = "Should never get here"}
  return $retval;
}

  # This function “EnumerateGroupMembership” is what generates the members of the each group that
  # passes through it and commits it to an Excel worksheet within the Excel workbook.
Function PopulateGroupMembership ($strIdRef)
{
  # Evaluates the format of the group. If it’s in “domain\group” format, split and assign
  # variables. 
  If ($strIdRef -like "*\*")
  {
    $arr = $strIdRef.Split("\")
        $d=$arr[0]
        $u=$arr[1]
  }
  # Else, just take the object as user and reference to the domain entered at the start of the
  # script.
  Else
  {
    $u = $strIdRef
  }

  $sag = ($serveradmingroups | where {$_.'AD or MF Group' -eq $u}).SRC

  # Test if the account exists. Flag a variable to mark as "Not Found"
  Try {$groupdata = Get-ADGroup -Server $d -Identity $u -Properties Description,Info} Catch {$groupdata = "Not Found"}

  If ($groupdata -ne "Not Found"){
  $testcount = Get-QADGroupMember -Identity $u -Service $d -SizeLimit 5 -WarningAction:SilentlyContinue
  }

  # Status text on screen
  Write-Host "Populate Group Membership: " $strIdRef -foreground "Cyan";

  # Performs a count of members in the group. If the count is less than one (effectively 0), then
  # create a filler object so it’s represented on the report.
  
  If ($groupdata -eq "Not Found") {
    New-Object PSObject -Property @{
    Name = "No Such Group"
    Group = $d + "\" + $u
    DistinguishedName = "No Such Group"
    Samaccountname = "nogroup"}
    }
  Elseif ($testcount.count -lt 1) {
    New-Object PSObject -Property @{
    Name = "Empty Group"
    Group = $d + "\" + $u
    DistinguishedName = "Empty, Group"
    Samaccountname = "egroup"}
    }
  Else {
    Get-QADGroupMember -Identity $u -Service $d -SizeLimit 0 -IncludedProperties SamAccountName,distinguishedName,Description,Manager,extensionAttribute6,DisplayName,EmployeeType | Select-Object @{Name="Group";Expression={$d + "\" + $u}},@{Name="grpdesc";Expression={$groupdata.description}},@{Name="grpinfo";Expression={$groupdata.info}},@{Name="sag";Expression={$sag}},Name,SamAccountName,DistinguishedName,extensionAttribute6,Description,Manager,DisplayName,Office,EmployeeType;
  }
}

  # This function creates the BuiltIn Admin tab in the workbook. Every server and every path has
  # the Built-In Admin group representing it, and the process for gathering the membership data
  # from that group is different than with AD groups.
function BuiltInAdmin ($srvname)
{
$builtadminsheet = $workbook.worksheets.add()
$builtadminsheet.Name = "BUILTIN_Administrators"

$builtadminsheet.cells.item(1,1) = "Reviewer";
$builtadminsheet.cells.item(1,1).Font.Bold=$True;
$builtadminsheet.cells.item(1,1).Font.Size=9;
$builtadminsheet.cells.item(1,2) = "Action";
$builtadminsheet.cells.item(1,2).Font.Bold=$True;
$builtadminsheet.cells.item(1,2).Font.Size=9;
$builtadminsheet.cells.item(1,3) = "Developer";
$builtadminsheet.cells.item(1,3).Font.Bold=$True;
$builtadminsheet.cells.item(1,3).Font.Size=9;
$builtadminsheet.cells.item(1,4) = "Non-Individual Solution";
$builtadminsheet.cells.item(1,4).Font.Bold=$True;
$builtadminsheet.cells.item(1,4).Font.Size=9;
$builtadminsheet.cells.item(1,5) = "Account Name";
$builtadminsheet.cells.item(1,5).Font.Bold=$True;
$builtadminsheet.cells.item(1,5).Font.Size=9;
$builtadminsheet.cells.item(1,6) = "User Name";
$builtadminsheet.cells.item(1,6).Font.Bold=$True;
$builtadminsheet.cells.item(1,6).Font.Size=9;
$builtadminsheet.cells.item(1,7) = "Description";
$builtadminsheet.cells.item(1,7).Font.Bold=$True;
$builtadminsheet.cells.item(1,7).Font.Size=9;
$builtadminsheet.cells.item(1,8) = "Permissions";
$builtadminsheet.cells.item(1,8).Font.Bold=$True;
$builtadminsheet.cells.item(1,8).Font.Size=9;
$builtadminsheet.cells.item(1,9) = "Environment";
$builtadminsheet.cells.item(1,9).Font.Bold=$True;
$builtadminsheet.cells.item(1,9).Font.Size=9;
$builtadminsheet.cells.item(1,10) = "Folder/Group";
$builtadminsheet.cells.item(1,10).Font.Bold=$True;
$builtadminsheet.cells.item(1,10).Font.Size=9;
$builtadminsheet.cells.item(1,11) = "Path";
$builtadminsheet.cells.item(1,11).Font.Bold=$True;
$builtadminsheet.cells.item(1,11).Font.Size=9;
$builtadminsheet.cells.item(1,12) = "Owner/Supervisor";
$builtadminsheet.cells.item(1,12).Font.Bold=$True;
$builtadminsheet.cells.item(1,12).Font.Size=9;
$builtadminsheet.cells.item(1,13) = "Escalation Manager";
$builtadminsheet.cells.item(1,13).Font.Bold=$True;
$builtadminsheet.cells.item(1,13).Font.Size=9;
$builtadminsheet.cells.item(1,14) = "AGT ER Ticket";
$builtadminsheet.cells.item(1,14).Font.Bold=$True;
$builtadminsheet.cells.item(1,14).Font.Size=9;
$builtadminsheet.cells.entireColumn.ColumnWidth=20
$builtadminsheet.cells.entireRow.RowHeight=12

$builtadminsheet.application.activewindow.splitcolumn = 0
$builtadminsheet.application.activewindow.splitrow = 1
$builtadminsheet.application.activewindow.freezepanes = $true

$headerRange = $builtadminsheet.Range("a1","n1")
$headerRange.AutoFilter() | Out-Null

$excelrowcount = 2

$domain = $srvname
$user = "Administrators"

$obj = [ADSI]("WinNT://$domain/$user")
$members = @($obj.psbase.Invoke("Members"))

foreach ($member in $members){
    $adspath = $member.GetType().InvokeMember('AdsPath','GetProperty',$null,$member,$null).Split("/")[2]
	$WinNTName = $member.GetType().InvokeMember('Name','GetProperty',$null,$member,$null)
	$winNTDomUser = $adspath + "\" + $winntName;

    If ($winNTDomUser -eq "Everyone" -or $winNTDomUser -eq "CREATOR OWNER" -or $winNTDomUser -eq "NT AUTHORITY\SYSTEM" -or $winNTDomUser -eq "BUILTIN\Administrators" -or $winNTDomUser -eq "BUILTIN\Users" -or $winNTDomUser -eq "US\lsrvsup" -or $winNTDomUser -eq "DS\lsrvsup") {
        $permacctdesc = ""}
    ElseIf ((Get-ADObject -Filter {samaccountname -eq $WinNTName} -Server $adspath).objectclass -eq "group") {
    $permacctdesc = (Get-ADGroup -Server $adspath -Identity $WinNTName -Properties Description).Description}
    Else {$permacctdesc = (Get-ADUser -Server $adspath -Identity $WinNTName -Properties Description).Description}

    If (($serveradmingroups | where {$_.'AD or MF Group' -eq "$WinNTName"}).SRC -ne $null){$grpowner = "AGT"}Else{$grpowner = ""}

    $builtadminsheet.cells.item($excelrowcount,1).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,2) = "Please Select Action"
    $builtadminsheet.cells.item($excelrowcount,2).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,3) = "Please Select Choice"
    $builtadminsheet.cells.item($excelrowcount,3).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,4) = "Please Select Choice"
    $builtadminsheet.cells.item($excelrowcount,4).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,5) = $winntDomUser
    $builtadminsheet.cells.item($excelrowcount,5).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,6).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,7) = $permacctdesc
    $builtadminsheet.cells.item($excelrowcount,7).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,8).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,9).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,10) = "BUILTIN\Administrators"
    $builtadminsheet.cells.item($excelrowcount,10).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,11).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,12) = $grpowner
    $builtadminsheet.cells.item($excelrowcount,12).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,13).Font.Size=9
    $builtadminsheet.cells.item($excelrowcount,14) = ($serveradmingroups | where {$_.'AD or MF Group' -eq "$WinNTName"}).SRC
    $builtadminsheet.cells.item($excelrowcount,14).Font.Size=9
	$excelrowcount += 1;

	$memberClass = $member.GetType().InvokeMember('Class','GetProperty',$null,$member,$null)
    }
}


  ## Start Script ##
  # Loading Quest Active Roles at the start of the script.
$QuestADLoadedByContractorEmail = $false
    If(!(Get-PSSnapIn Quest.ActiveRoles.ADManagement -ErrorAction:SilentlyContinue)) {
        If(!(Get-PSSnapIn Quest.ActiveRoles.ADManagement -Registered -ErrorAction:SilentlyContinue)) {
            Write-Error "Quest.ActiveRoles.ADManagement Snap-In Not Found! Exiting"; exit}
        else {
            Add-PSSnapIn Quest.ActiveRoles.ADManagement -ErrorAction:SilentlyContinue
            $QuestADLoadedByContractorEmail = $true
        }
    }

  # Disable to 1000 object size limit for all Quest AD commands.
Set-QADPSSnapinSettings -DefaultSizeLimit 0

  # Create the Excel file
Write-Host "Creating MS Excel File" -foreground "Green"
Write-Host " ";

$excel = New-Object -ComObject Excel.Application;
$excel.Visible = $False;

  # Add a workbook to the Excel file and name it.
$workbook = $excel.Workbooks.Add();

$permsheet = $workbook.worksheets | Where {$_.Name -eq "Sheet1"}
$Worksheet2 = $workbook.worksheets | Where {$_.Name -eq "Sheet2"}
$Worksheet3 = $workbook.worksheets | Where {$_.Name -eq "Sheet3"}

if ($Worksheet2 -ne $null) {$Worksheet2.Delete();}
if ($Worksheet3 -ne $null) {$Worksheet3.Delete();}

  # Create and format the BrowsePerms worksheet
$permsheet.name = "BrowsePerms"

$permsheet.cells.item(1,1) = "Reviewer";
$permsheet.cells.item(1,1).Font.Bold=$True;
$permsheet.cells.item(1,1).Font.Size=9;
$permsheet.cells.item(1,2) = "Action";
$permsheet.cells.item(1,2).Font.Bold=$True;
$permsheet.cells.item(1,2).Font.Size=9;
$permsheet.cells.item(1,3) = "Developer";
$permsheet.cells.item(1,3).Font.Bold=$True;
$permsheet.cells.item(1,3).Font.Size=9;
$permsheet.cells.item(1,4) = "Non-Individual Solution";
$permsheet.cells.item(1,4).Font.Bold=$True;
$permsheet.cells.item(1,4).Font.Size=9;
$permsheet.cells.item(1,5) = "Account Name";
$permsheet.cells.item(1,5).Font.Bold=$True;
$permsheet.cells.item(1,5).Font.Size=9;
$permsheet.cells.item(1,6) = "User Name";
$permsheet.cells.item(1,6).Font.Bold=$True;
$permsheet.cells.item(1,6).Font.Size=9;
$permsheet.cells.item(1,7) = "Description";
$permsheet.cells.item(1,7).Font.Bold=$True;
$permsheet.cells.item(1,7).Font.Size=9;
$permsheet.cells.item(1,8) = "Permissions";
$permsheet.cells.item(1,8).Font.Bold=$True;
$permsheet.cells.item(1,8).Font.Size=9;
$permsheet.cells.item(1,9) = "Applies To";
$permsheet.cells.item(1,9).Font.Bold=$True;
$permsheet.cells.item(1,9).Font.Size=9;
$permsheet.cells.item(1,10) = "Environment";
$permsheet.cells.item(1,10).Font.Bold=$True;
$permsheet.cells.item(1,10).Font.Size=9;
$permsheet.cells.item(1,11) = "Folder/Group";
$permsheet.cells.item(1,11).Font.Bold=$True;
$permsheet.cells.item(1,11).Font.Size=9;
$permsheet.cells.item(1,12) = "Path";
$permsheet.cells.item(1,12).Font.Bold=$True;
$permsheet.cells.item(1,12).Font.Size=9;
$permsheet.cells.item(1,13) = "Owner/Supervisor";
$permsheet.cells.item(1,13).Font.Bold=$True;
$permsheet.cells.item(1,13).Font.Size=9;
$permsheet.cells.item(1,14) = "Escalation Manager";
$permsheet.cells.item(1,14).Font.Bold=$True;
$permsheet.cells.item(1,14).Font.Size=9;
$permsheet.cells.item(1,15) = "AGT ER Ticket";
$permsheet.cells.item(1,15).Font.Bold=$True;
$permsheet.cells.item(1,15).Font.Size=9;
$headerRange = $permsheet.Range("a1","q1")
$headerRange.AutoFilter() | Out-Null
$permsheet.cells.entireColumn.ColumnWidth=25
$permsheet.cells.entireRow.RowHeight=12

  # Setup top row lock
$permsheet.application.activewindow.splitcolumn = 0
$permsheet.application.activewindow.splitrow = 1
$permsheet.application.activewindow.freezepanes = $true

$excelrowcount = 2

  # Clear the Command Prompt Window - same as "cls"
Clear-Host;
Write-Host "-----------------------------------------------------"  -foreground "Yellow";
Write-Host "Get-ACL Compliance Report: " $(Get-Date) -foreground "Yellow";
Write-Host "-----------------------------------------------------"  -foreground "Yellow";
Write-Host " ";

  # Staging $dirlist to include initial path and all subpaths
#$dirpath = "\\CRMSXPRD134\C$\Claims"
Write-Host "Gathering Directory Data" -foreground "green"
Write-Host " ";

  # Checks for child paths. If no child paths are found, sets the original path as the path list.
  # If there are child paths found, lists and adds them to an array to be processed.
If ((Get-ChildItem -recurse -attributes D $dirpath).FullName -eq $null) {
    $dirlist = $dirpath
    } Else {
    $dirchild = (Get-ChildItem -recurse -attributes D $dirpath).FullName
    $dirparen = (Get-Item $dirpath).FullName
    $dirlist = @()
    $dirlist = @($dirparen) + @($dirchild)
    }

Write-Host "Gathering ACL Data for the Paths" -foreground "Green"

  # Take list of directories and feed through the GetACLData function, pumping out data onto the 
  # BrowsePerms tab.
$dirlist | ForEach-Object {Write-Host "Get ACL Data for: " $_ -foreground "Magenta"; GetACLData $_;}

  # Removes “NT Authority”, “Creator Owner”, and BUILTIN\Users from the group membership function
$grouprange = $PermSheet.Cells.Item(1,3).EntireColumn.Value2 | select -Unique | Where-Object {$_ -ne "NT AUTHORITY\SYSTEM" -and $_ -ne "Creator Owner" -and $_ -ne "BUILTIN\Users"} | foreach {[string]$_}

# $xlFilterValues = 7
# $permfilter = @("FullControl","*Modify*")
# $headerRange = $permsheet.Range("a1","j1")
# $headerRange.AutoFilter(4,$permfilter,$xlFilterValues) | Out-Null
# $headerRange.AutoFilter(3,$grouprange,$xlFilterValues) | Out-Null

  # Removes “Account Name”, “NT Authority”, and “BUILTIN\Users” from group membership function
$permgrps = $PermSheet.Cells.Item(1,5).EntireColumn.Value2 | select -Unique | Where-Object {$_ -ne "Account Name" -and $_ -ne "NT AUTHORITY\SYSTEM" -and $_ -ne "BUILTIN\Administrators"} | foreach {[string]$_}

  # Gather the server's local admin group members

Write-Host "Gathering Server's Local Admin Group Members" -foreground "Green"

$admgrpdmn = $servername
$admgrpusr = "Administrators"
$admobj = [ADSI]("WinNT://$admgrpdmn/$admgrpusr")
$admmembers = @($admobj.psbase.Invoke("Members"))

$adminobj = foreach ($member in $admmembers){
    $adspath = $member.GetType().InvokeMember('AdsPath','GetProperty',$null,$member,$null).Split("/")[2];
    $WinNTName = $member.GetType().InvokeMember('Name','GetProperty',$null,$member,$null);
    $adspath + "\" + $winntName
    }

$admingrps = $adminobj | foreach {
    $domain = $_.Split("\")[0]
    $name = $_.Split("\")[1]
    If ((Get-ADObject -Filter {samaccountname -eq $name} -Server $domain).objectclass -eq "group") {$_}
}

# $grouplist = [string[]]$grouplist | where {$_ -ne ""}

  # Compile group list

$combgrps = @($permgrps) + @($admingrps) | Where-Object {$_ -like "*US\*" -or $_ -like "*AEGONUK\*" -or $_ -like "*DS\*" -or $_ -like "*NL\*"} | Select-Object -Unique

$grouplist = $combgrps | foreach {
    $domain = $_.Split("\")[0]
    $name = $_.Split("\")[1]
    If ((Get-ADObject -Filter {samaccountname -eq $name} -Server $domain).objectclass -eq "group") {$_}
}

$recgroups = foreach($member in $grouplist){
    $domain = $member.Split("\")[0]
    $groupname = $member.Split("\")[1]
    Get-QADGroupMember -Identity $groupname -Service $domain -SizeLimit 0 -Indirect -properties NTAccountName | where {$_.type -like "group"} | Select NTAccountName
}

$allgroups = $grouplist + $recgroups.ntaccountname | Select -Unique

  #Generate single page group membership report

Write-Host " ";
Write-Host "-----------------------------------------------------"  -foreground "Yellow";
Write-Host "Get Group Membership Report: " $(Get-Date) -foreground "Yellow";
Write-Host "-----------------------------------------------------"  -foreground "Yellow";
Write-Host " ";

$svcaccts = "SVC", "SHR", "SPT"

  # Create and format/prep the Excel worksheet
$GroupWkSht = $workbook.worksheets.add()
$GroupWkSht.name = "Group Report"
$excelrowcount = 2;

  # Header Row Names
$GroupWkSht.cells.item(1,1) = "Reviewer";
$GroupWkSht.cells.item(1,1).Font.Bold=$True;
$GroupWkSht.cells.item(1,1).Font.Size=9;
$GroupWkSht.cells.item(1,2) = "Action";
$GroupWkSht.cells.item(1,2).Font.Bold=$True;
$GroupWkSht.cells.item(1,2).Font.Size=9;
$GroupWkSht.cells.item(1,3) = "Developer";
$GroupWkSht.cells.item(1,3).Font.Bold=$True;
$GroupWkSht.cells.item(1,3).Font.Size=9;
$GroupWkSht.cells.item(1,4) = "Non-Individual Solution";
$GroupWkSht.cells.item(1,4).Font.Bold=$True;
$GroupWkSht.cells.item(1,4).Font.Size=9;
$GroupWkSht.cells.item(1,5) = "Account Name";
$GroupWkSht.cells.item(1,5).Font.Bold=$True;
$GroupWkSht.cells.item(1,5).Font.Size=9;
$GroupWkSht.cells.item(1,6) = "User Name";
$GroupWkSht.cells.item(1,6).Font.Bold=$True;
$GroupWkSht.cells.item(1,6).Font.Size=9;
$GroupWkSht.cells.item(1,7) = "Permissions";
$GroupWkSht.cells.item(1,7).Font.Bold=$True;
$GroupWkSht.cells.item(1,7).Font.Size=9;
$GroupWkSht.cells.item(1,8) = "Environment";
$GroupWkSht.cells.item(1,8).Font.Bold=$True;
$GroupWkSht.cells.item(1,8).Font.Size=9;
$GroupWkSht.cells.item(1,9) = "Folder/Group";
$GroupWkSht.cells.item(1,9).Font.Bold=$True;
$GroupWkSht.cells.item(1,9).Font.Size=9;
$GroupWkSht.cells.item(1,10) = "Path";
$GroupWkSht.cells.item(1,10).Font.Bold=$True;
$GroupWkSht.cells.item(1,10).Font.Size=9;
$GroupWkSht.cells.item(1,11) = "Owner/Supervisor";
$GroupWkSht.cells.item(1,11).Font.Bold=$True;
$GroupWkSht.cells.item(1,11).Font.Size=9;
$GroupWkSht.cells.item(1,12) = "Escalation Manager";
$GroupWkSht.cells.item(1,12).Font.Bold=$True;
$GroupWkSht.cells.item(1,12).Font.Size=9;
$GroupWkSht.cells.item(1,13) = "AGT ER Ticket";
$GroupWkSht.cells.item(1,13).Font.Bold=$True;
$GroupWkSht.cells.item(1,13).Font.Size=9;
$GroupWkSht.cells.item(1,14) = "User Description";
$GroupWkSht.cells.item(1,14).Font.Bold=$True;
$GroupWkSht.cells.item(1,14).Font.Size=9;
$GroupWkSht.cells.item(1,15) = "Group Description";
$GroupWkSht.cells.item(1,15).Font.Bold=$True;
$GroupWkSht.cells.item(1,15).Font.Size=9;
$GroupWkSht.cells.item(1,16) = "Ownership";
$GroupWkSht.cells.item(1,16).Font.Bold=$True;
$GroupWkSht.cells.item(1,16).Font.Size=9;
$GroupWkSht.cells.entireColumn.ColumnWidth=20;
$GroupWkSht.cells.entireRow.RowHeight=12;
  # Setup up header filter and lock
$headerRange = $GroupWkSht.Range("a1","p1")
$headerRange.AutoFilter() | Out-Null
$GroupWkSht.application.activewindow.splitcolumn = 0
$GroupWkSht.application.activewindow.splitrow = 1
$GroupWkSht.application.activewindow.freezepanes = $true

  # Feed each group through the PopulateGroupMembership function.
$groupdata = $allgroups | foreach {PopulateGroupMembership $_}

  # Figure out permsheet
$permrowcount = ($permsheet.UsedRange.Rows).count
$permnamecol = 3
$permpermscol = 4

#$groupdataset = $groupdata[6]

  # Feed each result into the Excel spreadsheet.
Foreach ($member in $groupdata)
{
# Following line is for debug purposes. Uncomment for debugging.
# Write-Host $member.samaccountname
  # Checks for empty groups and assigns "Empty Group" to variables to be displayed in 
  # spreadsheet.

If ($member.name -eq "Empty Group"){
  $groupuserid = "Empty Group"
  $usermanager = "Empty Group"
  $usermanagername = "No Manager Found"
  } Elseif ($member.name -eq "No Such Group"){
  $groupuserid = "No Such Group"
  $usermanager = "No Such Group"
  $usermanagername = "No Such Group"
  } Else {
    # Truncates domain name to just the first part, like US and AEGONUK rather than the whole thing.
  $fulldn = $member.distinguishedName
  $justdcs = $fulldn.split(",") | where {$_ -like "DC=*"}
  $domain= $justdcs[0] -replace "DC=", ""
  $groupuserid = $domain + "\" + $member.samaccountname
}

  # Since Manager comes in DN format, and sometimes isn't even available, First check if there 
  # is a manager name. If not, assign name and email address to a not found text string.
  # Included Escalation Manager.

$usermanager = $member.manager

If (!$usermanager){

  If ($svcaccts -contains $member.employeetype){
    $svcmgr = $member.office.Split(";")[0]
    $svcmanager = Get-ADUser -Filter {DisplayName -eq $svcmgr} -Server $domain -Properties Mail,Manager,DisplayName
        If (!$svcmanager){
            $svcmanager = Get-ADUser -Filter {DisplayName -eq $svcmgr} -Server US -Properties Mail,Manager,DisplayName
        } Else{
            $usermanagername = "No Manager Found"
            $usermanagermail = "No Manager Email Found"
            $escmanagername = "No Manager Found"
            $escmanagermail = "No Manager Found"
        }

    If (!$svcmanager) {
        $usermanagername = "No Manager Found"
        $usermanagermail = "No Manager Email Found"
        $escmanagername = "No Manager Found"
        $escmanagermail = "No Manager Found"
    } Else {
        $usermanagername = $svcmanager.DisplayName
        $usermanagermail = $svcmanager.Mail
        $escmanager = $svcmanager.manager
        $escdcs = $escmanager.split(",") | where {$_ -like "DC=*"}
        $escdomain= $escdcs[0] -replace "DC=", ""
        $escmanagername = (Get-ADUser -Identity $escmanager -Server $escdomain -Properties DisplayName).DisplayName
        $escmanagermail = (Get-ADUser -Identity $escmanager -Server $escdomain -Properties Mail).Mail
    }
  } Else{
    If ($member.name -like "*S-1-5*") {
        $groupuserid = $member.Name
        $memsam = $member.samaccountname
    } Else {
        If ($member.samaccountname.ToLower().StartsWith("adm")) {
            $admuser = $member.DisplayName -Replace "ADM ", ""
            $admuserdata = Get-ADUser -Filter {DisplayName -eq $admuser} -Server $domain -Properties Manager
            $admmgr = $admuserdata.manager
            $admdcs = $admmgr.split(",") | where {$_ -like "DC=*"}
            $admdomain= $admdcs[0] -replace "DC=", ""
            $usermanager = Get-ADUser -Identity $admmgr -Server $admdomain -Properties Mail,Manager,DisplayName
            $usermanagername = $usermanager.DisplayName
            $usermanagermail = $usermanager.mail
            $escmanager = $usermanager.manager
            $escdcs = $escmanager.split(",") | where {$_ -like "DC=*"}
            $escdomain= $escdcs[0] -replace "DC=", ""
            $escmanagername = (Get-ADUser -Identity $escmanager -Server $escdomain -Properties DisplayName).DisplayName
            $escmanagermail = (Get-ADUser -Identity $escmanager -Server $escdomain -Properties Mail).Mail
        } Else {
            If ($member.samaccountname.ToLower().StartsWith("dadm")) {
                $admuser = $member.DisplayName -Replace "DADM ", ""
                $admuserdata = Get-ADUser -Filter {DisplayName -eq $admuser} -Server $domain -Properties Manager
                    If (!$admuserdata) {
                        $usermanagername = "No Manager Found"
                        $usermanagermail = "No Manager Email Found"
                        $escmanagername = "No Manager Found"
                        $escmanagermail = "No Manager Found"
                    } Else {
                        $admmgr = $admuserdata.manager
                        $admdcs = $admmgr.split(",") | where {$_ -like "DC=*"}
                        $admdomain= $admdcs[0] -replace "DC=", ""
                        $usermanager = Get-ADUser -Identity $admmgr -Server $admdomain -Properties Mail,Manager,DisplayName
                        $usermanagername = $usermanager.DisplayName
                        $usermanagermail = $usermanager.mail
                        $escmanager = $usermanager.manager
                        $escdcs = $escmanager.split(",") | where {$_ -like "DC=*"}
                        $escdomain= $escdcs[0] -replace "DC=", ""
                        $escmanagername = (Get-ADUser -Identity $escmanager -Server $escdomain -Properties DisplayName).DisplayName
                        $escmanagermail = (Get-ADUser -Identity $escmanager -Server $escdomain -Properties Mail).Mail
                    }
            } Else {
                $usermanagername = "No Manager Found"
                $usermanagermail = "No Manager Email Found"
                $escmanagername = "No Manager Found"
                $escmanagermail = "No Manager Found"
            }   
    }
    }
  }
}

  # If manager name is found, run that DN name against AD and pull the display name and email 
  # address. Assign to variables.
  # Included Escalation Manager.

Else{
  $fullmanager = $member.manager
  $mgrdcs = $fullmanager.split(",") | where {$_ -like "DC=*"}
  $mgrdomain= $mgrdcs[0] -replace "DC=", ""
  $usermanager = Get-ADUser -Identity $fullmanager -Server $mgrdomain -Properties Mail,Manager,DisplayName
  $usermanagername = $usermanager.displayname
    If(!$usermanager.mail){
    $usermanagermail = "No Manager Email Found"}
    Else{
    $usermanagermail = $usermanager.mail}
  $escmanager = $usermanager.manager
    If(!$escmanager){
        $escmanagername = "No Manager Found"
        $escmanagermail = "No Manager Found"}
    Else{
        $escdcs = $escmanager.split(",") | where {$_ -like "DC=*"}
        $escdomain= $escdcs[0] -replace "DC=", ""
        $escmanagername = (Get-ADUser -Identity $escmanager -Server $escdomain -Properties DisplayName).DisplayName
        $escmanagermail = (Get-ADUser -Identity $escmanager -Server $escdomain -Properties Mail).Mail
        }
}

  # Assigns variables to data.
$groupid = $member.group
$username = $member.DisplayName
$userdescription = $member.description
$usercostcenter = $member.extensionAttribute6
$action = "Please Select Action"

  # Applies data to spreadsheet row.
$GroupWkSht.Cells.Item($excelrowcount,1).Font.Size=9
$GroupWkSht.cells.item($excelrowcount,2) = $action
$GroupWkSht.Cells.Item($excelrowcount,2).Font.Size=9
$GroupWkSht.cells.item($excelrowcount,3) = "Please Select Choice"
$GroupWkSht.Cells.Item($excelrowcount,3).Font.Size=9
$GroupWkSht.cells.item($excelrowcount,4) = "Please Select Choice"
$GroupWkSht.Cells.Item($excelrowcount,4).Font.Size=9
$GroupWkSht.cells.item($excelrowcount,5) = $groupuserid
$GroupWkSht.Cells.Item($excelrowcount,5).Font.Size=9
$GroupWkSht.cells.item($excelrowcount,6) = $username
$GroupWkSht.Cells.Item($excelrowcount,6).Font.Size=9
$GroupWkSht.Cells.Item($excelrowcount,7).Font.Size=9
$GroupWkSht.Cells.Item($excelrowcount,8).Font.Size=9
$GroupWkSht.cells.item($excelrowcount,9) = $groupid
$GroupWkSht.Cells.Item($excelrowcount,9).Font.Size=9
$GroupWkSht.Cells.Item($excelrowcount,10).Font.Size=9
$GroupWkSht.Cells.Item($excelrowcount,11) = $usermanagername
$GroupWkSht.Cells.Item($excelrowcount,11).Font.Size=9
$GroupWkSht.Cells.Item($excelrowcount,12) = $escmanagername
$GroupWkSht.Cells.Item($excelrowcount,12).Font.Size=9
$GroupWkSht.Cells.Item($excelrowcount,13) = $member.sag
$GroupWkSht.Cells.Item($excelrowcount,13).Font.Size=9
$GroupWkSht.Cells.Item($excelrowcount,14) = $userdescription
$GroupWkSht.Cells.Item($excelrowcount,14).Font.Size=9
$GroupWkSht.Cells.Item($excelrowcount,15) = $member.grpdesc
$GroupWkSht.Cells.Item($excelrowcount,15).Font.Size=9
$GroupWkSht.Cells.Item($excelrowcount,16) = $member.grpinfo
$GroupWkSht.Cells.Item($excelrowcount,16).Font.Size=9
$excelrowcount +=1
}

Write-Host " ";
Write-Host "-----------------------------------------------------"  -foreground "Yellow";
Write-Host "Generate Built-In Admin Report: " $(Get-Date) -foreground "Yellow";
Write-Host "-----------------------------------------------------"  -foreground "Yellow";
Write-Host " ";

BuiltInAdmin $servername

($workbook.worksheets | Where {$_.Name -eq "BUILTIN_Administrators"}).Move($workbook.sheets.item(1))
($workbook.worksheets | Where {$_.Name -eq "BrowsePerms"}).Move($workbook.sheets.item(1))

$datename = get-date
$uncargument = $dirpath.Replace("\","_");
$uncargument = $uncargument.Replace("__","");

$newFileName = (Get-Item -Path ".\" -verbose).fullname + "\" + $datename.Year.ToString("0000") + $datename.Month.ToString("00") + $datename.day.ToString("00") + "_" + $datename.hour.ToString("00") + $datename.minute.ToString("00") + $datename.second.ToString("00") + "_" + $uncargument + "-ATPPerms.xlsx";
$newFileName;

$workbook.SaveAs($newFileName);
$workbook.Saved = $True;
$workbook.Close();
$excel.Quit();