Mixing SQL query and PowerShell send-mailmessage

2
votes

I've combed through the help files here and can't quite find the right combination of what I'm looking for.

Someone before my time created a PowerShell script that is used to create user accounts in Active Directory. Currently, when a new account is requested, the admin (me) runs a script that creates the AD account, then goes back to a Web-based form to enter the user name/password (also generated by that script) to close the request. Closing it through the form also kicks off an email to the requestor and two system admins with the user name and password in it.

Because it's silly to have to copy/paste anything, I've successfully added cmdlets to the script write the user name, password and other data back to the SQL table directly and close the ticket. I've also successfully added a send-mailmessage cmdlet to generate that user name/pswd email. My problem comes when I need to look up (via a SQL query) and send to the email address of the original requestor. The request form captures the user ID of the requestor, which I then use a query to find their email address from another table and define it as a variable.

$requestor = Invoke-Sqlcmd -ServerInstance [servername] -Database [dbname] -Query "select b.email from [Table1] a left outer join [Table2] b on a.requestedby = b.clockid where a.accrequestid = '$reqID'"

Two things may be causing problems here. When I define that variable and then type $requestor for output, I get

PS SQLSERVER:> $requestor

email
[[email protected]]

I may need to find a way to define that variable as just the email address value without the field header, and I'm not sure how to do that. (I'm very new to PowerShell.)

The other issue is using that variable as part of an array. Currently, my send-mailmessage cmdlet looks somewhat like this:

$PSEmailServer = "[SMTPServerName]" Send-MailMessage -From "[EmailAddress]" -To "[email protected]", "[email protected]", "$requestor" -Subject "User Account Info ($DisplayName)" -Body "The user account you requested has been created."

The email will deliver to email1 and email2, but I get this error from the use of the variable:

Send-MailMessage : The specified string is not in the form required for an e-mail address. At [FileName].ps1:381 char:1 + Send-MailMessage -From "[email protected]" -To "$requestor" -Subject ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidType: (:) [Send-MailMessage], FormatException + FullyQualifiedErrorId : FormatException,Microsoft.PowerShell.Commands.SendMailMessage

I have tried all combinations of single, double and no quotes around that $requestor variable, and tried sending to only the address returned by the variable. No luck.

Does anyone have any experience with something like this? I know I could go create a trigger on the SQL table to send an email instead, but I'd love to figure out what the problem is here. My guess is that it's the former issue...

Many thanks!

(The database is SQL2008R2 and PowerShell is v4.)

1
sql-serverpowershell

1 Answers

0
votes

$requestor will contain the SQL record as an object. As you're selecting the email column in your query this will probably be an array of email addresses.

You can inspect what the $requestor object is by typing $requestor.GetType() at the PowerShell prompt.

However, at a guess I'd imagine you need to use:

($requestor.email)[0] in your Send-MailMessage command to access the string property of the returned object or create a variable for the requestor email address by doing something like:

$reqEmail = $requestor | Select-Object -ExpandProperty email -First 1