Inventory Outlook

UPDATE: This script has been updated here…
As I mentioned before, we’re running a hosted version of Exchange 2007 here (please see the date of publishing for how ridiculous this is) and have nothing to manage Exchange except a web interface. This means there wasn’t a whole lot I could do to fix any problems in the system the data comes from, but at least I could use it to help reconcile everything else. Since I have no connection into our Exchange server itself, the only solution was for me to setup my Outlook application to automatically synchronize its local copy of the GAL and then use Powershell to inventory that. So, here it is, my script to inventory Outlook’s Global Address List.

Import-Module SQLPS -DisableNameChecking
$Outlook = New-Object -Comobject Outlook.Application
$Mapi = $Outlook.GetNamespace("MAPI")
$Gal = $Mapi.GetGlobalAddressList()


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

Invoke-Sqlcmd -Query "DELETE FROM [$SQLDatabase].[dbo].[MailUserObjects]" -ServerInstance $SQLServer -Database $SQLDatabase
Invoke-Sqlcmd -Query "DELETE FROM [$SQLDatabase].[dbo].[MailGroupObjects]" -ServerInstance $SQLServer -Database $SQLDatabase
Invoke-Sqlcmd -Query "DELETE FROM [$SQLDatabase].[dbo].[MailGroupMembership]" -ServerInstance $SQLServer -Database $SQLDatabase

ForEach ($Entry In $Gal.AddressEntries) {
    "Gathering Information on object " + $
    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("") `
            | Where-Object {$_.Contains("@")}}}

        $query = "INSERT INTO [$SQLDatabase].[dbo].[MailUserObjects] VALUES ('$($userobject.Name)','" + `
            "$($userobject.Alias)','$($userobject.FirstName)','$($userobject.LastName)','" + `
            "$($userobject.PrimarySMTPAddress)','$($userobject.Address)','$($userobject.Manager)','" + `
            "$($userobject.AssistantName)','$($userobject.BusinessTelephoneNumber)','$($userobject.City)','" + `
            "$($userobject.Comments)','$($userobject.CompanyName)','$($userobject.Department)','" + `
            "$($userobject.JobTitle)','$($userobject.MobileTelephoneNumber)','$($userobject.OfficeLocation)','" + `
            "$($userobject.PostalCode)','$($userobject.StateOrProvince)','$($userobject.StreetAddress)','" + `
        Invoke-Sqlcmd -Query $query -ServerInstance $SQLServer -Database $SQLDatabase
    ElseIf($entry.AddressEntryUserType -eq 1) {
        $groupobject = $entry.GetExchangeDistributionList() | select Name,Alias,PrimarySmtpAddress,Comments
        $query = "INSERT INTO [$SQLDatabase].[dbo].[MailGroupObjects] VALUES ('$($groupobject.Name)','" + `
        Invoke-Sqlcmd -Query $query -ServerInstance $SQLServer -Database $SQLDatabase

        $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
                $query = "INSERT INTO [$SQLDatabase].[dbo].[MailGroupMembership] VALUES ('$($groupobject.Name)','" + `
                Invoke-Sqlcmd -Query $query -ServerInstance $SQLServer -Database $SQLDatabase
            ElseIf ($member.AddressEntryUserType -eq 1) {
                $membersmtp = $member.GetExchangeDistributionList() | select PrimarySmtpAddress
                $query = "INSERT INTO [$SQLDatabase].[dbo].[MailGroupMembership] VALUES ('$($groupobject.Name)','" + `
                Invoke-Sqlcmd -Query $query -ServerInstance $SQLServer -Database $SQLDatabase
            ElseIf ($member.AddressEntryUserType -eq 5)    {
                    $membersmtp = $member.GetExchangeUser() | select PrimarySmtpAddress
                $query = "INSERT INTO [$SQLDatabase].[dbo].[MailGroupMembership] VALUES ('$($groupobject.Name)','" + `
                Invoke-Sqlcmd -Query $query -ServerInstance $SQLServer -Database $SQLDatabase
This entry was posted in Programming and tagged , , , , , . Bookmark the permalink.

3 Responses to Inventory Outlook

  1. do you have a twitter that i can follow

    • Rich Hopkins says:

      I’m not active on it in any way. I’m surprised anyone found this blog yet, I’ve got indexing turned off until my new domain gets registered and I can move all this to a new host.

  2. Pingback: UPDATED: Inventory Outlook GAL | Rich Hopkins

Leave a Reply

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

You are commenting using your 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