Script advice for grouping values in tables and email formatting

Hi, I’m working on a script to check the patch level of all the servers in our environment and notify the correct server application team via email, if they have a server that hasn’t been updated in the last 90 days (We have multiple app teams that are set as server owners).

It’s still a little rough but it seems to work. Right now I’m just exporting the results to a CSV and catching the errors in a hash table.

What I want to do is actually avoid exporting the results to any file and store them in a way I can format them in a table and send it. Unfortunately I’m a little bit stuck with what would be the best way to do this and looking for some advice from the forum.

In summary I want to achieve the following.

  1. Store the results of $obj $team $team_email $lastdate in a way that can be formatted nicely in a table of the body of an email sent with Send-MailMessage.
  2. Be able to sort this table by the email value, group and send the list of servers with the same email owner's individually formatted tables with only their servers in the body of the email.
What would be the best way to do this?

 

[pre]

$token = xxxxxx
$headers = xxxxxx

$servers = Get-ADComputer -Filter {(Name -like “srv”) -and (Enabled -eq $true) -and (Operatingsystem -like “server”)}

$RPCerror = [ordered]@{}

Foreach ($server in $servers)
{

Get info from the IPAM portal

$obj = ($server.Name).ToLower()
$res = (Invoke-RestMethod -Uri “https://IPAMPORTAL.local” -Headers $headers).results
$team = $res.custom_fields.responsible_team
$team_email = $res.custom_fields.team_dist_list
$datesub90 = (Get-date).adddays(-90)

try{
$lastdate = ((Get-HotFix -ComputerName $obj | Sort-Object -Property InstalledOn)[-1] | Where-Object InstalledOn -LT $datesub90 | Select-Object -First 1).InstalledOn

if ($lastdate -notlike $null)
{
$response = $obj + ", " + $team + ", " + $team_email + “,” + $lastdate
Out-File -FilePath “.\result.csv” -InputObject $response -Append
Write-Output $response
}

}
catch {
$RPCerror.Add($obj,($Error[0].Exception.Message))
}

}

[/pre]

 

This is exactly what new-object is made for.

New-Object -TypeName psobject -Property @{
Server = $obj
Team = $team
TeamEmail = $teamemail
Lastdate = $lastdate
}

If you need the object to retain the order of properties, create an ordered hashtable for the properties first.

$properties = [ordered]@{
Server = $obj
Team = $team
TeamEmail = $teamemail
Lastdate = $lastdate
}

New-Object -TypeName psobject -Property $properties

You can collect all the output to a variable outside the loop like this

$results = Foreach($server in $servers){

...

}

Or you could use an arraylist

$results = New-Object System.Collections.Arraylist

foreach($server in $servers){

...

[void]$results.add($(New-Object -TypeName PSObject -Property $properties))

}

Once you have all of your results, group them and create your emails. Reference each property where you need it with dot notation.

$results | Group-Object -Property 'email' | ForEach-Object {$(
$mailparams = @{
SMTPServer = "Your Exchange Server"
To = "{0} <{1}>" -f $_.group.team[0],$_.group.teamemail[0]
From = "From Email Name <from@domain.com>"
Subject = "Last update over 90 days"
Body = $(foreach($item in ($_.group)){

"Server: {0}      Last update: {1}" -f $item.obj,$item.lastdate

})
Priority = 'High'
}
Send-MailMessage @mailparams
)}

This example assumes each team email has only one team associated with it. If there are multiple teams for one email, I would change the grouping to the team name and then adjust the loops accordingly. This is the same example with @mailparams ran through write-output instead of send-mailmessage.

[img]https://pasteboard.co/J381xZJ.jpg

I hope this helps out.

Thanks for that. I’ve found a good solution now using a datatable