Post reply

Powershell formatting help - convert-html commandlet

  • February 25, 2017 at 12:08 pm

    #335672

    Hi Experts,

    I need some formatting help in powershell using convert-html commandlet.

    Basically, here is my requirement. I want to develop a powershell script which reads the SQL server errorlog and fetches Errors occurred in last 24 hours. That data has to be nicely formmated and sent as an email to the dba team.

    Here is the script works fine but the formatting I am not getting properly. I have spent a lot of time but felt like if someone can help me on this. I am attaching 2 outputs. Unformatted sql server output 1.png and powershell html formatted output 2.png.

    Note: Output should contain 3 columns 1. Logdate, 2.ProcessInfo and 3.Text. Can anybody please help In the formatting piece or suggestions would be appreciated.

    param(
           [string]$inst=$null,  # provide the instance name
           #[datetime]$startdt='1900-01-01',
        #[datetime]$startdt=(get-date).AddDays(-1),
        [datetime]$startdt=(Get-Date).AddHours(-24),
           [string]$srch=$null
           )

    # Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
    $v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
    if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
      }

    # Handle any errors that occur
    Trap {
      # Handle the error
      $err = $_.Exception
      write-output $err.Message
      while( $err.InnerException ) {
           $err = $err.InnerException
           write-output $err.Message
           };
      # End the script.
      break
      }
      clear-host
    # Connect to the specified SQL Server instance
    $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst

    # Get the current error log
    $err = $s.ReadErrorLog()
    #$todaysdt = Get-Date
    #Write-Host $todaysdt
    #Write-Host "**************"
    #Write-Host $startdt
    #Write-Host "**************"

    # Initialize a new collection, then concatenate the errorlog properties together
    $errlog = @()
    $err | where {$_.LogDate -ge $startdt} | foreach {
           $errlog += [string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text
           }

    # Search the errorlog and return any error and the subsequent detailed message
    $results =@()

    if ($srch -eq 'DBCC') {
           $results  = $results + $errlog | select-string -pattern 'DBCC' -context 0,0
           }
    else {
    $results  = $results + $errlog | select-string -pattern 'Error:' -context 0,1
           }
    Clear-Host
    Write-Host $results

    $results | Out-File C:\Scripts\Test.htm
    Invoke-Expression C:\Scripts\Test.htm

    # Template for HTML
    $html = @"
    <html>
        <head>
            <style>
                BODY{
                   background-color:white;
                }
                TABLE{
                    font-family: Arial;
                    font-size: 12px;
                    width:100%;
                    height:75%;
                    border-width: 1px;
                    border-style: solid;
                    border-color: black;
                    border-collapse: collapse;
                }
                TH{
                    border-width: 1px;
                    padding: 0px;
                    border-style: solid;
                    border-color: black;
                    color:white;
                    background-color: green;
                }
                TD{
                    border-width: 1px;
                    padding: 0px;
                    border-style: solid;
                    border-color: black;
                    background-color:white
                }
            </style>
        </head>
        <body>
           
    "@

    $FilePath = "c:\scripts"  # this is the place where output html files gets created
    $OutFile = Join-Path -path $FilePath -childPath ("SQLErrors_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".html")

    $results |  ConvertTo-HTML -head $html -body "<H3> SQL Server ERRORS from ERRORLOG : </H3><br>"| Out-file $OutFile
    Invoke-Expression  $OutFile

    << 1.png>>

    <<2.png>>


    Basically, I want the 2nd output to formatted nicely and sent as an email to the DBATeam.

    Thanks,

    Sam

  • February 26, 2017 at 1:55 am

    #1930370

    Any help?

  • February 26, 2017 at 4:49 pm

    #1930399

    Can you be more specific about what is working and what isn't. Exactly.

    Oh and please remember that anyone who may consider responding does so with little, if any, benefit to themselves. Thank you.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • February 27, 2017 at 10:42 am

    #1930514

    I am sorry for the confusion. Basically the output of sql server is stored in the $results variable.
    This is the one I want to render in nice html format. So, in the output of html should contain 3 columns (1.Logdate, 2.ProcessInfo and 3.Text).

  • February 28, 2017 at 1:41 am

    #1930623

    Is the third image what you are currently getting?

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • February 28, 2017 at 12:29 pm

    #1930789

    Yes.

  • March 1, 2017 at 4:04 am

    #1930914

    What is the output of $results to the console?
    What is it's type? (I am assuming an array.)

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply