Friday, March 27, 2009

Sanitizing Get-LogonStatistics cmdlet data to view better Logon stats in Exchange 2007

If you have used the get-Logonstatistics cmdlet you will know that it’s a great tool for looking at who’s logged onto your Exchange server using Outlook but the results set that it returns can be a little awkward to use in a continuative way. This is way of saying there is a lot of noise in what is returned also because logons are happening at different times of the day if you are looking to log this information over a period of time so you can do statistical analysis of the data it can be a little difficult. So what this script does is first looks at sanitizing the data by only looking at the results from get-Logonstatistics that actually represent mailboxes and also reduces down each logon so it only shows one entry per IP/Client connection. To create files that can be used to look at logon statistics it records the results of each query in a daily file and uses some logic to find new logons and filter existing ones.

How it works

Like many of the scripts I post it uses a lot of different powershell functions to achieve different levels of functionality. It uses Import-csv and Export-csv to keep track of the history status as well as two hashtables are used with an aggregation key that filters down the result set of a Get-logonstatistic query down to a useable format. A get-mailbox query is also used to make sure that only logons that relate to actual mailboxes are included in the results.

Running the Script

When you run the script it takes one argument which is the name of the server you want to run it against. Eg.

.\ sanlog.ps1 yourservername

This is just a simple start but it gives a framework you can could use to create actual logon reports that record when users loged on and logged off and how long they where using resources on your server.

I’ve put a download of this script here the script itself looks like.

$ServerName = $args[0]

$ExcomCollection = @()
$MBHash = @{ }
$MBHistHash = @{ }

$HistoryDir = "c:\LogonHistory"

if (!(Test-Path -path $HistoryDir))
{
New-Item $HistoryDir -type directory
$frun = 1
}

$datetime = get-date
$fname = $script:HistoryDir + "\"
$fname = $fname + $datetime.ToString("yyyyMMdd") + $ServerName + "-LogonHist.csv"

Import-Csv ($fname) | %{
$idvalue = $_.identity.ToString()
$logonEvent = $_
$ltime = [datetime]::Parse($_.LogonTime)
if ($_.ClientIPAddress -eq $null){$agvalue = $_.identity.ToString() + $_.UserName + $ltime.ToString("hhmm").Substring(0,3)}
else{$agvalue = $_.identity.ToString() + $_.UserName + $_.ClientIPAddress}
if ($MBHistHash.Containskey($agvalue) -eq $false){
$MBHistHash.Add($agvalue,$_)
}

}


get-mailbox -server $ServerName -ResultSize Unlimited | foreach-object{
if ($MBHash.Containskey($_.LegacyExchangeDN.ToString()) -eq $false){
$MBHash.add($_.LegacyExchangeDN.ToString(),$_)
}
}
$LogonUnQ = @{ }

get-logonstatistics | foreach-object{
$idvalue = $_.identity.ToString()
$logonEvent = $_
$ltime = $_.LogonTime
if ($_.ClientIPAddress -eq $null){$agvalue = $_.identity.ToString() + $_.UserName + $ltime.ToString("hhmm").Substring(0,3)}
else{$agvalue = $_.identity.ToString() + $_.UserName + $_.ClientIPAddress}
if ($idvalue -ne $null){
if ($LogonUnQ.Containskey($agvalue) -eq $false){
if ($MBHash.Containskey($idvalue)){
$LogonUnQ.Add($agvalue,$logonEvent)
if ($MBHistHash.Containskey($agvalue) -eq $false){
$MBHistHash.Add($agvalue,$_)
}
else{
$ts = New-timeSpan $MBHistHash[$agvalue].LogonTime $ltime
if ($ts.minutes -gt 5){
$MBHistHash.Add($agvalue+$ltime,$_)

}
}
}

}
}
}
foreach ($row in $MBHistHash.Values){
$ExcomCollection += $row
}

$ExcomCollection | export-csv –encoding "unicode" -noTypeInformation $fname

Monday, March 02, 2009

Creating Emails based on a Twitter feed / Friends timeline status updates

Continuing on from my previous post about posting your current Exchange calendar appointments as twitter status updates this script explores some more creative uses of the Twitter and Exchange 2007 API's. This script will read your current Twitter account details and grab your friend’s timeline and then create emails in your inbox (or other folder of choice) for each update in your friends twitter timelines (or the people your following). To match up the Received time of the email with the time the status update is posted on Twitter the script sets the necessary mapi properties to make the message appear that it was received at the same time as the status update was posted. To keep track of which updates have been created as email it downloads the latest feed and then on each run it uses Xpath to read the XML file and loads a hashtable which is used to ensure that no status update is created as an email twice. To access Exchange and create the messages in the inbox from the twitter status update it uses some precanned Exchange Web Services code I’ve added to my EWSUtil powershell library.

How does it work?

A quick run through of the script logic is it will first check to see if there is a temp directory on the C:\ and then it will see if there is a file called twitstatus.xml in this directory. If there is no file then the script knows this is the first run and it won’t bother the check to see if the status updates being read from the friend’s timeline have already been created. If the twitstatus.xml file does exist the file is read and a hashtable of the status Id’s is created. The script then reads the twitter friends timeline feed for the configured account and then using Xpath runs through all the status updates in the feed it does a compare to make sure that your own feed Status updates are excluded and also checks the hashtable to ensure that duplicates aren’t created. If it determines that an email should be added from the status update it calls a EWSUtil method that will create a sent email in the folder specified it does a conversion of the internal Twitter date/time to a date/time that can be use used in .NET and Exchange and handles the conversion to UTC. The Twitter Status text is added to both the Subject and the Body of the message this allows any URL to be accessed that are included in the status update.

Using this script

To use this script you need to have a twitter account where your following other peoples twitter updates. If you have no twitter friends try your Federal politicians. You need to configure the following two variables with your twitter account details

$twitterusername = "username"
$twitterpassword = "password"

Then you need to fill out the following Exchange variables in

$ExchangeServername = "servername"

Put the serverName of youy CAS server Then

$emEmailAddress = “twittest@domain.com”

Put the email address of the Exchange Mailbox where you want to create the updates.In

$userName = "twittest"
$password = "Password"
$domain = "domain"

Put a Username and password that has access to this account if you’re using a Cloud mailbox such as a microsoftonline.com account set the domain to blank and use something like

$emEmailAddress = "twittest@youdomain.microsoftonline.com"
$userName = "twittest@youdomain.microsoftonline.com"
$password = "youpassword"
$domain = ""

This script requires the latest copy of the EWSUtil which you can download from here I’ve put a download of the script here the script itself looks like.

$twitterusername = "username"
$twitterpassword = "password"
$ExchangeServername = "servername"
$emEmailAddress = "twittest@domain.com"
$userName = "twittest"
$password = "Password"
$domain = "domain"

$casURL = "https://" + $ExchangeServername + "/EWS/Exchange.asmx"
[void][Reflection.Assembly]::LoadFile("C:\temp\EWSUtil.dll")

$statushash = @{ }

function convertTwitterTime($ptim){
$year = $ptim.Substring(26,4)
$month = $ptim.Substring(4,3)
$day = $ptim.Substring(0,3)
$dayNum = $ptim.Substring(8,2)
$time = $ptim.Substring(11,8)
$combTime = $day + " " + $dayNum + " " + $month + " " + $year + " " + $time + " GMT"
$convertedTime = [DateTime]::Parse($combTime)
return $convertedTime.ToLocalTime()
}


$TempDir = "c:\Temp"
if (!(Test-Path -path $TempDir))
{
New-Item $TempDir -type directory
}
$StatusFile = $TempDir + "\twitStatus.xml"
if (!(Test-Path -path $StatusFile))
{
"Non Status File First Run ?"
}
else{
"Status Found"
[xml]$StatusXmlDoc = Get-Content $StatusFile
foreach($status in $StatusXmlDoc.statuses.status){
$statushash.Add($status.id.ToString(),1)
}

}
$ewc = new-object EWSUtil.EWSConnection($emEmailAddress,$false, $userName, $password,$domain, $casURL)
[System.Net.ServicePointManager]::Expect100Continue = $false
$request = [System.Net.WebRequest]::Create("http://twitter.com/statuses/friends_timeline.xml")
$request.Credentials = new-object System.Net.NetworkCredential($twitterusername,$twitterpassword)
$request.Method = "GET"
$request.ContentType = "application/x-www-form-urlencoded"
$response = $request.GetResponse()
$ResponseStream = $response.GetResponseStream()
$ResponseXmlDoc = new-object System.Xml.XmlDocument
$ResponseXmlDoc.Load($ResponseStream)
$StatusNodes = @($ResponseXmlDoc.getElementsByTagName("status"))
for($snodes=0;$snodes -lt $StatusNodes.Length;$snodes++){
if ($statushash.Containskey($StatusNodes[$snodes].id) -eq $false){
if ($twitterusername -ne $StatusNodes[$snodes].user.screen_name){
[EWSUtil.EWS.DistinguishedFolderIdType] $dType = new-object EWSUtil.EWS.DistinguishedFolderIdType
$dType.Id = [EWSUtil.EWS.DistinguishedFolderIdNameType]::inbox
$time = convertTwitterTime($StatusNodes[$snodes].created_at)
$ewc.CreateTwitMail($dType,$time,$StatusNodes[$snodes].user.Name.Replace(" ","") + "@twitterexdev.com",$StatusNodes[$snodes].user.Name,$StatusNodes[$snodes].text)
}
}
}
$ResponseXmlDoc.Save($TempDir + "\twitStatus.xml")