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.

Statistics:
-----------
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.