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 🙂




One thought on “Powershell and RegExp: a "match" made my day.

  • July 12, 2012 at 5:44 pm
    Permalink

    Good work

Comments are closed.

%d bloggers like this: