Solved

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

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
This article will help you understand what HashTables are and how to use them in PowerShell.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to count occurrences of each item in an array.

777 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