Help needed in powershell formatting

This topic contains 5 replies, has 3 voices, and was last updated by  M1981 7 months, 2 weeks ago.

  • Author
    Posts
  • #65260

    M1981
    Participant

    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.
    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 = @"

    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
    }

    "@

    $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 " SQL Server ERRORS from ERRORLOG : "| Out-file $OutFile
    Invoke-Expression $OutFile

    Thanks,
    -M

  • #65266

    M1981
    Participant

    Can anybody help?

  • #65283

    Curtis Smith
    Participant

    Here are a lot of unknowns here. What the data set looks like, what the output looks like, what the expected output is. What exactly is not working.

    You say Output should contain 3 columns 1. Logdate, 2.ProcessInfo and 3.Text.
    What are you getting instead?
    Is the formatting problem that you are getting too many columns, not enough columns, color's wrong, etc?

    • #65329

      M1981
      Participant

      Hi Smith,

      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. The output will contain 3 columns. So, in the output of html should contain 3 columns (1.Logdate, 2.ProcessInfo and 3.Text).

      Am I clear ??

  • #65338

    Rob Simmers
    Participant

    A couple of small things. First, the CSS needs to be wrapped in STYLE tags prior to being placed in the HEAD. Second, the -Body is being filled with your results, so if you want a header use -PreContent and add a header tag like H2:

    Note: Forum eats HTML, so the style and h2 are standard HTML tags, not curly braces

    $html = @"
    {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}
    "@
    
    $results = @()
    $results += [pscustomobject]@{
        Logdate = Get-Date
        ProcessInfo = "Red"
        Text = "Some text"
    }
    $results += [pscustomobject]@{
        Logdate = Get-Date
        ProcessInfo = "Blue"
        Text = "Some more text"
    }
    $results += [pscustomobject]@{
        Logdate = Get-Date
        ProcessInfo = "Yellow"
        Text = "Some other text"
    }
    
    $OutFile = "{0}\test.html" -f [environment]::GetFolderPath("Desktop")
    $results | ConvertTo-HTML -head $html -PreContent "{h2}SQL Server ERRORS from ERRORLOG:{/h2}"| Out-file $OutFile
    Invoke-Expression $OutFile
    
  • #65400

    M1981
    Participant

    Thanks Rob. Made some changes to the script and it worked.

You must be logged in to reply to this topic.