parse html email for values in table

Welcome Forums General PowerShell Q&A parse html email for values in table

Viewing 1 reply thread
  • Author
    Posts
    • #212745
      Participant
      Topics: 50
      Replies: 206
      Points: 839
      Helping Hand
      Rank: Major Contributor

      I have an html email that contains a table in it’s body. The tables contains several rows, I want to parse the html and extract the data in that table.

       

      MachineName TimeCreated LevelDisplayName LogName EventID ProviderName Message
      D01.si.corp 12/13/2017 6:00:06 AM Error System 7023 Service Control Manager The Interactive Services Detection service terminated with the following error: Incorrect function.
      D01.si.corp 12/13/2017 6:00:06 AM Error System 10016 Microsoft-Windows-DistributedCOM The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID {06FA962D-B817-4349-8219-93F97AB359D0} and APPID {5E038245-CF81-44BE-8018-9A2981B9DC9B} to the user NT AUTHORITY\SYSTEM SID (S-1-5-18) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

       

      So far, I’ve got a script that accesses the relevant emails from my pst file, and presents the contents.

      But I’m not able to parse the table in the emails and extract the contents of that.

      The command I’m using is

      $Outlook = New-Object -ComObject 'Outlook.Application' -ErrorAction 'Stop'
      
      $pstRootFolder = $outlook.GetNameSpace('MAPI').Stores|?{("C:\Iain\Empyrean.pst" -eq [string]$_.FilePath)}|%{$_.GetRootFolder()}
      $pstRootFolder.Folders | ?{$_.FolderPath -match '_Logs'} | %{
          $_.items | ?{$_.SenderName  -match 'dl-dba' -And $_.Subject -match 'SSIS Errors Report'} | sort receivedtime -desc | Select -First 1 | %{
              $_.HTMLBody
          }
      }
      
      I then look at the contents of $AllEmail.HTMLBody , example below, and I want to extract the column data from the html table. How can I do that?
      
      

       

      <html> <head> </head> <p>Hello Team,</p> <p></p> <p>RE: SSIS Errors</p> <p><u>Please check the listed errors</u></p> <p> </p> <body><!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”&gt; <html xmlns=”http://www.w3.org/1999/xhtml”&gt; <head> <style>BODY{font-family: Arial; font-size: 10pt;}TABLE{border: 1px solid black; border-collapse: collapse;}TH{border: 1px solid black; background: #dddddd; padding: 5px;}TD{border: 1px solid black; padding: 5px;}</style> </head><body>

      <colgroup><col><col><col><col><col><col></colgroup>

      SqlInstance MessageTime ExecutionID Message ProjectName PackageName
      P01\PROD 3/25/2020 11:02:16 PM +00:00 124592 Lead_Members:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: &quot;Microsoft SQL Server Native Client 11.0&quot; Hresult: 0x80004005 Description: &quot;Unable to complete login process due to delay in opening server connection&quot;. RedBean.Reporting Lead_Members.dtsx
      P01\PROD 3/26/2020 12:31:36 AM +00:00 124734 MasterPayrollETL:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: &quot;Microsoft SQL Server Native Client 11.0&quot; Hresult: 0x80040E4D Description: &quot;Login failed for user ‘SI\prod’.&quot;. ETL.Exports MasterPayrollETL.dtsx
      T01\TEST 3/25/2020 9:21:47 PM +00:00 121629 Truncate contacts_cstm_staging:Error: Executing the query &quot;TRUNCATE TABLE contacts_cstm_staging;&quot; failed with the following error: &quot;The connection has been disabled.&quot;. Possible failure reasons: Problems with the query, &quot;ResultSet&quot; property not set correctly, parameters not set correctly, or connection not established correctly. CRM.Sync MasterCrmSync.dtsx

      </body></html> </body> <p></p> <p></p> <p>Thank You,</p> <p>Enterprise Technical Services Team</p> </html>

       

      • This topic was modified 4 months, 2 weeks ago by Iain.
      • This topic was modified 4 months, 2 weeks ago by Iain.
      • This topic was modified 4 months, 2 weeks ago by Iain.
      • This topic was modified 4 months, 2 weeks ago by kvprasoon. Reason: code formatting
    • #212754
      Participant
      Topics: 50
      Replies: 206
      Points: 839
      Helping Hand
      Rank: Major Contributor

      OK, that post never turned out formatted like I wanted…

Viewing 1 reply thread
  • The topic ‘parse html email for values in table’ is closed to new replies.