Gettint Email as HTML from powershell

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 10 months ago.

  • Author
    Posts
  • #6368

    by jagan at 2012-11-16 20:26:20

    Gurus, I am trying to write a code to send out list of failed jobs in the last 24 hours. I am using out-string to convert my table to string. I am able to get email with list of jobs. But, i would like to see the e-mail in a table format rather than a string format. Here is code. Please help me out for converting my code to send email in HTML format. Thank you in advance.
    #Set-ExecutionPolicy RemoteSigned

    ###########################################################################
    # Declare ServerName, DatabaseName and TableName to Get Server List
    ###########################################################################
    $SourceServerName = 'local'
    $SourceDatabaseName = 'dbadb'
    #$SourceTablename = 'LookUp_ServerList_NonProd'

    ###########################################################################
    # Declare Variables for Sending E-mails
    ###########################################################################

    $ToRecipient = "********@email.com"
    $From = "*********@email.com"
    $SMTPServer = "smtp.email.com"
    $GetDate = get-date -format g

    ###########################################################################
    # Create SqlConnection object and define connection string
    ###########################################################################

    $SQLCon = New-Object System.Data.SqlClient.SqlConnection
    $SQLCon.ConnectionString = "Server=$SourceServerName; Database=$SourceDatabaseName; Integrated Security=true"

    ###########################################################################
    # Create SqlCommand object, define command text, and set the connection
    ###########################################################################

    $SQLCmd = New-Object System.Data.SqlClient.SqlCommand
    $SQLCmd.CommandText = "SELECT SQLServerInstanceName
    FROM [dbo].[LookUp_SQLServerInstanceList_NonProd] AS LUSSILNP
    INNER JOIN [dbo].[LookUp_ServerList_NonProd] AS LUSLNP
    ON [LUSSILNP].[ServerID] = [LUSLNP].[ServerID]
    WHERE [SqlPingFlag] = 1"
    $SQLCmd.Connection = $SQLCon

    ###########################################################################
    # Create SqlDataAdapter object and set the command
    ###########################################################################

    $SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlDataAdapter.SelectCommand = $SQLCmd

    ###########################################################################
    # Create and fill the DataSet object
    ###########################################################################
    $DataSet = New-Object System.Data.DataSet
    $SqlDataAdapter.Fill($DataSet, "SQLServerInstanceName") | Out-Null

    ###########################################################################
    # Close the connection
    ###########################################################################
    $SQLCon.close()

    ###########################################################################
    # Function for sending E-mails to ********@email.com
    ###########################################################################

    Function SendEmail
    {
    #param($strTo, $strFrom, $strSubject, $strBody, $smtpServer)
    param($To, $From, $Subject, $Body, $smtpServer)
    $msg = new-object Net.Mail.MailMessage
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)
    $msg.From = $From
    $msg.To.Add($To)
    $msg.Subject = $Subject
    $msg.IsBodyHtml = 1
    $msg.Body = $Body
    $smtp.Send($msg)
    }

    ###########################################################################
    # Declare Database and query
    ###########################################################################

    $SQLDatabaseName = "msdb"
    $SQLQueryText = "SELECT [S2].[name], [dbo].[agent_datetime]([run_date] , [run_time]) AS RunDate
    FROM dbo.[sysjobhistory] AS S
    INNER JOIN dbo.[sysjobs] AS S2
    ON [S].[job_id] = [S2].[job_id]
    WHERE [dbo].[agent_datetime]([run_date] , [run_time]) >= GETDATE() - 1
    AND ([run_status] IN ( 0 , 3 )
    AND [S].[step_id] = 0)"

    ##################################################################################################
    # Iterate through the dataset to Run the query on Remote Server and send Results as E-Mails
    ##################################################################################################

    foreach ($row in $DataSet.tables["SQLServerInstanceName"].rows)
    {
    $DestinationServerName = $row.SQLServerInstanceName

    #RunSQLQuery -SQLServerName $DestinationserverName -SQLDatabaseName $SQLDBName -SQLQuery $SQLQueryText
    $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $DestinationServerName; Database = $SQLDatabaseName; Integrated Security=true"
    $SqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $SqlCommand.CommandText = $SQLQueryText
    $SqlCommand.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCommand
    $SQLDataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($SQLDataSet) | Out-Null
    $SqlConnection.Close()
    $Results = $SQLDataSet.Tables | Format-Table -Auto Name, RunDate | out-string
    $CountRows = $SQLDataSet.Tables[0].Rows.Count
    #$Text = @"Following Jobs have Failed:"@
    $BodyText = ("$Results")

    IF($CountRows -ne 0 )
    {
    $DestinationServerName
    $Subject = "$DestinationServerName - SQL Job Failed - $GetDate"
    #$Body = $TableResults

    SendEmail -To $ToRecipient -From $From -Subject $Subject -Body $BodyText -BodyASHTML -Auto Name -smtpServer $SMTPServer
    }
    }

    by DonJ at 2012-11-17 06:52:09

    Have you looked at ConvertTo-HTML instead of using Format-Table?

    See also "Creating HTML Reports in PowerShell" at http://powershellbooks.com if you need more functionality than ConvertTo-HTML provides.

    by jagan at 2012-11-17 19:09:01

    I tried using convert to HTML... I wasn't successful with that. Here is how I did it. Please suggest if there is any other efficient way!

    #Set-ExecutionPolicy RemoteSigned

    ###########################################################################
    # Declare ServerName, DatabaseName and TableName to Get Server List
    ###########################################################################
    $SourceServerName = '*******'
    $SourceDatabaseName = 'dbadb'
    #$SourceTablename = 'LookUp_ServerList_NonProd'

    ###########################################################################
    # Declare Variables for Sending E-mails
    ###########################################################################

    $ToRecipient = "******@email.com"
    $From = "******@email.com"
    $SMTPServer = "smtp.email.com"
    $GetDate = get-date -format g

    ###########################################################################
    # Create SqlConnection object and define connection string
    ###########################################################################

    $SQLCon = New-Object System.Data.SqlClient.SqlConnection
    $SQLCon.ConnectionString = "Server=$SourceServerName; Database=$SourceDatabaseName; Integrated Security=true"

    ###########################################################################
    # Create SqlCommand object, define command text, and set the connection
    ###########################################################################

    $SQLCmd = New-Object System.Data.SqlClient.SqlCommand
    $SQLCmd.CommandText = "SELECT SQLServerInstanceName
    FROM [dbo].[LookUp_SQLServerInstanceList_NonProd] AS LUSSILNP
    INNER JOIN [dbo].[LookUp_ServerList_NonProd] AS LUSLNP
    ON [LUSSILNP].[ServerID] = [LUSLNP].[ServerID]
    WHERE [SqlPingFlag] = 1"
    $SQLCmd.Connection = $SQLCon

    ###########################################################################
    # Create SqlDataAdapter object and set the command
    ###########################################################################

    $SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlDataAdapter.SelectCommand = $SQLCmd

    ###########################################################################
    # Create and fill the DataSet object
    ###########################################################################
    $DataSet = New-Object System.Data.DataSet
    $SqlDataAdapter.Fill($DataSet, "SQLServerInstanceName") | Out-Null

    ###########################################################################
    # Close the connection
    ###########################################################################
    $SQLCon.close()

    ###########################################################################
    # Function for sending E-mails to ******@email.com
    ###########################################################################

    Function SendEmail
    {
    #param($strTo, $strFrom, $strSubject, $strBody, $smtpServer)
    param($To, $From, $Subject, $Body, $smtpServer)
    $msg = new-object Net.Mail.MailMessage
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)
    $msg.From = $From
    $msg.To.Add($To)
    $msg.Subject = $Subject
    $msg.IsBodyHtml = 1
    $msg.Body = $Body
    $smtp.Send($msg)
    }

    ###########################################################################
    # Declare Database and query
    ###########################################################################

    $SQLDatabaseName = "msdb"
    $SQLQueryText = "SELECT [S2].[name], [dbo].[agent_datetime]([run_date] , [run_time]) AS RunDate
    FROM dbo.[sysjobhistory] AS S
    INNER JOIN dbo.[sysjobs] AS S2
    ON [S].[job_id] = [S2].[job_id]
    WHERE [dbo].[agent_datetime]([run_date] , [run_time]) >= GETDATE() - 1
    AND ([run_status] IN ( 0 , 3 )
    AND [S].[step_id] = 0)"

    ##################################################################################################
    # Iterate through the dataset to Run the query on Remote Server and send Results as E-Mails
    ##################################################################################################

    foreach ($row in $DataSet.tables["SQLServerInstanceName"].rows)
    {
    $DestinationServerName = $row.SQLServerInstanceName

    #RunSQLQuery -SQLServerName $DestinationserverName -SQLDatabaseName $SQLDBName -SQLQuery $SQLQueryText
    $SQLConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $DestinationServerName; Database = $SQLDatabaseName; Integrated Security=true"
    $SqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $SqlCommand.CommandText = $SQLQueryText
    $SqlCommand.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCommand
    $SQLDataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($SQLDataSet) | Out-Null
    $SqlConnection.Close()
    $CountRows = $SQLDataSet.Tables[0].Rows.Count
    ###############################################################################################
    ## Following code is for prepping HTML E-Mail Message
    ###############################################################################################

    $table = $SQLDataSet.Tables[0]
    $col1 = New-Object system.Data.DataColumn SQlJobName, ([string])
    $col2 = New-Object system.Data.DataColumn SQLRunDate, ([string])
    $table.columns.add($col1)
    $table.columns.add($col2)
    foreach($row2 in $SQLDataset.tables["Name"].rows)
    {
    $row3 = $table.NewRow()
    $table.Rows.Add($row3)
    }
    # Create an HTML version of the DataTable
    $html = "

    "
    foreach ($row4 in $table.Rows)
    {
    $html += "

    "
    }
    $html += "

    SQLJobName JobRunDateTime
    " + $row4[0] + " " + $row4[1] + "

    "
    $HTMLmessage = @"

    Following are the Failed SQL Jobs


    $html

    "@

    ###############################################################################################
    ## If Any Failed Jobs Exists - Then Send and E-mail
    ###############################################################################################

    IF($CountRows -ne 0 )
    {
    $DestinationServerName
    $Subject = "$DestinationServerName - Failed SQL Jobs - $GetDate"
    #$Body = $TableResults
    #$body = $HTMLmessage
    SendEmail -To $ToRecipient -From $From -Subject $Subject -Body $HTMLmessage -BodyASHTML -Auto Name -smtpServer $SMTPServer
    }
    }

    by DonJ at 2012-11-17 20:13:25

    ConvertTo-HTML is an actual command. Did you try working with that?

    by jagan at 2012-11-18 09:37:54

    Yes I tried using ConvertTo-HTML in my script. I was not successful.

    I am not a pro in powershell. If there is an efficient way of dong this with ConvertTo-HTML then please do suggest. Or guide me with example code.

    Thanks
    Jagan K

    by DonJ at 2012-11-18 09:50:08

    I'm not sure about coming from a data table. If piping it directly to ConvertTo-HTML didn't work, then I would probably enumerate the rows and construct custom objects to pipe to ConvertTo-HTML.

    But encore we go into that, what's the actual problem? Your last example manually constructed HTML. Since you went through that trouble, did you not like the result? Did it not display well?

    I'd normally do something like this – this is pseudo-code, mind you, but if you assume $table is a table, making $row an individual data row...


    $objects = @()
    foreach ($row in $table) {
    $prop = @{Col1=$row.Property1 ; Col2=$row.Property2 ; Col3=$row.Property3}
    $obj = New-Object PSObject -Prop $prop
    $objects += $obj
    }
    $objects | ConvertTo-HTML

    Should generate pretty clean HTML.

    But it looks like you already put so much work into making the HTML, I can't think that's where you are stuck. Did your HTML just not look right? Or did it not display well in the e-mail client?

    by DonJ at 2012-11-18 10:35:25

    Something that I just thought of, too – is there a strong reason you need to do this *in PowerShell*? I mean, if the data is already in SQL Server, then SQL Server Reporting Services would be a very robust way to have these reports get e-mailed out, automatically on a schedule, as HTML or PDF or whatever. Seems like you're doing a lot of re-creating the wheel inside PowerShell, I'd hate to see you doing all this work for nothing .

    Just a thought!

    by jagan at 2012-11-18 17:52:12

    These are actually dev machines on which we have few maintenance jobs. We don't want to configure databasemail on all the dev servers. So, thats the reason we were looking for other alternatives like powershell or SSIS. I accept that we can do this more efficiently with SSIS and SSRS. However, we have chosen this route. Here is an example E-mail message we are getting now. We are happy with the e-mail message. I posted my code to this thread to hear comments from experts like you guys. I am very new to powershell. I want to learn. I want to do it in efficient way!!

    a user uploaded image

    Image is not showing up!! Above is a copied version of my e-mail

You must be logged in to reply to this topic.