Hello! We are working with Quest PSO and currently have need of a complicated NTFS permissions report. We need to combine info from both NTFS and AD into a single report. The report needs multiple tabs, so needs to export as an Excel file. The report is for entitlement reviews of specific executable file paths. We will enter a path to run the report for. The report should pull folder permissions for the path and all subfolders. Report would be similar to the existing User Permissions on Folders report, but with additional fields and pulling in AD information, then adding additional tabs. Sample report and the current powershell script we use to create this report are attached..
The following information is needed on the first tab (named BrowsePerms, if possible):
From NTSF, we need Account Name, Permissions, Inheritance, Folder/Group and Path
From AD, we need User Name (that ties to Account Name), Description, Manager email (If Account Name is a user) or Owner’s email (if Account Name is an AD group), and the Manager/Owner’s Manager’s email.
Additionally, we would like some placeholder and pre-filled fields built into the report. We want blank columns with these names: Reviewer, Environment, AGT ER Ticket. We need these columns to be prefilled: Action, prefilled with ‘Please Select Action’, Developer, prefilled with ‘Please Select Choice’ and Non-Individual Solution, prefilled with ‘Please Select Choice’.
Order of fields should be: Reviewer, Action, Developer, Non-Individual Solution, Account Name, User Name, Description, Permissions, Applies To, Environment, Folder/Group, Path, Manager/Owner, Escalation Manager, AGT ER Ticket
Report should be sorted by Path, and then by Manager/Owner, then by Account Name.
The second tab (named BUILTIN_Administrators, if possible) should include the following:
For the path specified, show all AD groups and/or users in BUILTIN\Administrators.
From NTSF pull Account Name, Permissions, Folder/Group and Path.
From AD, we need User Name (that ties to Account Name, if a user), Description, Manager email (If Account Name is a user) or Owner’s email (if Account Name is an AD group), and the Manager/Owner’s Manager’s email.
Additionally, we would like some placeholder and pre-filled fields built into the report. We want blank columns with these names: Reviewer, Environment, AGT ER Ticket. We need these columns to be prefilled: Action, prefilled with ‘Please Select Action’, Developer, prefilled with ‘Please Select Choice’ and Non-Individual Solution, prefilled with ‘Please Select Choice’.
Order of fields should be: Reviewer, Action, Developer, Non-Individual Solution, Account Name, User Name, Description, Permissions, Environment, Folder/Group, Path, Manager/Owner, Escalation Manager, AGT ER Ticket
Report should be sorted by Manager/Owner, then by Account Name.
Third tab will be a membership report including user information from each AD group that shows up in the reports on the first 2 tabs.
From AD: Group, Account Name, User Name, Manager email, Manager’s Manager’s email, Description (from user account), Group Description, Ownership
From NTSF: Permissions, Environment, Path
Blank/Prefilled fields: blank columns with these names: Reviewer, Environment, AGT ER Ticket. We need these columns to be prefilled: Action, prefilled with ‘Please Select Action’, Developer, prefilled with ‘Please Select Choice’ and Non-Individual Solution, prefilled with ‘Please Select Choice’.
Order of fields on tab 3 should be: Reviewer, Action, Developer, Non-Individual Solution, Account Name, User Name, Permissions, Environment, Group, Path, Manager/Owner, Escalation Manager, AGT ER Ticket, User Description, Group Description, Ownership
This tab should be sorted by Group, then Manager/Owner, then Account Name.
Report-ATPPerms.txt
<#
.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();