sometimes cell in html email is blank

PowerShell Help
1

My script queries a table on different SQL Server databases and then emails me if a new database was created yesterday. SOMETIMES the database name is blank. I’m not sure if it is caused by a spacial character or real long name. I “think” it might be fixed by type-casting my output parameters. Example:

Server Login Command Name
serverABC PersonWhoCreatedIt create database (this is blank)
Here is my code: [Pre]

find person who created the database

$query = " select distinct server_instance_name as Server,server_principal_name as Login,‘create login’ as Command,
REPLACE(LEFT([statement],CHARINDEX(‘]’,[statement])),‘CREATE LOGIN’,‘’) as Name
FROM [Utility].[dbo].[Audit_rows]
where event_time > getdate()-1 and [statement] like ‘create login%’ "

$ServerList = Get-DbaCmsRegServer -SqlInstance localhost -Group MyCMSGroupOfServers | Select-Object -ExpandProperty ServerName

#the purpose of the next 2 steps is in case you need to remove an array item.
$serverOrig = $ServerList
[System.Collections.ArrayList]$server = $serverOrig

Import-Module SQLPS

$results = @()

foreach($s in $server)
{
write-host "Executing query against server: " $s
$results += Invoke-Sqlcmd -query $query -ServerInstance $s
}

if($results.count -gt 0) {
$style = “<style>BODY{font-family: Arial; font-size: 10pt;}”
$style = $style + “TABLE{border: 1px solid black; border-collapse: collapse;}”
$style = $style + “TH{border: 1px solid black; background: #dddddd; padding: 5px; }”
$style = $style + “TD{border: 1px solid black; padding: 5px; }”
$style = $style + “</style>”

$body = $results | select-object Server,Login,Command,Name | convertto-html -Head $style | out-string

Send-MailMessage -From “kevin@gmail.com” -To “kevin@gmail.com” -Subject “Prod-new login created.Audit_Rows query (task:NewDB or new login(daily)-ks)” `
-BodyAsHtml -Body $body -SmtpServer “mySMTPserverName”
}

[/Pre]

2

There is nothing that would truncate or not populate data as far as Powershell is concerned. It’s performing a query, so if you have the server you should be able to connect and run the query against what is not blank (e.g. login) to see if the SQL parse is working properly. Here are some code suggestions such as here strings and splats:

Import-Module SQLPS

#Here String
$query = @” 
SELECT DISTINCT server_instance_name AS Server,
                server_principal_name AS Login,
                ’create login’ AS Command,
                REPLACE(LEFT([statement],CHARINDEX(‘]’,[statement])),’CREATE LOGIN’,”) AS Name
FROM [Utility].[dbo].[Audit_rows]
WHERE event_time > getdate()-1 
AND [statement] like ‘create login%’ 
”@

$ServerList = Get-DbaCmsRegServer -SqlInstance localhost -Group MyCMSGroupOfServers | 
              Select-Object -ExpandProperty ServerName

#the purpose of the next 2 steps is in case you need to remove an array item.
$serverOrig = $ServerList
[System.Collections.ArrayList]$server = $serverOrig

$results = foreach( $s in $server ) {
    write-host “Executing query against server: ” $s
    Invoke-Sqlcmd -query $query -ServerInstance $s
}

if ( $results ) {
$style = @“
<style>
    BODY{font-family: Arial; font-size: 10pt;}
    TABLE{border: 1px solid black; border-collapse: collapse;}
    TH{border: 1px solid black; background: #dddddd; padding: 5px;}
    TD{border: 1px solid black; padding: 5px; }
</style>
"@

    $body = $results | 
            convertto-html -Head $style -Property Server,Login,Command,Name | 
            out-string
    #splat
    $params = @{
        From       = “kevin@gmail.com” 
        To         = “kevin@gmail.com” 
        Subject    = “Prod-new login created.Audit_Rows query (task:NewDB or new login(daily)-ks)”
        BodyAsHtml = $true
        Body       = $body 
        SmtpServer = “mySMTPserverName”
    }

    Send-MailMessage @params
}