Solved

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

Posted on 2009-04-09
4
951 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
  • 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Set OWA language and time zone in Exchange for individuals, all users or per database.
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

808 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