Powershell - Send CSV data to email in a table

Question

Right now my goal is to download a CSV containing threat data from a url, capture two columns, File Name and the DeviceName, both of which are headers in the CSV. I then want to export that data to a single line in an email, which will then go to two members on our security team to review in a plaintext format. The goal is to make it unnecessary for them to download and copy paste directly from the CSV, as there's a lot of extraneous data in the CSV.

It's a pretty simple script, so everything works fine. My question is: how do I pull two columns of data from the csv and write it to the email body? Ideally in some sort of table, so the two columns are aligned.

I've found some resources online, most notably a script that takes any Object from the PowerShell interface, convert the object (table) into HTML and then sends it by email: https://gallery.technet.microsoft.com/scriptcenter/bd0c8d87-466f-4488-92a2-0f726cb6f4cd

Which is perfect, since this is exactly what I want to do. I'm just not really sure how to properly pass the data from the CSV to the powershell function.

Note this is also my first project in powershell, and it's mostly cobbled together from technet documentation and scraps of code examples I've found online. I'm a junior sysadmin, so not a ton of programming background.

Thanks bunches!

Update 1

So managed to get a bit more success this time around, but unfortunately the emailed output is a garbled list of numbers that don't correspond to any data found within the CSV.

I'm pretty sure my error is somewhere in here, but I'm not quite sure where I'm failing:

$csv = Import-Csv $output | Select "File Name",DeviceName | ConvertTo-Html

The rest of my code is below:

<#
.SYNOPSIS
Send an email with an object in a pretty table
.DESCRIPTION
Send email
.PARAMETER InputObject
Any PSOBJECT or other Table
.PARAMETER Subject
The Subject of the email
.PARAMETER To
The To field is who receives the email
.PARAMETER From
The From address of the email
.PARAMETER CSS
This is the Cascading Style Sheet that will be used to Style the table
.PARAMETER SmtpServer
The SMTP relay server
.EXAMPLE
PS C:\> Send-HtmlEmail -InputObject (Get-process *vmware* | select CPU, WS) -Subject "This is a process report"
An example to send some process information to email recipient
.NOTES
NAME        :  Send-HtmlEmail
VERSION     :  1.1.0   
LAST UPDATED:  01/03/2013
AUTHOR      :  Milo
.INPUTS
None
.OUTPUTS
None
#> 

function Send-HTMLEmail {
#Requires -Version 2.0
[CmdletBinding()]
 Param 
   ([Parameter(Mandatory=$True,
               Position = 0,
               ValueFromPipeline=$true,
               ValueFromPipelineByPropertyName=$true,
               HelpMessage="Please enter the Inputobject")]
    $InputObject,
    [Parameter(Mandatory=$True,
               Position = 1,
               ValueFromPipeline=$true,
               ValueFromPipelineByPropertyName=$true,
               HelpMessage="Please enter the Subject")]
    [String]$Subject,    
    [Parameter(Mandatory=$False,
               Position = 2,
               HelpMessage="Please enter the To address")]    
    [String[]]$To = "smurphy@klick.com",
    [String]$From = "cylancereporting@klick.com",    
    [String]$CSS,
    [String]$SmtpServer ="smtp.klick.com"
   )#End Param

if (!$CSS)
{
    $CSS = @"
        <style type="text/css">
            table {
            font-family: Verdana;
            border-style: dashed;
            border-width: 1px;
            border-color: #FF6600;
            padding: 5px;
            background-color: #FFFFCC;
            table-layout: auto;
            text-align: center;
            font-size: 8pt;
            }

            table th {
            border-bottom-style: solid;
            border-bottom-width: 1px;
            font: bold
            }
            table td {
            border-top-style: solid;
            border-top-width: 1px;
            }
            .style1 {
            font-family: Courier New, Courier, monospace;
            font-weight:bold;
            font-size:small;
            }
            </style>
"@
}#End if

$HTMLDetails = @{
    Title = $Subject
    Head = $CSS
    }

$Splat = @{
    To         =$To
    Body       ="$($InputObject | ConvertTo-Html @HTMLDetails)"
    Subject    =$Subject
    SmtpServer =$SmtpServer
    From       =$From
    BodyAsHtml =$True
    }
    Send-MailMessage @Splat

}#Send-HTMLEmail


#Defines variables
$url = "https://protect.cylance.com/Reports/ThreatDataReportV1/threats/BE5ABB1717DB46978BED0AF14A308557"
$output = "$PWD\ThreatsDataReport.csv"

(New-Object System.Net.WebClient).DownloadFile($url, $output)
#Downloads the CSV from $url and saves it to $output

$csv = Import-Csv $output | Select "File Name",DeviceName | ConvertTo-Html

Send-HTMLEmail -InputObject ($csv) -Subject "Cylance: Weekly Summary"

Original Code

$url = "foo.com/directlinktocsv"
$output = "$PSScriptRoot\ThreatsDataReport.csv"
$emailSMTPServer = "smtp.mydomain.com"
$emailFrom = "myemail@mydomain.com"
$emailRecipients = @("Foo <foo@mydomain.com>", "Bar <bar@mydomain.com>")
$emailSubject = "Cylance Detected Threats"
$emailBody = "This is where I want my output to go"

(New-Object System.Net.WebClient).DownloadFile($url, $output)

$csv = Import-Csv $output 

    $csv."File Name" | ForEach-Object {
    $_


    }

    $csv.DeviceName | ForEach-Object {

    $_

    }


Send-MailMessage -From $emailFrom -To $emailRecipients -SmtpServer $emailSMTPServer -subject $emailSubject -Body $emailBody

Answer 1

Thanks for posting this. I used it as a template and expanded on for some things I wanted. My csv data is from PRTG.

I was able to get around downloading the csv using Invoke-WebRequest. Inserting -UseBasicParsing allows the script to be scheduled with task scheduler.

$WebResponse = Invoke-WebRequest -UseBasicParsing "URL to CSV DATA"
$output = $WebResponse.Content

I also added some filtering and sorting logic to the contents

#Uses data from WebResponse, filters, sorts, and selects columns
#Multiple sections of the resultant html output allows for formatting
#Evaulates results set and sets alternate header when null
#Sets results and converts to html while inserting HTML head
$prep1 = ConvertFrom-Csv $output | ? Status -like Down | sort -Property 'Device', 'Sensor'| Select Probe,Device,Sensor
if (!$prep1) { $out1 = '<H3>No Sensors in <span style="color: #ff0000;">Down</span> State</H3>' }
else {$out1 = $prep1 | ConvertTo-Html -head $head -Body '<H3>Sensors in <span style="color: #ff0000;">Down</span> State</H3>'}

As I wanted different sections of data with different headers, I repeated the above and then just finished it by appending everything together.

#Create HTML Body
$emailBody = "$out1 $out2"

Here is the finished script.

#Define variables
$WebResponse = Invoke-WebRequest -UseBasicParsing "URL with output=csvtable"
$output = $WebResponse.Content
$emailSMTPServer = "smtp.mydomain.com"
$emailFrom = "reportingemail@domain.com"
$emailRecipients = @("Foo <foo@domain.com>"), "Bar <bar@domain.com>")
$emailSubject = "PRTG Status Report"

#Sets HTML Head Information when result set is not Null
$head = "<style>"
$head = $head + "BODY{}"
$head = $head + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$head = $head + "TH{border-width: 1px;padding: 1px;border-style: solid;border-color: black;background-color:gainsboro}"
$head = $head + "TD{border-width: 1px;padding: 1px;border-style: solid;border-color: black;background-color:palegoldenrod}"
$head = $head + "</style>"

#Uses data from WebResponse, filters, sorts, and selects columns
#Multiple sections of the resultant html output allows for formatting
#Evaulates results set and sets alternate header when null
#Sets results and converts to html while inserting HTML head

$prep1 = ConvertFrom-Csv $output | ? Status -like Down | sort -Property 'Device', 'Sensor'| Select Probe,Device,Sensor
if (!$prep1) { $out1 = '<H3>No Sensors in <span style="color: #ff0000;">Down</span> State</H3>' }
else {$out1 = $prep1 | ConvertTo-Html -head $head -Body '<H3>Sensors in <span style="color: #ff0000;">Down</span> State</H3>'}

$prep2 = ConvertFrom-Csv $output | ? Status -like Warning | ? Sensor -Notmatch 'Windows Updates Status' |sort -Property 'Device', 'Sensor'| Select Probe,Device,Sensor
if (!$prep2) { $out2 = '<H3>No Sensors in <span style="color: #ffcc00;">Warning</span> State</H3>' }
else {$out2 = $prep2 | ConvertTo-Html -head $head -Body '<H3>Sensors in <span style="color: #ffcc00;">Warning</span> State</H3>'}

#Create HTML Body
$emailBody = "$out1 $out2"

#Send email 
Send-MailMessage -From $emailFrom -To $emailRecipients -SmtpServer $emailSMTPServer -subject $emailSubject -Body $emailBody -BodyAsHtml

Answer 2

Managed to come up with an answer to this!

First things first I threw out the Send-HTMLEmail in the link, as it was neither necessary nor particularly helpful.

My fixed, working code is

#Defines variables
$url = "mysite.com/ThreatsDataReport.csv"
$output = "$PWD\ThreatsDataReport.csv"
$emailSMTPServer = "smtp.mydomain.com"
$emailFrom = "reportingemail@domain.com"
$emailRecipients = @("Foo <foo@domain.com>"), "Bar <bar@domain.com>")
$emailSubject = "Cylance Reporting"

(New-Object System.Net.WebClient).DownloadFile($url, $output)
#Downloads the CSV from $url and saves it to $output

#Sets HTML Head Information
$a = "<style>"
$a = $a + "BODY{background-color:peachpuff;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod}"
$a = $a + "</style>"




$csv = Import-Csv $output | Select "File Name",DeviceName,"File Path" | ConvertTo-Html -head $a -Body "<H2>Cylance Weekly Report</H2>"
#Imports the CSV saved to $output.
#Selects File Name, Device Name, and File Path columns, then converts to HTML using the Head +Body Information
$emailBody = "$csv"

#defines the body text of the email. Note that this has to be a string, $emailBody = $csv will not work on its own. 

Send-MailMessage -From $emailFrom -To $emailRecipients -SmtpServer $emailSMTPServer -subject $emailSubject -Body $emailBody -BodyAsHtml
#Sends the actual email 

Answer 3

If you only want the first row from the CSV file with those column names, you could do something like this:

$csv = Import-Csv $output | Select-Object -First 1
$emailBody = "File Name: {0}`r`nDevice Name: {1}" -f
  $csv."File Name", $csv.DeviceName
$params = @{
  "From"       = $emailFrom
  "To"         = $emailRecipients
  "SmtpServer" = $emailSMTPServer
  "Subject"    = $emailSubject
  "Body"       = $emailBody
}
Send-MailMessage @params

To grab multiple rows of output, you could do something like:

$emailBody = Import-Csv $output |
  Select-Object "File Name",DeviceName |
  Format-Table -AutoSize | Out-String
$params = @{
  "From"       = $emailFrom
  "To"         = $emailRecipients
  "SmtpServer" = $emailSMTPServer
  "Subject"    = $emailSubject
  "Body"       = $emailBody
}
Send-MailMessage @params