Solved

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

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Synchronize a new Active Directory domain with an existing Office 365 tenant
A procedure for exporting installed hotfix details of remote computers using powershell
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now