Powershell and RegExp: a "match" made my day.
Today I was working with a customer and friend (Claudio Latini, who I thank for the permission to post this, which is also work of his brain – especially the regular expression you'll see reading on!).
We are running several projects and activities together and, among several other things, he's in the process of migrating his users from Exchange 2003 to Exchange 2007. In this infrastructure, he has some ISA Server that publish both the Exchange2003 and the Exchange2007 frontends.
Now he wanted to know HOW MANY and WHICH ONES of his users actually have a PocketPC or other WIndows Mobile device and were actively connecting to the old FrontEnd. You give out mobile devices to people but those things are usually less "managed" – when compared to corporate PCs, at least. So you loose a bit control of the thing… usually people with mobile devices using ActiveSync in companies are managers, and especially since some of them might be on holiday at the moment, it was important to know WHO were the people that had to be told to reconfigure their device to point to the new name/server BEFORE he would start complaining about ActiveSync not working anymore…
So how do you figure out who's connecting ?
I am NO Exchange expert whatsoever… but a thing that came in handy was the thing that an ISA Server was reverse-publishing the frontend server. I know ISA (and firewalls/proxies in general) much better than Exchange, so I can help on that side. In the log files, ActiveSync Connections looked like the following URL, passing most parameters in the POST request: http://www.company.com/exchange?User=Mario&DeviceID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla (and on an unrelated note: yes, if you try to crawl this link, you are a bot 
)
So we exported ISA logs (there are several tools for this, including "Extract logs", but we did not use a script, we just used a filter for the correct publishing rule in the "Monitoring – Logging" tag in ISA Server Console and then copied and pasted those log lines) and tried to see if PowerShell could help tackle the issue.
Here we load our sample log (in a real log you would have much more information – each single line wrapping several console rows; I cut it short to the URL to make it more readable.
PS> get-content log.txt
http://www.company.com/exchange?User=Mario&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Gino&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Gino&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Mario&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Mario&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Mario&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Mario&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Mario&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Mario&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Mario&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Antonio&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
http://www.company.com/exchange?User=Mario&DevideID=186hkjw6gjw76463uh2g5gi2j3h&Bla=bla
We know Get-Content does not just display the file, it loads the file into a string array.
So we can cycle through the file and try to extract (using a regexp) the string after "User=" and before the first ampersand ("&"), which translates in the following regular expression:
"User=(?<nome>.*?)&"
(the regexp has been the most difficult thing to figure out, but it is very worth the hassle once you've done it…)
PS> get-content log.txt | foreach {$_ -match "User=(?<nome>.*?)&" | out-null; $matches} Name Value ---- ----- nome Mario 0 User=Mario& nome Gino 0 User=Gino& nome Antonio 0 User=Antonio& nome Antonio 0 User=Antonio& nome Gino 0 User=Gino& nome Antonio 0 User=Antonio& nome Antonio 0 User=Antonio& nome Mario 0 User=Mario& nome Mario 0 User=Mario& nome Mario 0 User=Mario& nome Mario 0 User=Mario& nome Antonio 0 User=Antonio& nome Antonio 0 User=Antonio& nome Mario 0 User=Mario& nome Antonio 0 User=Antonio& nome Antonio 0 User=Antonio& nome Mario 0 User=Mario& nome Antonio 0 User=Antonio& nome Antonio 0 User=Antonio& nome Mario 0 User=Mario& nome Antonio 0 User=Antonio& nome Antonio 0 User=Antonio& nome Mario 0 User=Mario& nome Mario 0 User=Mario&
This seems to work. Now we only have to get the Named Captures called "nome" (containing the user name):
PS> get-content log.txt | foreach {$_ -match "User=(?<name>.*?)&" | out-null; $matches["name"]} Mario Gino Antonio Antonio Gino Antonio Antonio Mario Mario Mario Mario Antonio Antonio Mario Antonio Antonio Mario Antonio Antonio Mario Antonio Antonio Mario Mario
Awesome. Now sort them and remove duplicates. Which is one more command in our pipeline:
get-content log.txt | foreach {$_ -match "User=(?<nome>.*?)&" | out-null; $matches["nome"]} | sort-object -uniq
P> get-content log.txt | foreach {$_ -match "User=(?<name>.*?)&" | out-null; $matches["name"]} | sort-object -uniq Antonio Gino Mario PS> PS>
Now you can call those three users and tell them to modify their ActiveSync configuration
Related posts:
- Java… oh Java… (aka "High vs. Low level languages rant")
- How to get a funky-colored Logon Desktop
- Using the SCX Agent with WSMan from Powershell v2
- ITPro vs. Dev: there is no such a thing.
- Much ado about Files Screening in R2
Related posts brought to you by Yet Another Related Posts Plugin.