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

7 comments:

Anonymous said...

I had to make the following script updates:

# Line 40: add -Server $servername
# get-logonstatistics -Server $servername | foreach-object{


# Line 69: removed –encoding "unicode" for better access from Excel
# $ExcomCollection | export-csv -noTypeInformation $fname

Chad Markley said...

Glen, do you have any resources you can point me to in order to better understandn how to take a PS script like this and either automate it, scheduled delivery, or incorporate it into a dynamic web page (.NET, etc) of some kind?

LOVE YOUR SITE !!!

Glen said...

To automate the script just use a scheduled task. Putting it into an email means turning the output into HTML. This isn't generally to hard eg you might want to look at this script which is an example of this
http://gsexdev.blogspot.com/2009/01/summarize-mailstore-growth-and.html

Cheers
Glen

Anonymous said...

[PS] D:\Mesajlasma\Evren\PS>.\sanlog.ps1 MSGMBX01

Security Warning
Run only scripts that you trust. While scripts from the Internet can be useful,
this script can potentially harm your computer. Do you want to run
D:\Mesajlasma\Evren\PS\sanlog.ps1?
[D] Do not run [R] Run once [S] Suspend [?] Help (default is "D"): r
Import-Csv : Cannot open file D:\LogonHistory\20090824MSGMBX01-LogonHist.csv.
At D:\Mesajlasma\Evren\PS\sanlog.ps1:19 char:11
+ Import-Csv <<<< ($fname) | %{

Glen said...

Sounds like the file is corrupt try deleting it.

Cheers
Glen

Anonymous said...

Or use set-remoteexecutionpolicy unrestricted first

Unknown said...

how can i get the logontime of users with specific dates?