asked on

How to iterate through a list of email addresses, and do SQL query based on each one?

I have a Powershell script that connects to a SQL database, queries a list of DISTINCT email addresses, and puts them in a variable $data. (To give a bit more background information, this is for an employee training tracking system I am working on, and this query pulls out the email addresses of all supervisors who have employees with expired training [training that needs to be renewed]).

The next part is what I need help with.

In Powershell, I need to iterate through each supervisor email address in $data, and generate another SQL query that returns a list of expired training items that that supervisor is in charge of. I do not need help with the SQL query, I can do that. I need help with the Powershell scripting part of it.

In the end, I need Powershell to email each supervisor a list of expired training items for employees that they are in charge of. My question is, how do I get Powershell to iterate through the supervisor email addresses, perform a query for each one (WHERE email='supervisor@mail.com'), and pass/store the queried list as a text argument/file so then I can email each supervisor?

If this question is too involved, just forget the final 'emailing each supervisor' part. If you can get me through the Powershell iterated querying part, I can ask another question about the actual email process later (or maybe figure it out myself).

Thanks in advance!

BSonPosh

If I understand you properly... this is what you do for the filter
$data | ?{$_.email -eq "supervisor@mail.com"}

KTN-IT

ASKER

Not exactly.

For instance, let's say $data is:

sv1@mail.com
sv2@mail.com
sv3@mail.com
sv4@mail.com

Now, I need Powershell to 1) create, 2) execute, 3)email the results of
the following SQL queries:

SELECT items FROM trainingrecords WHERE ExpirationDate <= getdate() AND
SupervisorEmail='sv1@mail.com'
SELECT items FROM [...] AND SupervisorEmail='sv2@mail.com'
SELECT items FROM [...] AND SupervisorEmail='sv3@mail.com'
SELECT items FROM [...] AND SupervisorEmail='sv4@mail.com'

I suppose that it would be easiest to do steps 1 through 3 for each email address, that way I would not need to create a separate variable/file to store each result set (I could reuse the same variable with each iteration).

Does that make it a little clearer? Thanks for commenting. I realize it is a pretty involved question, but if someone could just get me started in the right direction, it would be a tremendous help.

KTN-IT

ASKER

Here's a page I found with information on emailing in Powershell:

http://powershell.com/cs/media/p/251.aspx

$SmtpClient = new-object system.net.mail.smtpClient  
$smtpclient.Host = 'smtp.web.de' 
$Credentials = new-object System.Net.networkCredential 
$cred = Get-Credential 
$Credentials.domain = "" 
$Credentials.UserName = $cred.UserName 
$Credentials.Password = $cred.GetNetworkCredential() 
$SMTPClient.Credentials = $Credentials 
$smtpclient.Send('sender@web.de', 'recipient@email.de', 'New Mail', 'A message')

Open in new window

ASKER CERTIFIED SOLUTION

KTN-IT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial