Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-04-09
4
Medium Priority
?
968 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

604 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