sometimes cell in html email is blank

Welcome Forums General PowerShell Q&A sometimes cell in html email is blank

Viewing 1 reply thread
  • Author
    Posts
    • #250838
      Participant
      Topics: 1
      Replies: 0
      Points: 11
      Rank: Member

      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 “[email protected]” -To “[email protected]” -Subject “Prod-new login created.Audit_Rows query (task:NewDB or new login(daily)-ks)” `
      -BodyAsHtml -Body $body -SmtpServer “mySMTPserverName”
      }

      [/Pre]

    • #250889
      Participant
      Topics: 15
      Replies: 1765
      Points: 3,178
      Helping Hand
      Rank: Community Hero

      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:

Viewing 1 reply thread
  • You must be logged in to reply to this topic.