Tuesday, January 12, 2016

GAL Cleanup Part 1 - Expire old contacts

Over the 8 years that I've worked here, we've managed to virtually triple the number of contacts we host in our Global Address List. At this point, we have 90,000 mailboxes and 60,000 mail enabled contacts. I suspect that a vast majority of these contacts have not been sent to in several years! Heck, considering the volatile work environment, it's more than likely a good portion of those are no longer good. Customer at another company, leaves and we never delete their contact.

Phase 1:

This got me thinking.. Read in the primary smtp address from 100 or so contacts. Search the message tracking logs (on server hosting Internet bound email) for something going to this contact. Each contact I touch, I would mark Custom Attribute 9, with date/time. If I found an entry in the tracking logs, mark Custom Attribute 10. Relatively easy script to write.

#This is too easy!!
get-contacts -resultsize 100 | ForEach {get-messagetrackinglog -start (7 days ago) -recipient $_.externalemailaddress.addressstring.tostring()}

Unfortunately, that TOOK FOREVER!
  1. We have 12 hub transports that send out email to the Internet. This means to effectively scan for a single recipient, I'd need to scan all 12 servers. 
  2. We process probably a million messages each day going out to the Internet.
  3. As I said, we have 60,000 mail enabled contacts.
  4. We keep tracking logs back 30 days.
If I let it run in this state, we'd be scanning 1/30th contacts every day each month..

Phase 2:

I noticed that the Recipient field on get-messagetrackinglog uses OR logic. I could technically buffer up a big handful of recipients into that field and search for them all at once. 20, I'll start with twenty recipients per search. 

These are not the results I was hoping for...
Evidently, I've stumbled onto a 'known bug' with the cmdlet. Your search has to be under so many characters (256 iirc). Once you exceed that, it fails. Only workaround is to reduce the # of entries in your query. At one point, I reduced my # of recipients to only 5 addresses and the script was failing. What next? Only 2 people at a time? Not much of a time savings. 

Phase 3:

While walking out to my car that night I was discussing this project with a co-worker. During explaining the concept to him, I came up with an interesting idea. Scan message tracking logs for non-mailbox users. OK, it sounds worse, but it pays off. 
  1. Create giant string of every accepted domain. This will be used to filter out every mailbox recipient.
  2. Find and fine-tune 'directory searcher' function to validate email address is in GAL. 
So here's my basic process. On each hub transport:
  •  read message tracking logs and spit out all recipients
  • filter where internal domains -notmatch external email address domain
  • check GAL to see if contact exists for recipient.
  • get mailcontact - put today's date in CA10.
Now some contacts appear to get messages hourly as part of scheduled tasks. So I created a second filter on already touched contacts. 

Exchange 2010 RPC Client Access Logs + Powershell + LogParser

I've been working on a few projects revolving around analyzing Outlook clients connecting to our email environment. The latest request was for a report that would detail how many clients from one office are connecting to email via OWA vs Outlook. While I've seen the function that details CAS connections, it won't work for me. My customers range from over 45 different offices and they include vastly different #s of concurrent users. Many of the offices share the same CAS servers/pools.

This got me looking at the RPC Client Access logs on each box. From here I can filter my connections based on individual mailboxes, members of the same OU, or even distribution group members. Sadly with 40,000 active users, connecting to possible 60+ CAS boxes, that's easily a 9GB of data per person of log data to go through. While I have developed fairly efficient powershell to process this data, (it requires effectively reading in the log file for each server as a CSV and processing it individually) I believe I can do better.

LogParser 2.2

ExchangeServerPro published an article about using LogParser to query RPC Client Access logs. Reviewing this article, I was able to pick up the basics about building a LP query for RPC client. This got me to this: 

#Set up some basics for the script.
#Default path to the RPC Client Access logs. Going to build paths to.
$LogPath = "\C$\Program Files\Exchange\Logging\RPC Client Access\"
#Default path for LogParser executable
$LogparserExec = "C:\Program Files (x86)\Log Parser 2.2\logparser.exe"

#Final Report Path
$TodayString = (Get-Date -Format "yyyyMMdd").tostring()
$ReportPath = "c:\reports\OutlookClientReport_"+$TodayString + ".CSV"

#Get names for all CAS boxes
$CASPool = get-clientaccessServer | %{$_.name}

#Build source statement for all log files. (Reading only today's logs).
[array]$logPaths = $null
$CASPool | %{$logPaths += "'\\"+$_+$LogPath+"RCA_"+$todayString + "*.log'"}
$allServers = $logPaths -join(";")

#Build one REALLY BIG query.
$Query = "SELECT EXTRACT_SUFFIX(client-name,0,'=') as Name,client-software as Software,client-software-version as Version INTO '"+$ReportPath+"' FROM "+$allServers + " where software in ('outlook.exe';'OUTLOOK.EXE') Group BY Name,Software,Version ORDER BY Name"

#Execute LogParser search
& $LogparserExec $Query -i:CSV -nSkipLines:4  # -Stats:Off

When executed, this generates a CSV in the report path specified. It contains each customer who's connected today, and all the Outlook client version they connected with.
Elements processed: 9140619
Elements output:    53206
Execution time:     135.88 seconds (00:02:15.88)
As the 'Client-Name' field is based off the end-user's mailbox alias and/or legacyExchangeDN, you can search for a specific user based off it.

#Get specific mailbox path
$cn = (Get-Mailbox "End-User, Joe").LegacyExchangeDN

#Query that specifies client we are looking for.
$Query = "SELECT EXTRACT_SUFFIX(client-name,0,'=') as Name,client-software as Software,client-software-version as Version INTO '"+$ReportPath+"' FROM "+$allServers + " where software in ('outlook.exe';'OUTLOOK.EXE') and client-name='"+$cn+"' Group BY Name,Software,Version ORDER BY Name"

Now you are searching all of your client access servers for Outlook connections from this specific user. My average search time for a day's log files is about the same 2 minutes per person. 2,000 people will take 4,000 minutes, or about 2 days to run.

Grabbing certain GB of Mailboxes From a DB

We have a few databases that are reaching capacity. These DBs are around 600-700gb in the 1 TB drives we used.

$TotalMoveSize=0;$move = @();get-mailbox -Database DB08 | %{$size = (get-mailboxstatistics -identity $_.identity).totalitemsize.value.tobytes();if ($totalMoveSize + $size -le 300gb){$totalMoveSize += $size;$Move+= $_.identity}}

This one-liner, will read DB08, and keep adding mailboxes to an array until at or near 300gb in size.

Wednesday, October 14, 2015

Checking OWA.. Is Mine Hacked?

We've recently had a number of our staff ask about the recent OWA hack. To appease their fears, I went through and checked my OWA boxes to make sure that the OWAAuth.DLL hadn't been replaced or re-registered using a hacked version.

$servers = @("OWAServer1","OWAServer2") # get-ExchangeServer
$sbFileVersion = {
$FilePath = "C:\Program Files\Exchange\ClientAccess\Owa\auth\OWAAuth.dll"
Get-ChildItem $FilePath |  Select-Object Name,length,@{Name="Version";Expression={$_.versionInfo.FileVersion}},LastWriteTime
Invoke-Command -ScriptBlock $sbFileVersion -ComputerName $servers # | group fileversion
$SBRegistry = {
 #Return installed folder path for OWAAuth.DLL
 $RegKey = "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Components\66A06D0DD155D354CB4C311E0ED2EE9D"
 (Get-ItemProperty $regkey).$regvalue
Invoke-Command -ScriptBlock $SBRegistry -ComputerName $servers 
This does two things. First off, it checks the install path of the OWAAuth.DLL and returns the version and size of the file. I skimmed these to look for differences in the installed files.

Second step, it checks the registry on these same servers and looks to see what's registered. I visually checked to see if the path in the registry matches the install path. (Those registry values I found by searching one of my OWA box registries for the filename.)

My server running Exchange 2010 SP3 RU10 returned:

Name               : OWAAuth.dll
Length             : 104632
Version            : 14.03.0248.002
LastWriteTime      : 5/27/2015 1:47:42 PM
PSComputerName     : OWASERVER1

C:\Program Files\Exchange\ClientAccess\Owa\auth\OWAAuth.dll

Monday, May 11, 2015

Powershell IsNumeric

For the longest time, I've been using a visual basic trick to determine if a variable is a numeric value.

function isNumeric([string] $a) {
    $b = ([Microsoft.VisualBasic.Information]::isnumeric($a))
    return $b

IsNumeric "12"

IsNumeric "Bob"

Looking at the code, all I am doing is type-casting the variable as a integer and seeing if I get an error. So I could simply.

Try {
      [Int]$Variable -is [Int]
} Catch {

So, now in my code instead of evaluating the variable if it's a numeric value, then run through a "if numeric then ____ else _____". Now I simply encapsulate my THEN _ ELSE _  portions into my Try _ Catch _.

try {
 $DaysInt = [int]$daysBack
 $EndDate = Get-Date
 $StartDate = $EndDate.AddDays(-1 * $DaysBack).ToShortDateString()
} catch {
 $StartDate = Get-Date $daysback -Format g -ErrorAction silentlycontinue
  if ($StartDate -ne $null) {
  Write-Host "you entered a date" $StartDate 
  $EndDatestr = (Read-Host "Specify an End Date (enter for today)").trim()
  if ($enddatestr -eq "") {
   $EndDate = Get-Date
  } else {
   $endDate = Get-Date $EndDateStr  

Tuesday, April 14, 2015

Categorize Mailbox Sizes In Buckets

We are starting to plan for our migration to Exchange 2013. As part of the process we are running through the calculator. One of the steps for this report are to categorize your users.  "10% 100mb mailboxes, 50% 500mb users, etc.. " Sure... I could simply create a series of IF THEN statements..

ForEach Mailbox in this group
IF MailboxSize <= 500mb THEN LT500++
IF MailboxSize > 500mb and Mailbox <= 1GB then GT500++

But where's the elegance in that? All I want is a count of mailbox sizes, I don't care to know what the actual sizes are for this report.

Then I was working on an issue and found this mount point space script. This gave me an idea.. I could use expressions to count for me.. So, the idea being, I would do a simple query "Get-MailboxStatistics" and feed it to a formula to do all the math for me.

#Initialize the HASHTABLE.
$hash = @{}
$hash["lt 500mb"]=0
$hash["500 to 1gb"]=0
$hash["le 5gb"]=0
$hash["gt 5gb"]=0

#Create the expressions to do the counting..
$FiveH = @{Expression={$msize=[math]::Round($_.sizebytes /1mb);if ($msize -le 512) {$hash["lt 500mb"]++}}}
$Thou = @{Expression={$msize=[math]::Round($_.sizebytes /1mb);if ($msize -ge 513 -and $msize -lt 1024) {$hash["500 to 1gb"]++}}}
$TwoThou = @{Expression={$msize=[math]::Round($_.sizebytes /1mb);if ($msize -ge 1024 -and $msize -lt 5120) {$hash["le 5gb"]++}}}
$FiveThou = @{Expression={$msize=[math]::Round($_.sizebytes /1mb);if ($msize -gt 5120 ) {$hash["gt 5gb"]++}}}

#Now for the heavy lifting, We run all the mailboxes through the calculations..
Get-mailboxDatabase NWSALES-* | Get-mailboxstatistics | select @{name="SizeBytes";Expression={$_.totalitemsize.value.tobytes()}} | select $FiveH,$thou,$TwoThou,$FiveThou 
When it runs, it doesn't display anything useful. Simply a bunch of blank lines. I suppose I could have it echo the current value at the end, but that isn't necessary. After about 30seconds to a minute, I get back to the PS prompt. The final result of the script is the Hash Table $HASH.

Name Value 
---- ----- 
gt 5gb 223 
le 5gb 1169 
500 to 1gb 670 
lt 500mb 2109 

 With this, I can see half this population is under 500mb, but with another fairly large population between the 1gb to 5gb size.

Tuesday, February 3, 2015


This document will be updated regularly. I use it when checking determining Outlook versions of clients connecting to my environment. Version is that reported in the Exchange 2010 RCA logs for an Outlook client. Later versions I started including the KB article #. OutlookVersionMap.CSV
5.0.3165.0Outlook 2000
6.0.7654.12Outlook 2000
6.0.8153.0Outlook 2000
6.0.8165.0Outlook 2000
6.0.8211.0Outlook 2000
6.0.8244.0Outlook 2000 2002 2002 SP2 2002 SP3 2002 SP3
11.0.5604.0Outlook 2003 RTM
11.0.6352.0Outlook 2003 SP1
11.0.6555.0Outlook 2003 SP2
11.0.8000.0Outlook 2003 SP2
11.0.8161.0Outlook 2003 SP3
11.0.8200.0Outlook 2003 SP3
11.0.8303.0Outlook 2003 SP3 968688
12.0.4518.1014Outlook 2007 RTM
12.0.6024.5000Outlook 2007 RU1
12.0.6211.1000Outlook 2007 SP1
12.0.6212.1000Outlook 2007 SP1
12.0.6300.5000Outlook 2007 SP1
12.0.6315.5000Outlook 2007 SP1
12.0.6423.1000Outlook 2007 SP2
12.0.6504.5001Outlook 2007 SP2
12.0.6509.5000Outlook 2007 SP2
12.0.6529.5000Outlook 2007 SP2
12.0.6539.5000Outlook 2007 SP2
12.0.6550.5000Outlook 2007 SP2
12.0.6554.5000Outlook 2007 SP2
12.0.6557.5000Outlook 2007 SP2
12.0.6562.5003Outlook 2007 SP2
12.0.6606.1000Outlook 2007 SP3
12.0.6607.1000Outlook 2007 SP3
12.0.6661.5000Outlook 2007 SP3 2596598
12.0.6665.5000Outlook 2007 SP3 2687336
12.0.6670.5004Outlook 2007 SP3 2596802
12.0.6672.5000Outlook 2007 SP3 2768023
14.0.4734.1000Outlook 2010 RC/Beta
14.0.4760.1000Outlook 2010 RTM
14.0.4763.1000Outlook 2010 RTM
14.0.6029.1000Outlook 2010 SP1
14.0.6025.1000Outlook 2010 SP1
14.0.6109.5000Outlook 2010 SP1
14.0.6117.5001Outlook 2010 SP1
14.0.6126.5000Outlook 2010 SP1 2687351
14.0.6131.5002Outlook 2010 SP12597090
14.0.7010.1000Outlook 2010 SP1 2817371
14.0.7104.5000Outlook 2010 SP1 2817574
14.0.7106.5003Outlook 2010 SP2
14.0.7108.5000Outlook 2010 SP2 2849973
14.0.7113.5005Outlook 2010 SP2 2687567
14.0.7140.5001Outlook 2010 SP2 28782641/8/2015
14.0.7143.5000Outlook 2010 SP2 ###02/10/2015
15.0.4128.1019Outlook 2013 Preview
15.0.4420.1017Outlook 2013 2817619
15.0.4517.1003Outlook 2013 2817468
15.0.4517.1504Outlook 2013 2727096
15.0.4535.1001Outlook 2013 2817627
15.0.4551.1004Outlook 2013 2825677
15.0.4551.1505Outlook 2013 2837626
15.0.4569.1503Outlook 2013 SP1
15.0.4569.1508Outlook 2013 SP1 2863911
15.0.4615.1000Outlook 2013 SP1 2880470
15.0.4659.1000Outlook 2013 SP1 2889951
15.0.4667.1000Outlook 2013 SP1 289950411/11/2014
15.0.4675.1000Outlook 2013 SP1 292073412/9/2014
15.0.4675.1003Outlook 2013 SP1 291092312/17/2014
15.0.4693.1000Outlook 2013 SP1 292079802/10/2015
15.0.4701.1000Outlook 2013 SP1 295617003/10/2015

15.0.4711.1000 Outlook 2013 SP1 4/16/2015
14.0.7147.5001  Outlook 2010 SP2 4/16/2015
16.0.3930.1008  Outlook 2016 Preview 5/6/2015