TechnoGist

Today I was looking at producing a report from a SQL2005 database and exporting the SQL data into a CSV file. Once completed I would be sending the CSV file to a member of my team via Email. As this is a common request I wanted to automate this with PowerShell.

I had posted previously on basic database connectivity with PowerShell here and using that code as a basis I wrote a PowerShell script that connects to the SQL database and extracts the data into an SQLDataAdapter. The required table is selected and imported into a hash table, and then exported to a CSV file. This file is then attached to an email using the .NET SMTP Mail object and sent on its way via SMTP.

Download Script at PoshCode.org

Code:

#Connection Strings
$Database = "Database"
$Server = "SQLServer"
#SMTP Relay Server
$SMTPServer = "smtp.domain.com"
#Export File
$AttachmentPath = "C:\SQLData.csv"
# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT * FROM dbo.Test_Table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Hash Table
$objTable = $DataSet.Tables[0]
#Export Hash Table to CSV File
$objTable | Export-CSV $AttachmentPath
#Send SMTP Message
$Mailer = new-object Net.Mail.SMTPclient($SMTPServer)
$From = "email1@domain.com"
$To = "email2@domain.com"
$Subject = "Test Subject"
$Body = "Body Test"
$Msg = new-object Net.Mail.MailMessage($From,$To,$Subject,$Body)
$Msg.IsBodyHTML = $False
$Attachment = new-object Net.Mail.Attachment($AttachmentPath)
$Msg.attachments.add($Attachment)
$Mailer.send($Msg)

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

How To: Scanning Active Directory for SMTP Addresses.

When an object in AD is mail enabled it is assigned an SMTP address. Sometimes these addresses are assigned incorrectly to objects or a requested address is already in use on a object and you need to find the object that it is assigned to. I come across this situation sometimes and the simplest way is to query AD for the SMTP address. The SMTP addresses are stored in an AD attribute called “proxyAddresses” this holds all addresses including SMTP. Using the QUEST PowerShell PSSnapIn, you can query AD for this attribute using an LDAP filter.

Querying AD using Quest cmdlets and LDAP query.

Get-QADObject -LdapFilter "(proxyAddresses=*$Address*)" -IncludedProperties "proxyAddresses"

Using the above command with perform the filter, however you will only pull back the default cached set of attributes for the object so I add the ‘-includeallproperties’ switch to pull all attributes available for the object back into the cache.
I also set the ‘-SizeLimit 0′ so the result set isn’t limited to the standard 1000 returned objects.

The below code turns the basic shell command into a ‘Function’ called ‘Get-ProxyAddress.ps1′.
Instructions:

1. Copy the below code into a file called ‘Get-ProxyAddress.ps1′
2. Open the PowerShell shell and traverse to the directory that hosts the above file
3. Run the script using this syntax, “./Get-ProxyAddress.ps1 address@domain.com”
4. address@domain.com being the SMTP address you are searching for.

The output details either that the SMTP address is not found in AD or the below attributes are listed for the object that own the SMTP address.

• Name
• DisplayName
• ObjectClass
• Email
• AccountisDisabled
• AccountisLockedOut
• MailNickName
• LegacyExchangeDN

Download Script from PoshCode.org

Code:

Param (
    [Parameter(Mandatory=$true,
        Position=0,
        ValueFromPipeline=$true,
        HelpMessage="Enter SMTP address to search for in Active-Directory."
    )]
    [string]$objSMTP
	)
Function Get-ProxyAddresses ([string]$Address){
$objAD = $null
$objAD = Get-QADObject -LdapFilter "(proxyAddresses=*$Address*)" -IncludeAllProperties -SizeLimit 0 -ErrorAction SilentlyContinue
Write-Output $objAD
}#Close Function
#Validate Quest PSSnapin is loaded
Add-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue
#Run Function to search AD for SMTP address
$Results = $null
$Results = Get-ProxyAddresses -Address $objSMTP | Select-Object Name,DisplayName,ObjectClass,Email,AccountisDisabled,AccountisLockedOut,MailNickName,LegacyExchangeDN -ErrorAction SilentlyContinue
IF($Results -eq $null){
Write-Host ""
Write-Host "No Object Found with .attribute[proxyAddress] containing $objSMTP."}
Else{$Results | Format-List *}
#End

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.