UPDATED: Inventory Outlook GAL

Recently my company was acquired and the first thing to be migrated was our email infrastructure. Firstly, this makes all the work I did inventorying the Rackspace system both very useful up front and very useless once we’ve transitioned. The usefulness of course was that we had a full inventory of every mailbox, contact, and distribution list ready to send over for them to use in setting up all the new Exchange objects on their end. But, as with any big transition, mistakes get made, so in order to track them all down I had to pull out the Outlook GAL Inventory script that I used to use before I figured out the Rackspace API. Using this script I was able to get an inventory of the GAL for our new Exchange host and then compare the results in SQL to what I showed from Rackspace.  Its been well over a year since I wrote the original and it needed some updating, so here it is:

#========================================================================
# Created with: SAPIEN Technologies, Inc., PowerShell Studio 2012 v3.0.8
# Created on:   10/11/2012 6:30 PM
# Created by:   richard.hopkins
# Filename:     GAL2SQL
#========================================================================

#region Script Initializers
$Outlook = New-Object -Comobject Outlook.Application
$Mapi = $Outlook.GetNamespace("MAPI")
$Gal = $Mapi.GetGlobalAddressList()

$usercollection=@()
$groupcollection=@()
$groupmembercollection=@()
$contactcollection=@()

$Debug = $true
$SQLServer = "SERVER"
If ($Debug) {$SQLDatabase = "Inventory_Dev"}
Else {$SQLDatabase = "Inventory"}

#Create persistant connection to SQL to keep Temp Tables alive
$conn = New-Object Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$SQLServer;Database=$SQLDatabase;Trusted_Connection=Yes;Integrated Security=SSPI;"
$conn.Open()
$SQLQuery = ""
$SQLCmd = New-Object System.Data.SqlClient.SqlCommand $SQLQuery, $conn
#endregion

#region Create Temporary Tables on SQL Server
$SQLQuery = "CREATE TABLE [#MailGroupMembership](
	[GroupName] [nvarchar](255) NULL,
	[GroupSMTP] [nvarchar](255) NULL,
	[MembersSMTP] [nvarchar](255) NULL)"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null

$SQLQuery = "CREATE TABLE [#MailGroupObjects](
	[Name] [nvarchar](255) NULL,
	[Alias] [nvarchar](255) NULL,
	[PrimarySmtpAddress] [nvarchar](255) NULL,
	[Comments] [nvarchar](255) NULL)"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null

$SQLQuery = "CREATE TABLE [#MailUserObjects](
	[Name] [nvarchar](255) NULL,
	[Alias] [nvarchar](255) NULL,
	[FirstName] [nvarchar](255) NULL,
	[LastName] [nvarchar](255) NULL,
	[PrimarySMTPAddress] [nvarchar](255) NULL,
	[Address] [nvarchar](255) NULL,
	[Manager] [nvarchar](255) NULL,
	[AssistantName] [nvarchar](255) NULL,
	[BusinessTelephoneNumber] [nvarchar](255) NULL,
	[City] [nvarchar](255) NULL,
	[Comments] [nvarchar](255) NULL,
	[CompanyName] [nvarchar](255) NULL,
	[Department] [nvarchar](255) NULL,
	[JobTitle] [nvarchar](255) NULL,
	[MobileTelephoneNumber] [nvarchar](255) NULL,
	[OfficeLocation] [nvarchar](255) NULL,
	[PostalCode] [nvarchar](255) NULL,
	[StateOrProvince] [nvarchar](255) NULL,
	[StreetAddress] [nvarchar](255) NULL,
	[ExtraSMTPAddresses] [nvarchar](max) NULL)"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
#endregion

#region Main Script Body
#setup progress bar
$totalTicks = 0
ForEach ($Entry In $Gal.AddressEntries) {$script:totalTicks++}
$eachTick = 100/$totalTicks
$currentTick = 0

ForEach ($Entry In $Gal.AddressEntries) {
	Write-Progress -Activity "Inventory Global Adress List" -Status "Gathering Information on object $($entry.name)" -PercentComplete $currentTick
	
	#UserObjects
	If ($entry.AddressEntryUserType -eq 0) {
		$userobject = $entry.getExchangeUser() | select Name,Alias,FirstName,LastName,`
			PrimarySMTPAddress, Address,Manager,AssistantName, BusinessTelephoneNumber, City, `
			Comments,CompanyName,Department,Jobtitle, MobileTelephoneNumber,OfficeLocation, `
			Postalcode, StateorProvince, StreetAddress, @{Name="ExtraSMTPAddresses"; Expression = {`
			$entry.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x800F101E") `
			| Where-Object {$_.Contains("@")}}}

        $SQLQuery = "INSERT INTO [#MailUserObjects] VALUES ('$($userobject.Name -replace "'","''")','" + `
            "$($userobject.Alias -replace "'","''")','$($userobject.FirstName -replace "'","''")','" + `
			"$($userobject.LastName -replace "'","''")','$($userobject.PrimarySMTPAddress -replace "'","''")','" + `
			"$($userobject.Address -replace "'","''")','$($userobject.Manager -replace "'","''")','" + `
            "$($userobject.AssistantName -replace "'","''")','$($userobject.BusinessTelephoneNumber)','" + `
			"$($userobject.City -replace "'","''")','$($userobject.Comments -replace "'","''")','" + `
			"$($userobject.CompanyName -replace "'","''")','$($userobject.Department -replace "'","''")','" + `
            "$($userobject.JobTitle -replace "'","''")','$($userobject.MobileTelephoneNumber)','" + `
			"$($userobject.OfficeLocation -replace "'","''")','$($userobject.PostalCode)','" + `
			"$($userobject.StateOrProvince -replace "'","''")','$($userobject.StreetAddress -replace "'","''")','" + `
            "$($userobject.ExtraSMTPAddresses -replace "'","''")')"

		Write-Output $SQLQuery
		$SQLCmd.CommandText = $SQLQuery
		$SQLCmd.ExecuteNonQuery() | Out-Null
	}
     
	#GroupObjects
	ElseIf($entry.AddressEntryUserType -eq 1) {
		$groupobject = $entry.GetExchangeDistributionList() | select Name,Alias,PrimarySmtpAddress,Comments
        $SQLQuery = "INSERT INTO [#MailGroupObjects] VALUES ('$($groupobject.Name -replace "'","''")','" + `
            "$($groupobject.Alias -replace "'","''")','$($groupobject.PrimarySmtpAddress -replace "'","''")','" + `
			"$($groupobject.Comments -replace "'","''")')"
		Write-Output $SQLQuery
		$SQLCmd.CommandText = $SQLQuery
		$SQLCmd.ExecuteNonQuery() | Out-Null

		$groupobject = $entry.GetExchangeDistributionList()
		$members = $groupobject.GetExchangeDistributionListMembers()
	
		$membersobj= '' | Select GroupSMTP,MembersSMTP
		$smtpmembers = ''
	
        ForEach ($member in $members) {
            If ($member.AddressEntryUserType -eq 0) {
                $membersmtp = $member.getExchangeUser() | Select PrimarySmtpAddress
                $SQLQuery = "INSERT INTO [#MailGroupMembership] VALUES ('$($groupobject.Name -replace "'","''")','" + `
                    "$($groupobject.PrimarySMTPAddress -replace "'","''")','$($membersmtp.PrimarySMTPAddress -replace "'","''")')"
                Write-Output $SQLQuery
				$SQLCmd.CommandText = $SQLQuery
				$SQLCmd.ExecuteNonQuery() | Out-Null
            }
            ElseIf ($member.AddressEntryUserType -eq 1) {
                $membersmtp = $member.GetExchangeDistributionList() | select PrimarySmtpAddress
                $SQLQuery = "INSERT INTO [#MailGroupMembership] VALUES ('$($groupobject.Name -replace "'","''")','" + `
                    "$($groupobject.PrimarySMTPAddress -replace "'","''")','$($membersmtp.PrimarySMTPAddress -replace "'","''")')"
                Write-Output $SQLQuery
				$SQLCmd.CommandText = $SQLQuery
				$SQLCmd.ExecuteNonQuery() | Out-Null
            }
            ElseIf ($member.AddressEntryUserType -eq 5)    {
                    $membersmtp = $member.GetExchangeUser() | select PrimarySmtpAddress
                $SQLQuery = "INSERT INTO [#MailGroupMembership] VALUES ('$($groupobject.Name -replace "'","''")','" + `
                    "$($groupobject.PrimarySMTPAddress -replace "'","''")','$($membersmtp.PrimarySMTPAddress -replace "'","''")')"
                Write-Output $SQLQuery
				$SQLCmd.CommandText = $SQLQuery
				$SQLCmd.ExecuteNonQuery() | Out-Null
            }
        }	
	} 
	$currentTick += $eachTick
}
#endregion

#region Drop current tables and replace with temp data
$SQLQuery = "DROP TABLE [MailUserObjects]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "SELECT * INTO [MailUserObjects] FROM [#MailUserObjects]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "DROP TABLE [MailGroupObjects]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "SELECT * INTO [MailGroupObjects] FROM [#MailGroupObjects]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "DROP TABLE [MailGroupMembership]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "SELECT * INTO [MailGroupMembership] FROM [#MailGroupMembership]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
#endregion

#region Script Cleanup
$SQLCmd.Dispose()
$conn.Close()
$conn.Dispose()
#endregion 

edit: for some reason vs2html bugged out coloring the code, but it’s good.

Advertisements
This entry was posted in Programming and tagged , , , , , . Bookmark the permalink.

2 Responses to UPDATED: Inventory Outlook GAL

  1. Pingback: Inventory Outlook | Rich Hopkins

  2. Pingback: Updating Active Directory using PowerShell and SQL | Rich Hopkins

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s