Generate Excel Report of all shared drives Information including Folder Name, Size and ACL permission

This PowerShell script is designed to gather and organize information about folders, their sizes, and their Access Control Lists (ACLs) from selected drives on a local computer, excluding the C drive. It utilizes the ImportExcel module to export the collected data into an Excel file for easy viewing and analysis.

Steps and Features:

1. The script starts by importing the ImportExcel module, which is required for exporting data to Excel.
2. It queries the system to obtain a list of all available drives (excluding the C drive) using the Get-WmiObject cmdlet. Drives with drive type 3 (local hard drives) are selected.
3. A data array is initialized to store the collected information.
4. For each drive, the script iterates through all subfolders in that drive using the Get-ChildItem cmdlet, filtering only directories (folders).
5. For each folder, the script gathers the following information:
   • Drive letter
   • Folder name
   • Folder size, calculated by summing the sizes of all files in the folder and its subfolders
   • ACL information, obtained using the Get-Acl cmdlet. The ACL data is formatted for display by combining identity references and filesystem rights for each entry.
6. The collected information for each folder is stored as a custom object in the data array.
7. Once all drives and folders are processed, the script specifies the desired output Excel file path.
8. The data in the array is then exported to an Excel file using the Export-Excel cmdlet. The -AutoSize parameter is used to adjust column widths automatically for better visibility, and the data is organized in a table named “FolderInformation” with an additional title “Folder Information.”
9. After the export process is complete, a message is displayed indicating that the Excel file has been generated.

Powershell script:

# Import the required module
Import-Module ImportExcel

# Get all available drives except C drive
$drives = Get-WmiObject Win32_LogicalDisk | Where-Object { $_.DriveType -eq 3 -and $_.DeviceID -ne 'C:' }

# Create an array to store the data
$data = @()

foreach ($drive in $drives) {
    $rootFolder = $drive.DeviceID + "\"
    
    # Get all subfolders in the current drive
    $subfolders = Get-ChildItem -Path $rootFolder -Directory -Recurse

    # Loop through each subfolder to gather information
    foreach ($folder in $subfolders) {
        $folderName = $folder.Name
        $folderSize = (Get-ChildItem -Path $folder.FullName -File -Recurse | Measure-Object -Property Length -Sum).Sum
        $folderSizeFormatted = "{0:N2}" -f ($folderSize / 1MB) + " MB"
        $aclInfo = Get-Acl -Path $folder.FullName

        $aclData = $aclInfo.Access | ForEach-Object {
            "$($_.IdentityReference): $($_.FileSystemRights)"
        }

        $data += [PSCustomObject]@{
            Drive = $drive.DeviceID
            FolderName = $folderName
            FolderSize = $folderSizeFormatted
            ACL = $aclData -join "`n"  # Join ACL entries with a new line
        }
    }
}

# Define the output Excel file path
$outputFilePath = "e:\FolderInformation.xlsx"

# Export the data to an Excel file
$data | Export-Excel -Path $outputFilePath -AutoSize -TableName "FolderInformation" -Title "Folder Information"

Write-Host "Excel file '$outputFilePath' generated."

Usage:

1. Make sure to have the ImportExcel module installed. You can install it using Install-Module -Name ImportExcel.
2. Adjust the output Excel file path as needed by modifying the $outputFilePath variable.
3. Run the script in PowerShell. It will iterate through the specified drives, collect folder information, and create an Excel file with organized data that includes drive information, folder names, sizes, and ACLs.
4. The generated Excel file can be opened using any spreadsheet software, presenting the collected information in a structured, easy-to-read format.