I have a handful of SQL Agent jobs that run on our Central Management Server Instance. These jobs check certain settings across our environment and email me a report if any servers are out of compliance. I realize I could use Policy Based Management, but I've found this system works well, except for one thing...
The scripts populate an array in Powershell with the offending records. I then check the size of the array and if it has > 0 members, I email the results to an operator on the CMS instance. I realized recently that if the array has 1 member, I would not be notified. Only if there were two or more items in the array would I get an email. I had tested my script locally and it worked fine, the problem only seemed to occur when the script ran under the SQL Agent. Enter Google. Some searching turned up this article which describes how in versions of Powershell prior to v3 (the SQLPS.exe used by the SQL Agent is based on v2 in SQL 2008R2), single element (or empty) arrays are collapsed into a single (non-array) item, which lacks the .Count property. the workaround is to wrap the results as an array type with @(), like IF (@(QueryResults).Count -gt 0). Sure enough, this worked ... in testing on my local machine. Now, when running through the SQL Agent, I receive the emailed results even if the array is empty. Some testing confirmed that when the array is ostensibly empty, the Count property is still returning a value of 1, but only when running through the SQL Agent. WTH.
Having not yet discovered the underlying cause of this issue, I've found a way around it. I first convert the entire array to a string and test its length rather than the number of elements in the array. If the length > 1 then I know I should send the notification email. It's kind of a hack and I don't like it. So, if anyone reading this should know the real reason for the behavior and a better way around it, I'd love to hear it.
Should you find it useful, here is an example of the full script that I use to find databases not owned by 'sa'. You can add exceptions to it, to ignore specific non 'sa' users. This is useful to skip databases for applications like Sharepoint that use database ownership as part of its security strategy.
$ServerGroup = 'Production'
#Replace with the name of your CMS Server group
to check
$CMServer = 'CMSServerInstance' #Replace
with the name of your CMS SQL instance
$Operator = 'DBAs' #Replace with
the Operator Group that should be emailed
$FromAddress = 'sql_checkup@yourdomain.com'
#Replace with From Address for email being sent
$msg_Subject = "$ServerGroup
Database Ownership Report" #Replace
with subject line for emailbeing sent
$SmtpServer = 'relay.yourdomain.local'
#Replace with name of SMTP Server for email relay
## Fetch List of Servers in the group $ServerGroup from
Central Management Server $CMServer ##
$ServerList = Invoke-Sqlcmd -Query "SELECT
DISTINCT s.server_name AS 'ServerName'
FROM msdb.dbo.sysmanagement_shared_server_groups_internal g
INNER JOIN
msdb.dbo.sysmanagement_shared_registered_servers_internal s
ON g.server_group_id = s.server_group_id
WHERE g.name = '$ServerGroup'
ORDER BY s.server_name ;" -ServerInstance $CMServer
## Build results by executing our query against each server in
the list we just built ##
$QueryResults = foreach ($ServerName in
$ServerList)
{
Invoke-Sqlcmd -Query "SELECT @@ServerName As ServerName, d.name As
DatabaseName, s.name As Owner
FROM sys.databases d
LEFT OUTER JOIN sys.syslogins s on d.owner_sid = s.sid
WHERE ((s.name
<> 'sa' OR s.name IS NULL) AND ISNULL(s.name,'') NOT
IN('DOMAIN\excluded_user', 'APP_ExcludedUser')) AND d.source_database_id IS
NULL
ORDER BY
@@ServerName, d.name
" -ServerInstance
$ServerName.ServerName
}
## If there are any rows in the result set, email it to the
Operator specified in $Operator ##
$QueryResultsString = Out-String -InputObject $QueryResults
If ($QueryResultsString.Length -gt 0)
{
## Build data
set of Email addresses for the operator specified (if more than one address,
assumes ';' delimiter) ##
$email_addresses
= Invoke-Sqlcmd
-Query "create
table #email (address nvarchar(100))
declare @email_addresses
nvarchar(100)
declare @address
nvarchar(100)
declare @Pos INT
select @email_addresses
= email_address from msdb.dbo.sysoperators where name = '$Operator'
SET @email_addresses =
LTRIM(RTRIM(@email_addresses))+ ';'
SET @Pos =
CHARINDEX(';', @email_addresses, 1)
WHILE @Pos > 0
BEGIN
SET @address =
LTRIM(RTRIM(LEFT(@email_addresses, @Pos - 1)))
IF @address
<> ''
BEGIN
INSERT
INTO #email (address)
VALUES
(CAST(@address AS VARCHAR(MAX)))
END
SET
@email_addresses = RIGHT(@email_addresses, LEN(@email_addresses) - @Pos)
SET @Pos =
CHARINDEX(';', @email_addresses, 1)
END
select * from #email
drop table #email
;" -ServerInstance
$CMServer
## Convert the
data set to a string array for later use to build addressee's ##
$email_addresses_array
= @()
for ($i=0; $i -le $email_addresses.Length; $i++)
{
$email_addresses_array
= $email_addresses_array
+ ($email_addresses[$i].ItemArray -join
",")
}
## Build the
Email and sent it##
$Body = "The following
databases are not owned by sa :`n"
$Body += $QueryResults
| Out-String
$msg = New-Object System.Net.Mail.MailMessage
$msg.From = new-object System.Net.Mail.MailAddress($FromAddress)
foreach ($email_address in
$email_addresses_array)
{
If ($email_address.Length
-gt 0)
{
$msg.To.Add("$email_address")
}
}
$msg.Subject = $msg_Subject
$msg.Body = $Body
$smtpClient
= new-object
System.Net.Mail.SmtpClient
$smtpClient.Host = $SmtpServer
$smtpClient.Send($msg)
}
