Author Posts

February 25, 2017 at 7:16 pm

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

February 26, 2017 at 8:57 am

Can anybody help?

February 27, 2017 at 4:17 am

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?

February 27, 2017 at 5:39 pm

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

February 28, 2017 at 1:17 am

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

February 28, 2017 at 7:34 pm

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