Solved

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

Posted on 2009-04-09
4
954 Views
Last Modified: 2012-05-06
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!
0
Comment
Question by:KTN-IT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 18

Expert Comment

by:BSonPosh
ID: 24109959
If I understand you properly... this is what you do for the filter
$data | ?{$_.email -eq "supervisor@mail.com"}
0
 
LVL 5

Author Comment

by:KTN-IT
ID: 24112465
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.
0
 
LVL 5

Author Comment

by:KTN-IT
ID: 24112615
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

0
 
LVL 5

Accepted Solution

by:
KTN-IT earned 0 total points
ID: 24117855
I have fallen in love with Powershell.  It's the best thing Microsoft has come up with since Excel.  Windows finally has a command-line that equals or surpasses Unix shells.

Here's my solution:
#Thanks to http://mspowershell.blogspot.com/2009/02/t-sql-query-with-object-based-result.html
#for help learning how to query SQL databases from Powershell
function ConnectSQL {
    Param ($server, $query, $database)
    $conn = new-object ('System.Data.SqlClient.SqlConnection')
    $connString = "Server=$server;Integrated Security=SSPI;Database=$database"
    $conn.ConnectionString = $connString
    $conn.Open()
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $Rset = $sqlCmd.ExecuteReader()
    ,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
}
#Thanks to http://mspowershell.blogspot.com/2009/02/t-sql-query-with-object-based-result.html
function QuerySQL {
    Param ($server, $query, $database = "master")
    $data = ConnectSQL $server $query $database
    while ($data.read() -eq $true) {
        $max = $data.FieldCount -1
        $obj = New-Object Object
        For ($i = 0; $i -le $max; $i++) {
            $name = $data.GetName($i)
            $obj | Add-Member Noteproperty $name -value $data.GetValue($i)
     }
     $obj
    }
}
#Thanks to http://www.brokenhaze.com/index.php?topic=msh
function sendEmail {
    Param ($recipients, $messagebody)
    $objSMTPClient = new-object System.Net.Mail.smtpClient
    $objSMTPClient.host = "smtp.mail.com"
    $objCreds = new-object System.Net.networkCredential
    $objCreds.UserName = "automail@mail.com"
    $objCreds.Password = "opensesame"
    $objSMTPClient.Credentials = $objCreds
    $objMailMessage = new-object System.Net.Mail.MailMessage
    $objMailMessage.Subject = "Hello World!"
    $objMailMessage.IsBodyHTML = $true
    $objMailMessage.Body = $messagebody
    $objMailMessage.From = "automail@mail.com"
    $recipients | % {$objMailMessage.To.add($_)}
    $objSMTPClient.Send($objMailMessage)
}
 
$data = QuerySQL "SQLSERVERNAME" "SELECT EmailAddress FROM Table" "DatabaseName"
 
#Thanks to http://www.powershellpro.com/powershell-tutorial-introduction/ for getting me started!
for ($i=0; $i -le $data.length-1; $i++) {
    [string]$strSQL="SELECT Name,ItemName,Expires FROM ExpiredTraining WHERE Email='$($data[$i].EmailAddress)'"
    $data2=QuerySQL "SQLSERVERNAME" $strSQL "DatabaseName"
    sendEmail "receipient@mail.com" ($data2|convertto-Html)
}

Open in new window

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A procedure for exporting installed hotfix details of remote computers using powershell
A brief introduction to what I consider to be the best editor for PowerShell.
The viewer will learn how to count occurrences of each item in an array.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question