Trying to incorporate a csv export with a powershell script.

Question

I am exported a directory scan from TreeSize. The CSV looks like this:

TreeSize Report, 2/15/2024  10:50 AM
  E:\Folder\Users\  on  [Data] -   Filter: Name does not equal "~snapshot"
Drive: E:\      Size: 1992277.0 MB      Used: 850815.5 MB      Free: 1141461.5 MB     

Name,Path,Allocated,Files,Folders
"E:\Folder\Users\  on  [Data]","E:\Folder\Users\",556110.0 MB,795745,81199
"UserA","E:\Folder\Users\UserA\",16471.4 MB,10224,933
"UserB","E:\Folder\Users\UserB\",4408.3 MB,2642,184
"UserC","E:\Folder\Users\UserC\",704.5 MB,848,97
"UserD","E:\Folder\Users\UserD\",9440.0 MB,19158,813

I am trying to have a script that checks the group "users-group" and for each member of that group, it finds the corresponding user in the TreeSize CSV and adds the Allocated, Files, Folders value to an email that it then sends out.

This is the script I currently have:

$recipients = "admin@domain.local"
$sender = "Users-Group@domain.local"
$subject = "User Report"
$groupMembers = Get-ADGroupMember -Identity "users-group" -Recursive | Get-ADUser -Properties displayName, Mail, homeDirectory, Department | Select-Object displayName, Mail, homeDirectory, Department
$treeSizeReport = Import-Csv "\\domain.local\dfs\Share\Scripts\Datafiles\TreeSize-Report.csv"  # Combine the information from the two sources
$combinedData = foreach ($user in $groupMembers) {
    $matchingRow = $treeSizeReport | Where-Object { $_.Name -eq $user.displayName }

    [PSCustomObject]@{
        DisplayName   = $user.displayName
        Mail          = $user.Mail
        HomeDirectory = $user.homeDirectory
        Department    = $user.Department
        Allocated     = $matchingRow.Allocated
        Files         = $matchingRow.Files
        Folders       = $matchingRow.Folders
    }
}

# Sort the combined data by DisplayName
$sortedData = $combinedData | Sort-Object DisplayName

# Convert to HTML for email body
$body = $sortedData | ConvertTo-Html | Out-String

# Send email
Send-MailMessage -To $recipients -From $sender -Subject $subject -Body $body -BodyAsHtml -SmtpServer "smtp.domain.local" 

Unfortunately, the script is not working correctly and the Allocated, Files, and Folders values are not being added to the email. I am not sure what is wrong. Any help would be greatly appreciated.

Answer 1

Ok, after working on this all morning I was able to get it working. Here is the script as it exists now. I also modified the CSV export so it would not include the header information.

$csvPath = "\\domain.local\dfs\STS\Scripts\Datafiles\TreeSize-Report.csv"

# Read the CSV file
$csvData = Import-Csv -Path $csvPath -Header "Username", "Allocated Size", "Number of Files", "Number of Folders", "Path"

# Get users from the "users-App" group
$members = Get-ADGroupMember -Identity "users-App" -Recursive | Get-ADUser -Properties sAMAccountName, displayName, Mail, homeDirectory, Department |
    Select-Object sAMAccountName, displayName, Mail, homeDirectory, Department |
    Sort-Object displayName

# Initialize an array to collect users with no data
$usersWithNoData = @()

# Email configuration
$recipients = "App-DailyReport@domain.local"
$sender = "Users-App@domain.local"
$subject = "App User Backup Report"

# Prepare HTML body
$htmlBody = @"
<style>
    table {
        border-collapse: collapse;
        width: 100%;
    }
    th, td {
        border: 1px solid black;
        padding: 8px;
        text-align: left;
    }
</style>
<table>
    <tr>
        <th>Display Name</th>
        <th>Mail</th>
        <th>Home Directory</th>
        <th>Department</th>
        <th>Allocated Size</th>
        <th>Number of Files</th>
        <th>Number of Folders</th>
    </tr>
"@

# Loop through users and add data to HTML body
foreach ($member in $members) {
    $trimmedSAM = $member.sAMAccountName.Trim()
    $userData = $csvData | Where-Object { $_."Username".Trim() -eq $trimmedSAM }

    # Check if user is found in CSV data
    if ($userData) {
        $displayName = $member.displayName
        $mail = $member.Mail
        $homeDirectory = $member.homeDirectory
        $department = $member.Department
        $allocatedSize = $userData."Allocated Size"
        $numFiles = $userData."Number of Files"
        $numFolders = $userData."Number of Folders"

        $htmlBody += @"
<tr>
    <td>$displayName</td>
    <td>$mail</td>
    <td>$homeDirectory</td>
    <td>$department</td>
    <td>$allocatedSize</td>
    <td>$numFiles</td>
    <td>$numFolders</td>
</tr>
"@
    }
    else {
        # Collect users with no data in the array
        $usersWithNoData += $trimmedSAM
    }
}

# Close the HTML table
$htmlBody += "</table>"

# Add section for users with no data
$htmlBody += @"
<br>
<h3>Users with No Data:</h3>
<ul>
"@
foreach ($user in $usersWithNoData) {
    $htmlBody += "<li>$user</li>"
}
$htmlBody += "</ul>"

# Send email
Send-MailMessage -To $recipients -From $sender -Subject $subject -Body $htmlBody -BodyAsHtml -SmtpServer "smtp.domain.local"

Answer 2

Duplicate Entry

Answer 3

Hi Vincent Sprague,

The CSV file should begin with the column headers. You need to remove the 4 lines before the column headers or comment them out with "#" in the CSV file.

Best Regards,

Ian Xue

---

If the Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.