Help needed in powershell formatting

PowerShell Help
1

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

2

Can anybody help?

3

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?

4

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 ??

5

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
6

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