Script advice for grouping values in tables and email formatting

Welcome Forums General PowerShell Q&A Script advice for grouping values in tables and email formatting

Viewing 2 reply threads
  • Author
    Posts
    • #217239
      Participant
      Topics: 3
      Replies: 3
      Points: 31
      Rank: Member

      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?

       

      
      $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))
      }
      
      }
      
      

       

       

    • #217353
      Participant
      Topics: 3
      Replies: 342
      Points: 1,130
      Helping Hand
      Rank: Community Hero

      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.

      https://pasteboard.co/J381xZJ.jpg

      I hope this helps out.

    • #218613
      Participant
      Topics: 3
      Replies: 3
      Points: 31
      Rank: Member

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

Viewing 2 reply threads
  • You must be logged in to reply to this topic.