Powershell : Visualise your CSV

This particular article is about taking CSV files and making them look more exciting with HTML, obviously, the theme we’re going for minimalistic and professional here.

Obviously, the data in the CSV file needs to have attributes that you can apply the HDMI formatting to otherwise this won’t work.

Teams for Rooms Example

Let’s for this example use the Teams for room on Android post I did a couple of days ago, in that post an out of the devices available in the form of a CSV file and we need to make this look more professional by using the “device name” column and the searchable column called “health status”

Visualisations Required

In this particular result, we then need three visualizations, these are as follows:

Healthy : This will list all the rooms that do not require an upgrade that are in the state healthy
nonUrgent : these are the rooms that do not have the latest teams application installed on them and require an upgrade
Offline : These are rooms that do not appear online as reported by Teams, I have generally found this is because the IP address has changed and the off-line device has the old address (they should really be static addresses or if on DHCP they should be reserved)

All of these visualizations will contain a count of how many devices are in this category, this will be accompanied with a percentage and a progress bar that shows said percentage.

The script will also look for the latest CSV file in the current folder and apply the analysis to that CSV file, so you are not looking at older data.

This is what the end result visually looks like:

The for each nonUrgent device you will see the friendly name for the device, these are the devices that require the update:

This is the script for this report generator:

Script : ReportCSVGenerator.ps1

# Get the latest CSV file in the current directory

$latestCsv = Get-ChildItem -Filter "*.csv" | Sort-Object LastWriteTime -Descending | Select-Object -First 1

# Read the CSV file

$deviceData = Import-Csv -Path $latestCsv.FullName

# Create timestamp for the output file

$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

$outputFile = "health_status_report_$timestamp.html"

# Initialize counts

$healthyCounts = ($deviceData | Where-Object { $_.HealthStatus -eq "healthy" }).Count

$nonUrgentCounts = ($deviceData | Where-Object { $_.HealthStatus -eq "nonUrgent" }).Count

$offlineCounts = ($deviceData | Where-Object { $_.HealthStatus -eq "offline" }).Count

# Get rooms with issues

$nonUrgentRooms = $deviceData | Where-Object { $_.HealthStatus -eq "nonUrgent" } | Select-Object -ExpandProperty RoomName | Sort-Object

$offlineRooms = $deviceData | Where-Object { $_.HealthStatus -eq "offline" } | Select-Object -ExpandProperty RoomName | Sort-Object

# Calculate percentages for progress bars

$totalDevices = $deviceData.Count

$healthyPercent = [math]::Round(($healthyCounts / $totalDevices) * 100, 1)

$nonUrgentPercent = [math]::Round(($nonUrgentCounts / $totalDevices) * 100, 1)

$offlinePercent = [math]::Round(($offlineCounts / $totalDevices) * 100, 1)

# Create HTML content

$html = @"

<!DOCTYPE html>

<head>

<title>Device Health Status Report</title>

<style>

body {

font-family: 'Segoe UI', Arial, sans-serif;

line-height: 1.6;

color: #333;

max-width: 1200px;

margin: 0 auto;

padding: 20px;

background-color: #f5f5f5;

}

.container {

background-color: white;

padding: 30px;

border-radius: 10px;

box-shadow: 0 2px 4px rgba(0,0,0,0.1);

}

.header {

text-align: center;

margin-bottom: 30px;

color: #2c3e50;

}

.timestamp {

color: #666;

font-size: 0.9em;

text-align: center;

margin-bottom: 30px;

}

.status-cards {

display: grid;

grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));

gap: 20px;

margin-bottom: 30px;

}

.card {

padding: 20px;

border-radius: 8px;

text-align: center;

}

.card.healthy {

background-color: #e8f5e9;

border: 1px solid #4caf50;

}

.card.nonurgent {

background-color: #fff3e0;

border: 1px solid #ff9800;

}

.card.offline {

background-color: #ffebee;

border: 1px solid #f44336;

}

.status-count {

font-size: 2em;

font-weight: bold;

margin: 10px 0;

}

.progress-bar {

background-color: #eee;

height: 10px;

border-radius: 5px;

margin-top: 10px;

}

.progress {

height: 100%;

border-radius: 5px;

transition: width 0.5s ease-in-out;

}

.healthy .progress { background-color: #4caf50; }

.nonurgent .progress { background-color: #ff9800; }

.offline .progress { background-color: #f44336; }

.details-section {

margin-top: 30px;

padding: 20px;

background-color: #f8f9fa;

border-radius: 8px;

}

.details-section h3 {

color: #2c3e50;

margin-top: 0;

}

.room-list {

list-style-type: none;

padding: 0;

margin: 0;

}

.room-list li {

padding: 8px 0;

border-bottom: 1px solid #eee;

}

.room-list li:last-child {

border-bottom: none;

}

</style>

</head>

<body>

<h1>Device Health Status Report</h1>

</div>

Generated on $(Get-Date -Format "dddd, MMMM dd, yyyy HH:mm:ss")

</div>

<h3>Healthy Devices</h3>

<div class="status-count">$healthyCounts</div>

</div>

<div>$healthyPercent% of total</div>

</div>

<h3>Non-Urgent Issues</h3>

<div class="status-count">$nonUrgentCounts</div>

</div>

<div>$nonUrgentPercent% of total</div>

</div>

<h3>Offline Devices</h3>

<div class="status-count">$offlineCounts</div>

</div>

<div>$offlinePercent% of total</div>

</div>

<h3>Devices Requiring Attention (Non-Urgent)</h3>

$(if ($nonUrgentRooms) {

$nonUrgentRooms | ForEach-Object { "<li>$_</li>" }

} else {

"<li>No devices requiring attention</li>"

})

</ul>

</div>

<h3>Offline Devices (Urgent)</h3>

$(if ($offlineRooms) {

$offlineRooms | ForEach-Object { "<li>$_</li>" }

} else {

"<li>No offline devices</li>"

})

</ul>

</div>

</body>

</html>

# Save the HTML report

$html | Out-File -FilePath $outputFile -Encoding UTF8

# Display confirmation

Write-Host "Report generated: $outputFile" -ForegroundColor Green

Write-Host "`nSummary:"

Write-Host "Healthy: $healthyCounts" -ForegroundColor Green

Write-Host "Non-Urgent Issues: $nonUrgentCounts" -ForegroundColor Yellow

Write-Host "Offline: $offlineCounts" -ForegroundColor Red

If you have a report that runs that is not mailed out it will not be actioned, so the script below will mail this report out to people that need it, this will only e-mail when the count of nonHealthy devices reaches over 10, so if its under this you get no email.

This script also requires a html file with the content of the message that you can design to your own desires and it will use the latest CSV file in the folder it is run from that should include the CSV report:

Script : smtp-mailer.ps1

# Function to write verbose output

function Write-Log {

param($Message)

Write-Host "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss'): $Message"

}

Write-Log "Script started - Searching for most recent CSV file"

# Get the most recent CSV file in current directory

$csvFile = Get-ChildItem -Filter "*.csv" | Sort-Object LastWriteTime -Descending | Select-Object -First 1

Write-Log "Found CSV file: $($csvFile.Name)"

# Read and analyze the CSV

Write-Log "Reading CSV file..."

$devices = Import-Csv $csvFile

Write-Log "Total devices found in CSV: $($devices.Count)"

$noUrgentCount = ($devices | Where-Object { $_.HealthStatus -eq "nonUrgent" } | Measure-Object).Count

Write-Log "Devices with 'nonUrgent' status: $noUrgentCount"

if ($noUrgentCount -gt 10) {

Write-Log "noUrgent count exceeds threshold (10). Preparing email..."

# Read HTML template

Write-Log "Reading HTML template from Teams.html"

$htmlBody = Get-Content ".\Teams.html" -Raw

Write-Log "HTML template loaded successfully"

# Configure email

Write-Log "Configuring email parameters..."

$emailParams = @{

From = "teams.alert@croucher.cloud"

To = "lee@croucher.cloud"

Subject = "Teams : Meeting Room Patch Status"

Body = $htmlBody

BodyAsHtml = $true

SmtpServer = "smtpmail.bear.local"

Attachments = $csvFile.FullName

}

# Send email

Write-Log "Attempting to send email..."

try {

Send-MailMessage @emailParams

Write-Log "Email sent successfully"

}

catch {

Write-Log "ERROR: Failed to send email: $($_.Exception.Message)"

}

else {

Write-Log "noUrgent count ($noUrgentCount) does not exceed threshold. No email sent."

}

Write-Log "Script completed"

If you would like the latest HTML file attaching then you can use the script below which does the same job with a HTML file not a CSV:

# Function to write verbose output

function Write-Log {

param($Message)

Write-Host "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss'): $Message"

}

Write-Log "Script started - Searching for most recent HTML file"

# Get the most recent HTML file in current directory

$htmlFile = Get-ChildItem -Filter "*.html" | Sort-Object LastWriteTime -Descending | Select-Object -First 1

if ($htmlFile) {

Write-Log "Found HTML file: $($htmlFile.Name)"

# Read HTML template

Write-Log "Reading HTML file..."

$htmlBody = Get-Content $htmlFile.FullName -Raw

Write-Log "HTML template loaded successfully"

# Configure email

Write-Log "Configuring email parameters..."

$emailParams = @{

From = "teams.alert@croucher.cloud"

To = "lee@croucher.cloud"

Subject = "Teams : Meeting Room Patch Status"

Body = $htmlBody

BodyAsHtml = $true

SmtpServer = "smtpmail.bear.local"

Attachments = $htmlFile.FullName

}

# Send email

Write-Log "Attempting to send email..."

try {

Send-MailMessage @emailParams

Write-Log "Email sent successfully"

}

catch {

Write-Log "ERROR: Failed to send email: $($_.Exception.Message)"

}

else {

Write-Log "ERROR: No HTML file found in current directory"

}

Write-Log "Script completed"

Powershell : Visualise your CSV

نموذج الاتصال