During the course of migrating from one version of Exchange to the another version of Exchange or just moving between servers you might come across the need to shift some of your OultookAnywhere users who are proxying through one CAS server to another without taking the existing CAS server offline and letting autodiscover sort it out. Well i did so here's how you can do it
First of all this script only works if you can connect to the target pc via WMI remotly and access the registry. If this is all good here's what the script needs to do .
First things first is you need to work out the machine name of the PC you want to connect to and the username of the user you want to move. Eg Get-Logonstatitics | select Username,ClientIpAddress should provide that (you may have to use the IISlog files to workout if they are using OutlookAnywhere).
Now we have the hostname orIpAddress of the machine we want to target we now need to work out the SID of the user. Why you ask well where going to be using the Hkey_User key in the registry to make the change so the path to access the users profile setting includes the SID of that user. To work out the SID of the user we take the username and then using ADSI find the AD object and then thanks to a script from from this ng post we can get the SID from this user object in the format needed to use in the registry path.
Okay great so now we can use WMI to connect to registry Keys that belong to the user we are targeting but which keys do we need to change ? . Thanks to Vinay's script and Oz for his PRF script this helped work out the two entries we are intersted in which are the
RPCProxyServer=PT_UNICODE,0x6622
RPCProxyPrincipalName=PT_UNICODE,0x6625
Now to start glueing this all together I've added some code the first enums all these keys for all profiles for that user and reads those registry entries so the script can specifically target one proxy setting to change (eg only change those values that equal the old proxy server setting) in all profiles. To write the registry entries theres some code the first converts the string you entry for the proxyname into a binary array that can be written to the registry finally this code came out of another script of Sue Mosher's for doing signatures in Outlook.
Okay so to run this script it would look something like this.
cscript changeprx.vbs hostname username oldproxy.com.au newproxy.com.au
I've posted a copy of this script here the script itself looks like
SourceServer = wscript.arguments(2)
TargetServer = wscript.arguments(3)
strComputer = wscript.arguments(0)
strUser = wscript.arguments(1)
set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
mbQuery = "<LDAP://" & strNameingContext & ">;(&(objectclass=person)(samaccountname=" & strUser & "));name,distinguishedName;subtree"
Com.ActiveConnection = Conn
Com.CommandText = mbQuery
Set Rs = Com.Execute
While Not Rs.EOF
Userdn = rs.fields("distinguishedName")
wscript.echo Userdn
rs.movenext
Wend
Set objUser = GetObject("LDAP://" & Userdn)
usrsid = ObjSidToStrSid(objUser.objectSid)
wscript.echo "UserSid : " & usrsid
Const HKEY_USERS = &H80000003
Set oReg =GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
strComputer & "\root\default:StdRegProv")
prProxyVal = "001f6622"
crcertVal = "001f6625"
strKeyPath = usrsid & "\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles"
oReg.EnumKey HKEY_USERS, strKeyPath, arrSubKeys
For Each subkey In arrSubKeys
strFullPath = strKeyPath & "\" & subkey & "\13dbb0c8aa05101a9bb000aa002fc45a"
oReg.GetBinaryValue HKEY_USERS,strFullPath,prProxyVal,prProxyValRes
sval = ""
if not IsNull(prProxyValRes) then
For Each byteValue in prProxyValRes
sval = sval & ChrB(byteValue)
Next
if mid(sval,1,len(SourceServer)) = SourceServer then
newarrValue = StringToByteArray(TargetServer, True)
newarrValue2 = StringToByteArray(("msstd:" & TargetServer), True)
oReg.SetBinaryValue HKEY_USERS,strFullPath,prProxyVal,newarrValue
oReg.SetBinaryValue HKEY_USERS,strFullPath,crcertVal,newarrValue2
wscript.echo "Changed : " & sval
end if
end if
Next
Public Function StringToByteArray _
(Data, NeedNullTerminator)
Dim strAll
strAll = StringToHex4(Data)
If NeedNullTerminator Then
strAll = strAll & "0000"
End If
intLen = Len(strAll) \ 2
ReDim arr(intLen - 1)
For i = 1 To Len(strAll) \ 2
arr(i - 1) = CByte _
("&H" & Mid(strAll, (2 * i) - 1, 2))
Next
StringToByteArray = arr
End Function
Public Function StringToHex4(Data)
' Input: normal text
' Output: four-character string for each character,
' e.g. "3204" for lower-case Russian B,
' "6500" for ASCII e
' Output: correct characters
' needs to reverse order of bytes from 0432
Dim strAll
For i = 1 To Len(Data)
' get the four-character hex for each character
strChar = Mid(Data, i, 1)
strTemp = Right("00" & Hex(AscW(strChar)), 4)
strAll = strAll & Right(strTemp, 2) & Left(strTemp, 2)
Next
StringToHex4 = strAll
End Function
Function ArrayToMB(A)
Dim I, MB
For I = LBound(A) To UBound(A)
MB = MB & ChrB(A(I))
Next
ArrayToMB = MB
End Function
Function ObjSidToStrSid(arrSid)
' Function to convert OctetString (byte array) to Decimal string (SDDL)
Dim strHex, strDec
strHex = OctetStrToHexStr(arrSid)
strDec = HexStrToDecStr(strHex)
ObjSidToStrSid = strDec
End Function ' ObjSidToStrSid
Function OctetStrToHexStr(arrbytOctet)
' Function to convert OctetString (byte array) to Hex string.
Dim k
OctetStrToHexStr = ""
For k = 1 To Lenb(arrbytOctet)
OctetStrToHexStr = OctetStrToHexStr _
& Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
Next
End Function ' OctetStrToHexStr
Function HexStrToDecStr(strSid)
Const BYTES_IN_32BITS = 4
Const SRL_BYTE = 0
Const IAV_START_BYTE = 2
Const IAV_END_BYTE = 7
Const RID_START_BYTE = 8
Const MSB = 3 'Most significant byte
Const LSB = 0 'Least significant byte
Dim arrbytSid, lngTemp, base, offset, i
ReDim arrbytSid(Len(strSid)/2 - 1)
' Convert hex string into integer array
For i = 0 To UBound(arrbytSid)
arrbytSid(i) = CInt("&H" & Mid(strSid, 2 * i + 1, 2))
Next
' Add SRL number
HexStrToDecStr = "S-" & arrbytSid(SRL_BYTE)
' Add Identifier Authority Value
lngTemp = 0
For i = IAV_START_BYTE To IAV_END_BYTE
lngTemp = lngTemp * 256 + arrbytSid(i)
Next
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
For base = RID_START_BYTE To UBound(arrbytSid) Step BYTES_IN_32BITS
lngTemp = 0
For offset = MSB to LSB Step -1
lngTemp = lngTemp * 256 + arrbytSid(base + offset)
Next
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
Next
End Function ' HexStrToDecStr
Friday, November 13, 2009
Saturday, November 07, 2009
Reporting on Outlook Anywhere users from the IIS logs using Powershell
Logs files are great things but as with a lot of things the hidden value of these resources is often hard to see at there face value. I had a very simple problem this week where i needed to know which users where using Outlook Anywhere and what their IPAddress where. For this the IIS logs could more then provide an answer for this but these log files and get quite large and contain a mix of different information not just Outlook http/rpc traffic.
One of the main challenges you have when looking at IIS logs files is that depending on who enabled logging the Fields and the order they are being logged in may differ. So a parser/script you write to work against one set of logs might not work against another server. To solve this problem is quite simple when a log file is created at the top of the log file on line 4 is written the field format for the file. So if you first read this line parse it into a Hashtable and index it by the fieldname we then know what the index array value will be when you go to read each line of the log file for the field you want to extract.
To make the script easy to use I've added a front end GUI component to make the script present a file select box to select the log file to work with. It then reads the file using get-content and filters it based on OutlookAnywhere log entries using a like filter on “*MSRPC*”. The script groups the results by IPAddress and UserName and builds a exportable collection that produces a csv file or the results of the scan with a simple list of OutlookAnywhere users and what IP address they came from. The cool thing about the script is that it can be easily adapted to scan for anything in a IIS log file eg say you wanted a list of people using a Iphone and their IPaddress.
I've put a download of the script here the script itself looks like
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
$exFileName = new-object System.Windows.Forms.openFileDialog
$exFileName.ShowHelp = $true
$exFileName.ShowDialog()
$fname = $exFileName.FileName
$mbcombCollection = @()
$FldHash = @{}
$usHash = @{}
$fieldsline = (Get-Content $fname)[3]
$fldarray = $fieldsline.Split(" ")
$fnum = -1
foreach ($fld in $fldarray){
$FldHash.add($fld,$fnum)
$fnum++
}
get-content $fname | Where-Object -FilterScript { $_ -ilike “*MSRPC*” } | %{
$lnum ++
if ($lnum -eq $rnma){ Write-Progress -Activity "Read Lines" -Status $lnum
$rnma = $rnma + 1000
}
$linarr = $_.split(" ")
$uid = $linarr[$FldHash["cs-username"]] + $linarr[$FldHash["c-ip"]]
if ($linarr[$FldHash["cs-username"]].length -gt 2){
if ($usHash.Containskey($uid) -eq $false){
$usrobj = "" | select UserName,IpAddress
$usrobj.UserName = $linarr[$FldHash["cs-username"]]
$usrobj.IpAddress = $linarr[$FldHash["c-ip"]]
$usHash.add($uid,$usrobj)
$mbcombCollection += $usrobj
}
}
}
$mbcombCollection | export-csv –encoding "unicode" -noTypeInformation c:\oareport.csv
One of the main challenges you have when looking at IIS logs files is that depending on who enabled logging the Fields and the order they are being logged in may differ. So a parser/script you write to work against one set of logs might not work against another server. To solve this problem is quite simple when a log file is created at the top of the log file on line 4 is written the field format for the file. So if you first read this line parse it into a Hashtable and index it by the fieldname we then know what the index array value will be when you go to read each line of the log file for the field you want to extract.
To make the script easy to use I've added a front end GUI component to make the script present a file select box to select the log file to work with. It then reads the file using get-content and filters it based on OutlookAnywhere log entries using a like filter on “*MSRPC*”. The script groups the results by IPAddress and UserName and builds a exportable collection that produces a csv file or the results of the scan with a simple list of OutlookAnywhere users and what IP address they came from. The cool thing about the script is that it can be easily adapted to scan for anything in a IIS log file eg say you wanted a list of people using a Iphone and their IPaddress.
I've put a download of the script here the script itself looks like
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
$exFileName = new-object System.Windows.Forms.openFileDialog
$exFileName.ShowHelp = $true
$exFileName.ShowDialog()
$fname = $exFileName.FileName
$mbcombCollection = @()
$FldHash = @{}
$usHash = @{}
$fieldsline = (Get-Content $fname)[3]
$fldarray = $fieldsline.Split(" ")
$fnum = -1
foreach ($fld in $fldarray){
$FldHash.add($fld,$fnum)
$fnum++
}
get-content $fname | Where-Object -FilterScript { $_ -ilike “*MSRPC*” } | %{
$lnum ++
if ($lnum -eq $rnma){ Write-Progress -Activity "Read Lines" -Status $lnum
$rnma = $rnma + 1000
}
$linarr = $_.split(" ")
$uid = $linarr[$FldHash["cs-username"]] + $linarr[$FldHash["c-ip"]]
if ($linarr[$FldHash["cs-username"]].length -gt 2){
if ($usHash.Containskey($uid) -eq $false){
$usrobj = "" | select UserName,IpAddress
$usrobj.UserName = $linarr[$FldHash["cs-username"]]
$usrobj.IpAddress = $linarr[$FldHash["c-ip"]]
$usHash.add($uid,$usrobj)
$mbcombCollection += $usrobj
}
}
}
$mbcombCollection | export-csv –encoding "unicode" -noTypeInformation c:\oareport.csv
Tuesday, October 13, 2009
Moving Items into their own folder by a date range using the EWS Managed API and Powershell (attempting to reduce mailbox clutter)
Most people these days are on track to endless mailbox clutter based on an exponentially increasing number of email items that arrive every day. Also Shared mailboxes can soon become an administrative nightmare if mailbox content isn’t actively maintained and archived. One small way of trying to claw back some of this affect is to write a script that can organize messages into their own folder based on certain parameters. In Exchange 2007 (and now 2010) you can use the EWS Managed API to do this reasonably easy. You just need to split this up into some easy chunks of functionality that you can logically splice together.
The first chunk is some code that will find the objects in question that you want to move in the EWS Managed API this involves setting up an appropriate search filter.
Because I want to use a date range this means I need two search criteria meaning you have to use a searchfiltercollection with an AND logical operator. Sounds a little complicated but not really first we need to create the collection
$sfCollection = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And);
Next we need to create two searchfilter objects that will cover the date ranges when want to include in the search (I’ve defined the date as the previous day eg.)
$StartDate = [system.DateTime]::Today.AddDays(-1)
$EndDate = [system.DateTime]::Today
$Sfgt = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsGreaterThan([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, $StartDate)
$Sflt = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsLessThan([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, $EndDate)
Then all you need to do is add these objects to the serachcollection
$sfCollection.add($Sfgt)
$sfCollection.add($Sflt)
The next thing we need to do is create a folder for the item to go into which is pretty easy Im just going to save it as a sub folder of the inbox and name it after the current date
$NewFolder = new-object Microsoft.Exchange.WebServices.Data.Folder($service)
$NewFolder.DisplayName = $EndDate.ToString("yyyy-MM-dd")
$NewFolder.Save($InboxFolder.Id.UniqueId)
Now it just a matter of going through the items in the result set and move these to the folder we just created
foreach ($miMailItems in $frFolderResult.Items){
$miMailItems.Subject.ToString()
$miMailItems.Move($NewFolder.Id.UniqueId)
}
I put a download of this code here the complete script itself looks like the following it basically uses the currently logged on user to access the mailbox configured via the email address.
$MailboxName = "user@domain.com"
$StartDate = [system.DateTime]::Today.AddDays(-1)
$EndDate = [system.DateTime]::Today
$dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"
[void][Reflection.Assembly]::LoadFile($dllpath)
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)
$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"
$aceuser = [ADSI]$sidbind
$service.AutodiscoverUrl($aceuser.mail.ToString())
$folderid = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox,$MailboxName)
$InboxFolder = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid)
$Sfgt = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsGreaterThan([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, $StartDate)
$Sflt = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsLessThan([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, $EndDate)
$sfCollection = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And);
$sfCollection.add($Sfgt)
$sfCollection.add($Sflt)
$view = new-object Microsoft.Exchange.WebServices.Data.ItemView(2000)
$frFolderResult = $InboxFolder.FindItems($sfCollection,$view)
$NewFolder = new-object Microsoft.Exchange.WebServices.Data.Folder($service)
$NewFolder.DisplayName = $EndDate.ToString("yyyy-MM-dd")
$NewFolder.Save($InboxFolder.Id.UniqueId)
foreach ($miMailItems in $frFolderResult.Items){
"Moving" + $miMailItems.Subject.ToString()
[VOID]$miMailItems.Move($NewFolder.Id.UniqueId)
}
The first chunk is some code that will find the objects in question that you want to move in the EWS Managed API this involves setting up an appropriate search filter.
Because I want to use a date range this means I need two search criteria meaning you have to use a searchfiltercollection with an AND logical operator. Sounds a little complicated but not really first we need to create the collection
$sfCollection = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And);
Next we need to create two searchfilter objects that will cover the date ranges when want to include in the search (I’ve defined the date as the previous day eg.)
$StartDate = [system.DateTime]::Today.AddDays(-1)
$EndDate = [system.DateTime]::Today
$Sfgt = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsGreaterThan([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, $StartDate)
$Sflt = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsLessThan([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, $EndDate)
Then all you need to do is add these objects to the serachcollection
$sfCollection.add($Sfgt)
$sfCollection.add($Sflt)
The next thing we need to do is create a folder for the item to go into which is pretty easy Im just going to save it as a sub folder of the inbox and name it after the current date
$NewFolder = new-object Microsoft.Exchange.WebServices.Data.Folder($service)
$NewFolder.DisplayName = $EndDate.ToString("yyyy-MM-dd")
$NewFolder.Save($InboxFolder.Id.UniqueId)
Now it just a matter of going through the items in the result set and move these to the folder we just created
foreach ($miMailItems in $frFolderResult.Items){
$miMailItems.Subject.ToString()
$miMailItems.Move($NewFolder.Id.UniqueId)
}
I put a download of this code here the complete script itself looks like the following it basically uses the currently logged on user to access the mailbox configured via the email address.
$MailboxName = "user@domain.com"
$StartDate = [system.DateTime]::Today.AddDays(-1)
$EndDate = [system.DateTime]::Today
$dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"
[void][Reflection.Assembly]::LoadFile($dllpath)
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)
$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"
$aceuser = [ADSI]$sidbind
$service.AutodiscoverUrl($aceuser.mail.ToString())
$folderid = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox,$MailboxName)
$InboxFolder = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid)
$Sfgt = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsGreaterThan([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, $StartDate)
$Sflt = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsLessThan([Microsoft.Exchange.WebServices.Data.ItemSchema]::DateTimeReceived, $EndDate)
$sfCollection = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And);
$sfCollection.add($Sfgt)
$sfCollection.add($Sflt)
$view = new-object Microsoft.Exchange.WebServices.Data.ItemView(2000)
$frFolderResult = $InboxFolder.FindItems($sfCollection,$view)
$NewFolder = new-object Microsoft.Exchange.WebServices.Data.Folder($service)
$NewFolder.DisplayName = $EndDate.ToString("yyyy-MM-dd")
$NewFolder.Save($InboxFolder.Id.UniqueId)
foreach ($miMailItems in $frFolderResult.Items){
"Moving" + $miMailItems.Subject.ToString()
[VOID]$miMailItems.Move($NewFolder.Id.UniqueId)
}
Saturday, September 26, 2009
Exchange 2007 Folder Audit Log Powershell Gui Version 1.0
One of the cool things about new features when they are introduced in Service packs is it gives us all a lot more ways of seeing whats happening day to day within our Email environments and hopefully gives us some windows into miss and poor configuration problems. The folder access audit logs are cool but buried in the windows event logs can be hard to use to gain the clarity that might be useful.
The good thing is with a little bit of powershell, a bit of imagination and some time you can bash together something usefull that will extract that information from the logs, parse (or if your using 2008 xpath). Then group that information into a more usable form and present it back into a Winform so you can interact with it.
I've put together actually two scripts to do this the main difference between these scripts is the method they use to access the log. The first script uses WMI to access the Exchange Audit logs (a couple of weeks okay i said this wouldn't work which i was completly wrong about the issue i was having is that you need to have at least view only Exchange Admin to to query these logs). The second script uses the .NET 3.5 class and Xpath to access the logs but this will only work on platforms that support these new classes eg Vista,7,2008.
This script uses a combination of Exchange Management Shell cmdlets get-mailbox and get-mailuser to get the displayname information about all users and then store these in hashtable which can then be used when referencing information that is retrieved from the event logs. It then retrieves the eventlogs for the defined period and server you select via the GUI and then summerises the number of accesses to each folder and displays them in one grid. You can then select the mailox or useraccount used and get the detail on the raw logs. There is also the ability to export each of the grid to a CSV file from the buttons at the bottom of the GUI.
These two scripts are very beta-ish at the moment because of the amount of data that can build up in these logs depending on your log settings. This means when you query and group this data it can be a big challenge without a proper database what I've found is trying to group more then an hours worth or information can be a bit of issue. I'm also not really decided on the how to best group the data that is coming back I'm leaning more towards a summarised approach but there is always version 2 for that.
I've put a download of the code for the two scripts here the script itself looks like
[System.Reflection.Assembly]::LoadWithPartialName("System.Core")
$UserDNHash = @{ }
$FHash = @{ }
$RHash = @{ }
function FillDnHash(){
$mbServers = get-mailboxserver
$mbServers | foreach-object{
get-mailbox -server $_.Name -ResultSize Unlimited | foreach-object{
$UserDNHash.Add($_.LegacyExchangeDN.ToString(),$_)
}
}
get-mailuser -ResultSize Unlimited | foreach-object{
$UserDNHash.Add($_.LegacyExchangeDN.ToString(),$_)
}
}
FillDnHash
function showdetail(){
if ($GnGroupbyDrop.SelectedItem -eq "UserName Used"){
$rows = $fsTable.Select("AccessedBy = '" + $f2Table.DefaultView[$dgDataGrid.CurrentCell.RowIndex][0] + "'")}
else{
$rows = $fsTable.Select("Mailbox = '" + $f1Table.DefaultView[$dgDataGrid.CurrentCell.RowIndex][0] + "'")}
$frTable.clear()
foreach ($row in $rows){
$frTable.rows.add($row[0].ToString(),$row[1].ToString(),$row[2].ToString(),$row[3].ToString()
,$row[4].ToString(),$row[5].ToString(),$row[6].ToString(),$row[7].ToString(),$row[8].ToString(),
$row[9].ToString())
}
$dgDataGrid1.datasource = $frTable
}
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
$form = new-object System.Windows.Forms.form
# Add DataTable
$Dataset = New-Object System.Data.DataSet
$fsTable = New-Object System.Data.DataTable
$f1Table = New-Object System.Data.DataTable
$f2Table = New-Object System.Data.DataTable
$frTable = New-Object System.Data.DataTable
$f1Table.TableName = "Folder Access Forward"
$f1Table.Columns.Add("Mailbox")
$f1Table.Columns.Add("Inbox #")
$f1Table.Columns.Add("Calendar #")
$f1Table.Columns.Add("FreeBusy #")
$f1Table.Columns.Add("Other #")
$Dataset.tables.add($f1Table)
$f2Table.TableName = "Folder Access Reverse"
$f2Table.Columns.Add("User")
$f2Table.Columns.Add("Inbox #")
$f2Table.Columns.Add("Calendar #")
$f2Table.Columns.Add("FreeBusy #")
$f2Table.Columns.Add("Other #")
$Dataset.tables.add($f2Table)
$fsTable.TableName = "Folder Access Detail"
$fsTable.Columns.Add("RecordID")
$fsTable.Columns.Add("DateTime",[DATETIME])
$fsTable.Columns.Add("Mailbox")
$fsTable.Columns.Add("FolderName")
$fstable.Columns.Add("FolderPath")
$fsTable.Columns.Add("AccessedBy")
$fsTable.Columns.Add("IPAddress")
$fsTable.Columns.Add("MachineName")
$fsTable.Columns.Add("ProgramName")
$fsTable.Columns.Add("ApplicationID")
$Dataset.tables.add($fsTable)
$frtable.TableName = "Folder Access Detail Show"
$frTable.Columns.Add("RecordID")
$frtable.Columns.Add("DateTime",[DATETIME])
$frtable.Columns.Add("Mailbox")
$frtable.Columns.Add("FolderName")
$frtable.Columns.Add("FolderPath")
$frtable.Columns.Add("AccessedBy")
$frtable.Columns.Add("IPAddress")
$frtable.Columns.Add("MachineName")
$frtable.Columns.Add("ProgramName")
$frtable.Columns.Add("ApplicationID")
$Dataset.tables.add($frtable)
$sdetailButton = new-object System.Windows.Forms.Button
$sdetailButton.Location = new-object System.Drawing.Size(550,19)
$sdetailButton.Size = new-object System.Drawing.Size(120,23)
$sdetailButton.Text = "Show Folder Detail"
$sdetailButton.visible = $True
$sdetailButton.Add_Click({showDetail})
$form.Controls.Add($sdetailButton)
$gllogButton = new-object System.Windows.Forms.Button
$gllogButton.Location = new-object System.Drawing.Size(440,19)
$gllogButton.Size = new-object System.Drawing.Size(90,23)
$gllogButton.Text = "Get Logs"
$gllogButton.visible = $True
$gllogButton.Add_Click({getLogs})
$form.Controls.Add($gllogButton)
# Add Server DropLable
$snServerNamelableBox = new-object System.Windows.Forms.Label
$snServerNamelableBox.Location = new-object System.Drawing.Size(10,20)
$snServerNamelableBox.size = new-object System.Drawing.Size(80,20)
$snServerNamelableBox.Text = "ServerName"
$form.Controls.Add($snServerNamelableBox)
# Add Server Drop Down
$snServerNameDrop = new-object System.Windows.Forms.ComboBox
$snServerNameDrop.Location = new-object System.Drawing.Size(90,20)
$snServerNameDrop.Size = new-object System.Drawing.Size(100,30)
get-mailboxserver | ForEach-Object{$snServerNameDrop.Items.Add($_.Name)}
$form.Controls.Add($snServerNameDrop)
# Add DateTimePickers Button
$dpDatePickerFromlableBox = new-object System.Windows.Forms.Label
$dpDatePickerFromlableBox.Location = new-object System.Drawing.Size(10,50)
$dpDatePickerFromlableBox.size = new-object System.Drawing.Size(90,20)
$dpDatePickerFromlableBox.Text = "Logged Between"
$form.Controls.Add($dpDatePickerFromlableBox)
$dpTimeFrom = new-object System.Windows.Forms.DateTimePicker
$dpTimeFrom.Location = new-object System.Drawing.Size(110,50)
$dpTimeFrom.Size = new-object System.Drawing.Size(190,20)
$form.Controls.Add($dpTimeFrom)
$dpDatePickerFromlableBox1 = new-object System.Windows.Forms.Label
$dpDatePickerFromlableBox1.Location = new-object System.Drawing.Size(10,70)
$dpDatePickerFromlableBox1.size = new-object System.Drawing.Size(50,20)
$dpDatePickerFromlableBox1.Text = "and"
$form.Controls.Add($dpDatePickerFromlableBox1)
$dpTimeFrom1 = new-object System.Windows.Forms.DateTimePicker
$dpTimeFrom1.Location = new-object System.Drawing.Size(110,70)
$dpTimeFrom1.Size = new-object System.Drawing.Size(190,20)
$form.Controls.Add($dpTimeFrom1)
$dpTimeFrom2 = new-object System.Windows.Forms.DateTimePicker
$dpTimeFrom2.Format = "Time"
$dpTimeFrom2.value = [DateTime]::get_Now().AddHours(-1)
$dpTimeFrom2.ShowUpDown = $True
$dpTimeFrom2.Location = new-object System.Drawing.Size(310,50)
$dpTimeFrom2.Size = new-object System.Drawing.Size(190,20)
$form.Controls.Add($dpTimeFrom2)
$dpTimeFrom3 = new-object System.Windows.Forms.DateTimePicker
$dpTimeFrom3.Format = "Time"
$dpTimeFrom3.ShowUpDown = $True
$dpTimeFrom3.Location = new-object System.Drawing.Size(310,70)
$dpTimeFrom3.Size = new-object System.Drawing.Size(190,20)
$form.Controls.Add($dpTimeFrom3)
# Add DropLable
$GnGroupbylableBox = new-object System.Windows.Forms.Label
$GnGroupbylableBox.Location = new-object System.Drawing.Size(200,20)
$GnGroupbylableBox.size = new-object System.Drawing.Size(100,20)
$GnGroupbylableBox.Text = "Group Results By"
$form.Controls.Add($GnGroupbylableBox)
$GnGroupbyDrop = new-object System.Windows.Forms.ComboBox
$GnGroupbyDrop.Location = new-object System.Drawing.Size(310,20)
$GnGroupbyDrop.Size = new-object System.Drawing.Size(110,30)
$GnGroupbyDrop.Items.Add("Mailbox Accessed")
$GnGroupbyDrop.Items.Add("UserName Used")
$GnGroupbyDrop.Add_SelectedValueChanged({if ($GnGroupbyDrop.SelectedItem -eq "UserName Used"){$dgDataGrid.datasource = $f2Table}
else{$dgDataGrid.datasource = $f1Table}
})
$form.Controls.Add($GnGroupbyDrop)
# Add Export 1st Button
$exButton1 = new-object System.Windows.Forms.Button
$exButton1.Location = new-object System.Drawing.Size(10,620)
$exButton1.Size = new-object System.Drawing.Size(125,20)
$exButton1.Text = "Export Summary Grid"
$exButton1.Add_Click({exportTable})
$form.Controls.Add($exButton1)
# Add Export 2sn Button
$exButton2 = new-object System.Windows.Forms.Button
$exButton2.Location = new-object System.Drawing.Size(550,620)
$exButton2.Size = new-object System.Drawing.Size(135,20)
$exButton2.Text = "Export Details Grid"
$exButton2.Add_Click({exportDetail})
$form.Controls.Add($exButton2)
# Add DataGrid View
$dgDataGrid = new-object System.windows.forms.DataGridView
$dgDataGrid.Location = new-object System.Drawing.Size(10,100)
$dgDataGrid.size = new-object System.Drawing.Size(530,500)
$dgDataGrid.AutoSizeRowsMode = "AllHeaders"
$form.Controls.Add($dgDataGrid)
$dgDataGrid1 = new-object System.windows.forms.DataGridView
$dgDataGrid1.Location = new-object System.Drawing.Size(550,100)
$dgDataGrid1.size = new-object System.Drawing.Size(450,500)
$dgDataGrid1.AutoSizeRowsMode = "AllHeaders"
$form.Controls.Add($dgDataGrid1)
function getlogs{
$DateTo = New-Object System.DateTime $dpTimeFrom.value.year,$dpTimeFrom.value.month,$dpTimeFrom.value.day,$dpTimeFrom2.value.hour
,$dpTimeFrom2.value.minute,$dpTimeFrom2.value.second
$DateFrom = New-Object System.DateTime $dpTimeFrom1.value.year,$dpTimeFrom1.value.month,$dpTimeFrom1.value.day,
$dpTimeFrom3.value.hour,$dpTimeFrom3.value.minute,$dpTimeFrom3.value.second
$DateFromTS = New-TimeSpan -Start $DateFrom -End ([System.DateTime]::Now)
$DateToTS = New-TimeSpan -Start $DateTo -End ([System.DateTime]::Now)
$f1Table.Clear()
$f2Table.Clear()
$fsTable.Clear()
$elLogQuery = "
$eqEventLogQuery = new-object System.Diagnostics.Eventing.Reader.EventLogQuery("Exchange Auditing", [System.Diagnostics.Eventing.Reader.PathType]::LogName, $elLogQuery);
$eqEventLogQuery.Session = new-object System.Diagnostics.Eventing.Reader.EventLogSession($snServerNameDrop.SelectedItem)
$lrEventLogReader = new-object System.Diagnostics.Eventing.Reader.EventLogReader($eqEventLogQuery)
for($eventInstance = $lrEventLogReader.ReadEvent();$eventInstance -ne $null; $eventInstance = $lrEventLogReader.ReadEvent()){
[System.Diagnostics.Eventing.Reader.EventLogRecord]$erEventRecord = [System.Diagnostics.Eventing.Reader.EventLogRecord]$eventInstance
if($erEventRecord.Properties[5].Value -match "" -eq $false){
$exAuditObject = "" | select RecordID,TimeCreated,FolderPath,FolderName,Mailbox,AccessingUser,MailboxLegacyExchangeDN,AccessingUserLegacyExchangeDN
,MachineName,Address,ProcessName,ApplicationId
$exAuditObject.RecordID = $erEventRecord.RecordID
$exAuditObject.TimeCreated = $erEventRecord.TimeCreated.ToString("yyyy-MM-dd HH:mm:ss")
$exAuditObject.FolderPath = $erEventRecord.Properties[0].Value.ToString()
$exAuditObject.FolderName = $erEventRecord.Properties[1].Value.ToString()
$exAuditObject.Mailbox = $erEventRecord.Properties[2].Value.ToString()
$mbMailbox = $erEventRecord.Properties[5].Value.ToString()
$exAuditObject.AccessingUser = $erEventRecord.Properties[3].Value.ToString()
$AccessingUser = $erEventRecord.Properties[4].Value.ToString()
$exAuditObject.AccessingUserLegacyExchangeDN = $erEventRecord.Properties[4].Value.ToString()
$exAuditObject.MailboxLegacyExchangeDN = $erEventRecord.Properties[5].Value.ToString()
$exAuditObject.MachineName = $erEventRecord.Properties[8].Value.ToString()
$exAuditObject.Address = $erEventRecord.Properties[9].Value.ToString()
$exAuditObject.ProcessName = $erEventRecord.Properties[10].Value.ToString()
$exAuditObject.ApplicationId = $erEventRecord.Properties[12].Value.ToString()
if ($exAuditObject.MailboxLegacyExchangeDN.ToString() -ne $exAuditObject.AccessingUserLegacyExchangeDN.ToString()){
$fsTable.Rows.add($exAuditObject.RecordID,$exAuditObject.TimeCreated.ToString(),
$UserDNHash[$mbMailbox].DisplayName,$exAuditObject.FolderName.ToString(),
$exAuditObject.FolderPath.ToString(),$UserDNHash[$AccessingUser].DisplayName,$exAuditObject.Address.ToString()
,$exAuditObject.MachineName.ToString(),$exAuditObject.ProcessName.ToString(),$exAuditObject.ApplicationId.ToString())
if($Fhash.containskey($mbMailbox)){
switch($exAuditObject.FolderName.ToString()){
"Inbox" {$Fhash[$mbMailbox].InboxCount = $Fhash[$mbMailbox].InboxCount + 1}
"Calendar" {$Fhash[$mbMailbox].CalendarCount = $Fhash[$mbMailbox].CalendarCount + 1}
"FreeBusy Data" {$FHash[$mbMailbox].FreeBusyCount = $FHash[$mbMailbox].FreeBusyCount + 1}
default {$Fhash[$mbMailbox].OtherCount = $Fhash[$mbMailbox].OtherCount + 1}
}
}
else{
$coCustObj = "" | select MailboxName,InboxCount,CalendarCount,FreeBusyCount,OtherCount
$coCustObj.MailboxName = $mbMailbox
$coCustObj.InboxCount = 0
$coCustObj.CalendarCount = 0
$coCustObj.FreeBusyCount = 0
$coCustObj.OtherCount = 0
$Fhash.add($mbMailbox,$coCustObj)
switch($folderName){
"Inbox" {$Fhash[$mbMailbox].InboxCount = $Fhash[$mbMailbox].InboxCount + 1}
"Calendar" {$Fhash[$mbMailbox].CalendarCount = $Fhash[$mbMailbox].CalendarCount + 1}
"FreeBusy Data" {$FHash[$mbMailbox].FreeBusyCount = $FHash[$mbMailbox].FreeBusyCount + 1}
default {$Fhash[$mbMailbox].OtherCount = $Fhash[$mbMailbox].OtherCount + 1}
}
}
if($RHash.containskey($AccessingUser)){
switch($exAuditObject.FolderName.ToString()){
"Inbox" {$RHash[$AccessingUser].InboxCount = $RHash[$AccessingUser].InboxCount + 1}
"Calendar" {$RHash[$AccessingUser].CalendarCount = $RHash[$AccessingUser].CalendarCount + 1}
"FreeBusy Data" {$RHash[$AccessingUser].FreeBusyCount = $RHash[$AccessingUser].FreeBusyCount + 1}
default {$RHash[$AccessingUser].OtherCount = $RHash[$AccessingUser].OtherCount + 1}
}
}
else{
$coCustObj = "" | select MailboxName,InboxCount,FreeBusyCount,CalendarCount,OtherCount
$coCustObj.MailboxName = $AccessingUser
$coCustObj.InboxCount = 0
$coCustObj.CalendarCount = 0
$coCustObj.FreeBusyCount = 0
$coCustObj.OtherCount = 0
$Rhash.add($AccessingUser,$coCustObj)
switch($exAuditObject.FolderName.ToString()){
"Inbox" {$RHash[$AccessingUser].InboxCount = $RHash[$AccessingUser].InboxCount + 1}
"Calendar" {$RHash[$AccessingUser].CalendarCount = $RHash[$AccessingUser].CalendarCount + 1}
"FreeBusy Data" {$RHash[$AccessingUser].FreeBusyCount = $RHash[$AccessingUser].FreeBusyCount + 1}
default {$RHash[$AccessingUser].OtherCount = $RHash[$AccessingUser].OtherCount + 1}
}
}
}
}
}
foreach($key in $Fhash.keys){
$f1Table.rows.add($UserDNHash[$Fhash[$key].MailboxName].DisplayName,$Fhash[$key].InboxCount,
$Fhash[$key].CalendarCount,$Fhash[$key].FreeBusyCount,$Fhash[$key].OtherCount)
}
foreach($key in $Rhash.keys){
$f2Table.rows.add($UserDNHash[$Rhash[$key].MailboxName].DisplayName,$Rhash[$key].InboxCount,
$Rhash[$key].CalendarCount,$Rhash[$key].FreeBusyCount,$Rhash[$key].OtherCount)
}
if ($GnGroupbyDrop.SelectedItem -eq "UserName Used"){$dgDataGrid.datasource = $f2Table}
else{$dgDataGrid.datasource = $f1Table}
}
function exportTable{
$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.Showhelp = $true
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("MailboxName,InboxCount,FreeBusyCount,CalendarCount,OtherCount")
$table = $dgDataGrid.datasource
foreach($row in $table.Rows){
$logfile.WriteLine("`"" + $row[0].ToString() + "`"," + $row[1].ToString() + "," + $row[2].ToString() + "," + $row[3].ToString() + "," + $row[4].ToString())
}
$logfile.Close()
}
}
function exportDetail{
$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.Showhelp = $true
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("RecordID,TimeCreated,FolderPath,FolderName,Mailbox,
AccessingUser,MailboxLegacyExchangeDN,AccessingUserLegacyExchangeDN,MachineName,
Address,ProcessName,ApplicationId")
$table = $dgDataGrid1.datasource
foreach($row in $table.Rows){
$logfile.WriteLine("`"" + $row[0].ToString() + "`"," + $row[1].ToString() + "," + $row[2].ToString() + "," + $row[3].ToString() + "," + $row[4].ToString() + "," + $row[5].ToString() + "," + $row[6].ToString()+ "," + $row[7].ToString() + "," + $row[8].ToString() + "," + $row[9].ToString())
}
$logfile.Close()
}
}
$form.Text = "Exchange 2007 Mailbox Folder Access Audit Form"
$form.size = new-object System.Drawing.Size(1000,800)
$form.autoscroll = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()
The good thing is with a little bit of powershell, a bit of imagination and some time you can bash together something usefull that will extract that information from the logs, parse (or if your using 2008 xpath). Then group that information into a more usable form and present it back into a Winform so you can interact with it.
I've put together actually two scripts to do this the main difference between these scripts is the method they use to access the log. The first script uses WMI to access the Exchange Audit logs (a couple of weeks okay i said this wouldn't work which i was completly wrong about the issue i was having is that you need to have at least view only Exchange Admin to to query these logs). The second script uses the .NET 3.5 class and Xpath to access the logs but this will only work on platforms that support these new classes eg Vista,7,2008.
This script uses a combination of Exchange Management Shell cmdlets get-mailbox and get-mailuser to get the displayname information about all users and then store these in hashtable which can then be used when referencing information that is retrieved from the event logs. It then retrieves the eventlogs for the defined period and server you select via the GUI and then summerises the number of accesses to each folder and displays them in one grid. You can then select the mailox or useraccount used and get the detail on the raw logs. There is also the ability to export each of the grid to a CSV file from the buttons at the bottom of the GUI.
These two scripts are very beta-ish at the moment because of the amount of data that can build up in these logs depending on your log settings. This means when you query and group this data it can be a big challenge without a proper database what I've found is trying to group more then an hours worth or information can be a bit of issue. I'm also not really decided on the how to best group the data that is coming back I'm leaning more towards a summarised approach but there is always version 2 for that.
I've put a download of the code for the two scripts here the script itself looks like
[System.Reflection.Assembly]::LoadWithPartialName("System.Core")
$UserDNHash = @{ }
$FHash = @{ }
$RHash = @{ }
function FillDnHash(){
$mbServers = get-mailboxserver
$mbServers | foreach-object{
get-mailbox -server $_.Name -ResultSize Unlimited | foreach-object{
$UserDNHash.Add($_.LegacyExchangeDN.ToString(),$_)
}
}
get-mailuser -ResultSize Unlimited | foreach-object{
$UserDNHash.Add($_.LegacyExchangeDN.ToString(),$_)
}
}
FillDnHash
function showdetail(){
if ($GnGroupbyDrop.SelectedItem -eq "UserName Used"){
$rows = $fsTable.Select("AccessedBy = '" + $f2Table.DefaultView[$dgDataGrid.CurrentCell.RowIndex][0] + "'")}
else{
$rows = $fsTable.Select("Mailbox = '" + $f1Table.DefaultView[$dgDataGrid.CurrentCell.RowIndex][0] + "'")}
$frTable.clear()
foreach ($row in $rows){
$frTable.rows.add($row[0].ToString(),$row[1].ToString(),$row[2].ToString(),$row[3].ToString()
,$row[4].ToString(),$row[5].ToString(),$row[6].ToString(),$row[7].ToString(),$row[8].ToString(),
$row[9].ToString())
}
$dgDataGrid1.datasource = $frTable
}
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
$form = new-object System.Windows.Forms.form
# Add DataTable
$Dataset = New-Object System.Data.DataSet
$fsTable = New-Object System.Data.DataTable
$f1Table = New-Object System.Data.DataTable
$f2Table = New-Object System.Data.DataTable
$frTable = New-Object System.Data.DataTable
$f1Table.TableName = "Folder Access Forward"
$f1Table.Columns.Add("Mailbox")
$f1Table.Columns.Add("Inbox #")
$f1Table.Columns.Add("Calendar #")
$f1Table.Columns.Add("FreeBusy #")
$f1Table.Columns.Add("Other #")
$Dataset.tables.add($f1Table)
$f2Table.TableName = "Folder Access Reverse"
$f2Table.Columns.Add("User")
$f2Table.Columns.Add("Inbox #")
$f2Table.Columns.Add("Calendar #")
$f2Table.Columns.Add("FreeBusy #")
$f2Table.Columns.Add("Other #")
$Dataset.tables.add($f2Table)
$fsTable.TableName = "Folder Access Detail"
$fsTable.Columns.Add("RecordID")
$fsTable.Columns.Add("DateTime",[DATETIME])
$fsTable.Columns.Add("Mailbox")
$fsTable.Columns.Add("FolderName")
$fstable.Columns.Add("FolderPath")
$fsTable.Columns.Add("AccessedBy")
$fsTable.Columns.Add("IPAddress")
$fsTable.Columns.Add("MachineName")
$fsTable.Columns.Add("ProgramName")
$fsTable.Columns.Add("ApplicationID")
$Dataset.tables.add($fsTable)
$frtable.TableName = "Folder Access Detail Show"
$frTable.Columns.Add("RecordID")
$frtable.Columns.Add("DateTime",[DATETIME])
$frtable.Columns.Add("Mailbox")
$frtable.Columns.Add("FolderName")
$frtable.Columns.Add("FolderPath")
$frtable.Columns.Add("AccessedBy")
$frtable.Columns.Add("IPAddress")
$frtable.Columns.Add("MachineName")
$frtable.Columns.Add("ProgramName")
$frtable.Columns.Add("ApplicationID")
$Dataset.tables.add($frtable)
$sdetailButton = new-object System.Windows.Forms.Button
$sdetailButton.Location = new-object System.Drawing.Size(550,19)
$sdetailButton.Size = new-object System.Drawing.Size(120,23)
$sdetailButton.Text = "Show Folder Detail"
$sdetailButton.visible = $True
$sdetailButton.Add_Click({showDetail})
$form.Controls.Add($sdetailButton)
$gllogButton = new-object System.Windows.Forms.Button
$gllogButton.Location = new-object System.Drawing.Size(440,19)
$gllogButton.Size = new-object System.Drawing.Size(90,23)
$gllogButton.Text = "Get Logs"
$gllogButton.visible = $True
$gllogButton.Add_Click({getLogs})
$form.Controls.Add($gllogButton)
# Add Server DropLable
$snServerNamelableBox = new-object System.Windows.Forms.Label
$snServerNamelableBox.Location = new-object System.Drawing.Size(10,20)
$snServerNamelableBox.size = new-object System.Drawing.Size(80,20)
$snServerNamelableBox.Text = "ServerName"
$form.Controls.Add($snServerNamelableBox)
# Add Server Drop Down
$snServerNameDrop = new-object System.Windows.Forms.ComboBox
$snServerNameDrop.Location = new-object System.Drawing.Size(90,20)
$snServerNameDrop.Size = new-object System.Drawing.Size(100,30)
get-mailboxserver | ForEach-Object{$snServerNameDrop.Items.Add($_.Name)}
$form.Controls.Add($snServerNameDrop)
# Add DateTimePickers Button
$dpDatePickerFromlableBox = new-object System.Windows.Forms.Label
$dpDatePickerFromlableBox.Location = new-object System.Drawing.Size(10,50)
$dpDatePickerFromlableBox.size = new-object System.Drawing.Size(90,20)
$dpDatePickerFromlableBox.Text = "Logged Between"
$form.Controls.Add($dpDatePickerFromlableBox)
$dpTimeFrom = new-object System.Windows.Forms.DateTimePicker
$dpTimeFrom.Location = new-object System.Drawing.Size(110,50)
$dpTimeFrom.Size = new-object System.Drawing.Size(190,20)
$form.Controls.Add($dpTimeFrom)
$dpDatePickerFromlableBox1 = new-object System.Windows.Forms.Label
$dpDatePickerFromlableBox1.Location = new-object System.Drawing.Size(10,70)
$dpDatePickerFromlableBox1.size = new-object System.Drawing.Size(50,20)
$dpDatePickerFromlableBox1.Text = "and"
$form.Controls.Add($dpDatePickerFromlableBox1)
$dpTimeFrom1 = new-object System.Windows.Forms.DateTimePicker
$dpTimeFrom1.Location = new-object System.Drawing.Size(110,70)
$dpTimeFrom1.Size = new-object System.Drawing.Size(190,20)
$form.Controls.Add($dpTimeFrom1)
$dpTimeFrom2 = new-object System.Windows.Forms.DateTimePicker
$dpTimeFrom2.Format = "Time"
$dpTimeFrom2.value = [DateTime]::get_Now().AddHours(-1)
$dpTimeFrom2.ShowUpDown = $True
$dpTimeFrom2.Location = new-object System.Drawing.Size(310,50)
$dpTimeFrom2.Size = new-object System.Drawing.Size(190,20)
$form.Controls.Add($dpTimeFrom2)
$dpTimeFrom3 = new-object System.Windows.Forms.DateTimePicker
$dpTimeFrom3.Format = "Time"
$dpTimeFrom3.ShowUpDown = $True
$dpTimeFrom3.Location = new-object System.Drawing.Size(310,70)
$dpTimeFrom3.Size = new-object System.Drawing.Size(190,20)
$form.Controls.Add($dpTimeFrom3)
# Add DropLable
$GnGroupbylableBox = new-object System.Windows.Forms.Label
$GnGroupbylableBox.Location = new-object System.Drawing.Size(200,20)
$GnGroupbylableBox.size = new-object System.Drawing.Size(100,20)
$GnGroupbylableBox.Text = "Group Results By"
$form.Controls.Add($GnGroupbylableBox)
$GnGroupbyDrop = new-object System.Windows.Forms.ComboBox
$GnGroupbyDrop.Location = new-object System.Drawing.Size(310,20)
$GnGroupbyDrop.Size = new-object System.Drawing.Size(110,30)
$GnGroupbyDrop.Items.Add("Mailbox Accessed")
$GnGroupbyDrop.Items.Add("UserName Used")
$GnGroupbyDrop.Add_SelectedValueChanged({if ($GnGroupbyDrop.SelectedItem -eq "UserName Used"){$dgDataGrid.datasource = $f2Table}
else{$dgDataGrid.datasource = $f1Table}
})
$form.Controls.Add($GnGroupbyDrop)
# Add Export 1st Button
$exButton1 = new-object System.Windows.Forms.Button
$exButton1.Location = new-object System.Drawing.Size(10,620)
$exButton1.Size = new-object System.Drawing.Size(125,20)
$exButton1.Text = "Export Summary Grid"
$exButton1.Add_Click({exportTable})
$form.Controls.Add($exButton1)
# Add Export 2sn Button
$exButton2 = new-object System.Windows.Forms.Button
$exButton2.Location = new-object System.Drawing.Size(550,620)
$exButton2.Size = new-object System.Drawing.Size(135,20)
$exButton2.Text = "Export Details Grid"
$exButton2.Add_Click({exportDetail})
$form.Controls.Add($exButton2)
# Add DataGrid View
$dgDataGrid = new-object System.windows.forms.DataGridView
$dgDataGrid.Location = new-object System.Drawing.Size(10,100)
$dgDataGrid.size = new-object System.Drawing.Size(530,500)
$dgDataGrid.AutoSizeRowsMode = "AllHeaders"
$form.Controls.Add($dgDataGrid)
$dgDataGrid1 = new-object System.windows.forms.DataGridView
$dgDataGrid1.Location = new-object System.Drawing.Size(550,100)
$dgDataGrid1.size = new-object System.Drawing.Size(450,500)
$dgDataGrid1.AutoSizeRowsMode = "AllHeaders"
$form.Controls.Add($dgDataGrid1)
function getlogs{
$DateTo = New-Object System.DateTime $dpTimeFrom.value.year,$dpTimeFrom.value.month,$dpTimeFrom.value.day,$dpTimeFrom2.value.hour
,$dpTimeFrom2.value.minute,$dpTimeFrom2.value.second
$DateFrom = New-Object System.DateTime $dpTimeFrom1.value.year,$dpTimeFrom1.value.month,$dpTimeFrom1.value.day,
$dpTimeFrom3.value.hour,$dpTimeFrom3.value.minute,$dpTimeFrom3.value.second
$DateFromTS = New-TimeSpan -Start $DateFrom -End ([System.DateTime]::Now)
$DateToTS = New-TimeSpan -Start $DateTo -End ([System.DateTime]::Now)
$f1Table.Clear()
$f2Table.Clear()
$fsTable.Clear()
$elLogQuery = "
$eqEventLogQuery = new-object System.Diagnostics.Eventing.Reader.EventLogQuery("Exchange Auditing", [System.Diagnostics.Eventing.Reader.PathType]::LogName, $elLogQuery);
$eqEventLogQuery.Session = new-object System.Diagnostics.Eventing.Reader.EventLogSession($snServerNameDrop.SelectedItem)
$lrEventLogReader = new-object System.Diagnostics.Eventing.Reader.EventLogReader($eqEventLogQuery)
for($eventInstance = $lrEventLogReader.ReadEvent();$eventInstance -ne $null; $eventInstance = $lrEventLogReader.ReadEvent()){
[System.Diagnostics.Eventing.Reader.EventLogRecord]$erEventRecord = [System.Diagnostics.Eventing.Reader.EventLogRecord]$eventInstance
if($erEventRecord.Properties[5].Value -match "
$exAuditObject = "" | select RecordID,TimeCreated,FolderPath,FolderName,Mailbox,AccessingUser,MailboxLegacyExchangeDN,AccessingUserLegacyExchangeDN
,MachineName,Address,ProcessName,ApplicationId
$exAuditObject.RecordID = $erEventRecord.RecordID
$exAuditObject.TimeCreated = $erEventRecord.TimeCreated.ToString("yyyy-MM-dd HH:mm:ss")
$exAuditObject.FolderPath = $erEventRecord.Properties[0].Value.ToString()
$exAuditObject.FolderName = $erEventRecord.Properties[1].Value.ToString()
$exAuditObject.Mailbox = $erEventRecord.Properties[2].Value.ToString()
$mbMailbox = $erEventRecord.Properties[5].Value.ToString()
$exAuditObject.AccessingUser = $erEventRecord.Properties[3].Value.ToString()
$AccessingUser = $erEventRecord.Properties[4].Value.ToString()
$exAuditObject.AccessingUserLegacyExchangeDN = $erEventRecord.Properties[4].Value.ToString()
$exAuditObject.MailboxLegacyExchangeDN = $erEventRecord.Properties[5].Value.ToString()
$exAuditObject.MachineName = $erEventRecord.Properties[8].Value.ToString()
$exAuditObject.Address = $erEventRecord.Properties[9].Value.ToString()
$exAuditObject.ProcessName = $erEventRecord.Properties[10].Value.ToString()
$exAuditObject.ApplicationId = $erEventRecord.Properties[12].Value.ToString()
if ($exAuditObject.MailboxLegacyExchangeDN.ToString() -ne $exAuditObject.AccessingUserLegacyExchangeDN.ToString()){
$fsTable.Rows.add($exAuditObject.RecordID,$exAuditObject.TimeCreated.ToString(),
$UserDNHash[$mbMailbox].DisplayName,$exAuditObject.FolderName.ToString(),
$exAuditObject.FolderPath.ToString(),$UserDNHash[$AccessingUser].DisplayName,$exAuditObject.Address.ToString()
,$exAuditObject.MachineName.ToString(),$exAuditObject.ProcessName.ToString(),$exAuditObject.ApplicationId.ToString())
if($Fhash.containskey($mbMailbox)){
switch($exAuditObject.FolderName.ToString()){
"Inbox" {$Fhash[$mbMailbox].InboxCount = $Fhash[$mbMailbox].InboxCount + 1}
"Calendar" {$Fhash[$mbMailbox].CalendarCount = $Fhash[$mbMailbox].CalendarCount + 1}
"FreeBusy Data" {$FHash[$mbMailbox].FreeBusyCount = $FHash[$mbMailbox].FreeBusyCount + 1}
default {$Fhash[$mbMailbox].OtherCount = $Fhash[$mbMailbox].OtherCount + 1}
}
}
else{
$coCustObj = "" | select MailboxName,InboxCount,CalendarCount,FreeBusyCount,OtherCount
$coCustObj.MailboxName = $mbMailbox
$coCustObj.InboxCount = 0
$coCustObj.CalendarCount = 0
$coCustObj.FreeBusyCount = 0
$coCustObj.OtherCount = 0
$Fhash.add($mbMailbox,$coCustObj)
switch($folderName){
"Inbox" {$Fhash[$mbMailbox].InboxCount = $Fhash[$mbMailbox].InboxCount + 1}
"Calendar" {$Fhash[$mbMailbox].CalendarCount = $Fhash[$mbMailbox].CalendarCount + 1}
"FreeBusy Data" {$FHash[$mbMailbox].FreeBusyCount = $FHash[$mbMailbox].FreeBusyCount + 1}
default {$Fhash[$mbMailbox].OtherCount = $Fhash[$mbMailbox].OtherCount + 1}
}
}
if($RHash.containskey($AccessingUser)){
switch($exAuditObject.FolderName.ToString()){
"Inbox" {$RHash[$AccessingUser].InboxCount = $RHash[$AccessingUser].InboxCount + 1}
"Calendar" {$RHash[$AccessingUser].CalendarCount = $RHash[$AccessingUser].CalendarCount + 1}
"FreeBusy Data" {$RHash[$AccessingUser].FreeBusyCount = $RHash[$AccessingUser].FreeBusyCount + 1}
default {$RHash[$AccessingUser].OtherCount = $RHash[$AccessingUser].OtherCount + 1}
}
}
else{
$coCustObj = "" | select MailboxName,InboxCount,FreeBusyCount,CalendarCount,OtherCount
$coCustObj.MailboxName = $AccessingUser
$coCustObj.InboxCount = 0
$coCustObj.CalendarCount = 0
$coCustObj.FreeBusyCount = 0
$coCustObj.OtherCount = 0
$Rhash.add($AccessingUser,$coCustObj)
switch($exAuditObject.FolderName.ToString()){
"Inbox" {$RHash[$AccessingUser].InboxCount = $RHash[$AccessingUser].InboxCount + 1}
"Calendar" {$RHash[$AccessingUser].CalendarCount = $RHash[$AccessingUser].CalendarCount + 1}
"FreeBusy Data" {$RHash[$AccessingUser].FreeBusyCount = $RHash[$AccessingUser].FreeBusyCount + 1}
default {$RHash[$AccessingUser].OtherCount = $RHash[$AccessingUser].OtherCount + 1}
}
}
}
}
}
foreach($key in $Fhash.keys){
$f1Table.rows.add($UserDNHash[$Fhash[$key].MailboxName].DisplayName,$Fhash[$key].InboxCount,
$Fhash[$key].CalendarCount,$Fhash[$key].FreeBusyCount,$Fhash[$key].OtherCount)
}
foreach($key in $Rhash.keys){
$f2Table.rows.add($UserDNHash[$Rhash[$key].MailboxName].DisplayName,$Rhash[$key].InboxCount,
$Rhash[$key].CalendarCount,$Rhash[$key].FreeBusyCount,$Rhash[$key].OtherCount)
}
if ($GnGroupbyDrop.SelectedItem -eq "UserName Used"){$dgDataGrid.datasource = $f2Table}
else{$dgDataGrid.datasource = $f1Table}
}
function exportTable{
$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.Showhelp = $true
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("MailboxName,InboxCount,FreeBusyCount,CalendarCount,OtherCount")
$table = $dgDataGrid.datasource
foreach($row in $table.Rows){
$logfile.WriteLine("`"" + $row[0].ToString() + "`"," + $row[1].ToString() + "," + $row[2].ToString() + "," + $row[3].ToString() + "," + $row[4].ToString())
}
$logfile.Close()
}
}
function exportDetail{
$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.Showhelp = $true
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("RecordID,TimeCreated,FolderPath,FolderName,Mailbox,
AccessingUser,MailboxLegacyExchangeDN,AccessingUserLegacyExchangeDN,MachineName,
Address,ProcessName,ApplicationId")
$table = $dgDataGrid1.datasource
foreach($row in $table.Rows){
$logfile.WriteLine("`"" + $row[0].ToString() + "`"," + $row[1].ToString() + "," + $row[2].ToString() + "," + $row[3].ToString() + "," + $row[4].ToString() + "," + $row[5].ToString() + "," + $row[6].ToString()+ "," + $row[7].ToString() + "," + $row[8].ToString() + "," + $row[9].ToString())
}
$logfile.Close()
}
}
$form.Text = "Exchange 2007 Mailbox Folder Access Audit Form"
$form.size = new-object System.Drawing.Size(1000,800)
$form.autoscroll = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()
Showing information about a Exchange 2003 user in Powershell
Haven't posted any 2003 scripts for a while so here's one that might be useful especially if your looking at doing a bit of documentation before a migration. There are a few tricks when your using ADSI scripts to get information eg if you want to get the OU check the parent object of the userObject. Or if you want to get the Storage Group use the Parent of the MailStore object. If you want to work out quotas check both the mailstore and the user object. To put this all together into a script that takes the username as a parameter it looks like this I've put a download of this code here.
param([String] $samaccountname)
$root = [ADSI]'LDAP://RootDSE'
$dfDefaultRootPath = "LDAP://" + $root.DefaultNamingContext.tostring()
$dfRoot = [ADSI]$dfDefaultRootPath
$gfGALQueryFilter = "(&(&(&(& (mailnickname=*)(objectCategory=person)(objectClass=user)(samaccountname=" + $samaccountname + ")))))"
$dfsearcher = new-object System.DirectoryServices.DirectorySearcher($dfRoot)
$dfsearcher.Filter = $gfGALQueryFilter
$srSearchResult = $dfsearcher.FindOne()
if ($srSearchResult -ne $null){
$uoUserobject = $srSearchResult.GetDirectoryEntry()
$msStore = [ADSI]("LDAP://" + $uoUserobject.homemdb)
$soServer = [ADSI]("LDAP://" + $msStore.msExchOwningServer)
$sgStorageGroup = $msStore.psbase.Parent
$ffEdbFileFilter = "name='" + $msStore.msExchEDBFile.ToString().replace("\","\\") + "'"
$ffStmFileFilter = "name='" + $msStore.msExchSLVFile.ToString().replace("\","\\") + "'"
$mbEdbSize =get-wmiobject CIM_Datafile -filter $ffEdbFileFilter -ComputerName $soServer.Name
$mbStmSize =get-wmiobject CIM_Datafile -filter $ffStmFileFilter -ComputerName $soServer.Name
[int64]$csCombinedSize = [double]$mbEdbSize.FileSize + [int64]$mbStmSize.FileSize
$msFilter = "LegacyDN='" + $uoUserobject.legacyExchangeDN + "'"
$mbsize = get-wmiobject -class Exchange_Mailbox -Namespace ROOT\MicrosoftExchangev2 -filter $msFilter -ComputerName $soServer.Name
$divval = ($csCombinedSize/1024)/100
$pcStore = ($mbsize.size/$divval)/100
"User DisplayName : " + $uoUserobject.displayName
"OU DisplayName : " + $uoUserobject.psbase.Parent.Name
"OU DistinguishedName : " + $uoUserobject.psbase.Parent.DistinguishedName
"Mail Server : " + $soServer.Name
"Exchange Version : " + $soServer.SerialNumber
"Mailbox Store : " + $msStore.Name
"Storage Group : " + $sgStorageGroup.Name
"MailStore Size : " + "{0:#.00}" -f ($csCombinedSize/1GB) + " GB"
"Mailbox Size : " + "{0:#.00}" -f ($mbsize.Size/1KB) + " MB"
"Percentage of Store Used by User : " + "{0:P1}" -f $pcStore
""
"Quotas"
""
"User Quotas"
""
"mDBStorageQuota : " + "{0:#.00}" -f ($uoUserobject.mDBStorageQuota.Value/1KB) + " MB"
"mDBOverQuotaLimit : " + "{0:#.00}" -f ($uoUserobject.mDBOverQuotaLimit.Value/1KB) + " MB"
"mDBOverHardQuotaLimit : " + "{0:#.00}" -f ($uoUserobject.mDBOverHardQuotaLimit.Value/1KB) + " MB"
""
"Store Quotas "
"mDBStorageQuota : " + "{0:#.00}" -f ($msStore.mDBStorageQuota.Value/1KB) + " MB"
"mDBOverQuotaLimit : " + "{0:#.00}" -f ($msStore.mDBOverQuotaLimit.Value/1KB) + " MB"
"mDBOverHardQuotaLimit : " + "{0:#.00}" -f ($msStore.mDBOverHardQuotaLimit.Value/1KB) + " MB"
}
param([String] $samaccountname)
$root = [ADSI]'LDAP://RootDSE'
$dfDefaultRootPath = "LDAP://" + $root.DefaultNamingContext.tostring()
$dfRoot = [ADSI]$dfDefaultRootPath
$gfGALQueryFilter = "(&(&(&(& (mailnickname=*)(objectCategory=person)(objectClass=user)(samaccountname=" + $samaccountname + ")))))"
$dfsearcher = new-object System.DirectoryServices.DirectorySearcher($dfRoot)
$dfsearcher.Filter = $gfGALQueryFilter
$srSearchResult = $dfsearcher.FindOne()
if ($srSearchResult -ne $null){
$uoUserobject = $srSearchResult.GetDirectoryEntry()
$msStore = [ADSI]("LDAP://" + $uoUserobject.homemdb)
$soServer = [ADSI]("LDAP://" + $msStore.msExchOwningServer)
$sgStorageGroup = $msStore.psbase.Parent
$ffEdbFileFilter = "name='" + $msStore.msExchEDBFile.ToString().replace("\","\\") + "'"
$ffStmFileFilter = "name='" + $msStore.msExchSLVFile.ToString().replace("\","\\") + "'"
$mbEdbSize =get-wmiobject CIM_Datafile -filter $ffEdbFileFilter -ComputerName $soServer.Name
$mbStmSize =get-wmiobject CIM_Datafile -filter $ffStmFileFilter -ComputerName $soServer.Name
[int64]$csCombinedSize = [double]$mbEdbSize.FileSize + [int64]$mbStmSize.FileSize
$msFilter = "LegacyDN='" + $uoUserobject.legacyExchangeDN + "'"
$mbsize = get-wmiobject -class Exchange_Mailbox -Namespace ROOT\MicrosoftExchangev2 -filter $msFilter -ComputerName $soServer.Name
$divval = ($csCombinedSize/1024)/100
$pcStore = ($mbsize.size/$divval)/100
"User DisplayName : " + $uoUserobject.displayName
"OU DisplayName : " + $uoUserobject.psbase.Parent.Name
"OU DistinguishedName : " + $uoUserobject.psbase.Parent.DistinguishedName
"Mail Server : " + $soServer.Name
"Exchange Version : " + $soServer.SerialNumber
"Mailbox Store : " + $msStore.Name
"Storage Group : " + $sgStorageGroup.Name
"MailStore Size : " + "{0:#.00}" -f ($csCombinedSize/1GB) + " GB"
"Mailbox Size : " + "{0:#.00}" -f ($mbsize.Size/1KB) + " MB"
"Percentage of Store Used by User : " + "{0:P1}" -f $pcStore
""
"Quotas"
""
"User Quotas"
""
"mDBStorageQuota : " + "{0:#.00}" -f ($uoUserobject.mDBStorageQuota.Value/1KB) + " MB"
"mDBOverQuotaLimit : " + "{0:#.00}" -f ($uoUserobject.mDBOverQuotaLimit.Value/1KB) + " MB"
"mDBOverHardQuotaLimit : " + "{0:#.00}" -f ($uoUserobject.mDBOverHardQuotaLimit.Value/1KB) + " MB"
""
"Store Quotas "
"mDBStorageQuota : " + "{0:#.00}" -f ($msStore.mDBStorageQuota.Value/1KB) + " MB"
"mDBOverQuotaLimit : " + "{0:#.00}" -f ($msStore.mDBOverQuotaLimit.Value/1KB) + " MB"
"mDBOverHardQuotaLimit : " + "{0:#.00}" -f ($msStore.mDBOverHardQuotaLimit.Value/1KB) + " MB"
}
Sunday, September 13, 2009
Getting the Exchange Auditing event logs programmatically
With the release of Exchange 2007 SP2 some great new auditing options are now possible to let you see how and when rights are being used within your exchange environment. More importantly in a serious situation you could use these features to provide some forensic auditing of what was being accessed in your exchange store when some suspicious activity may have happened. The first place to start if your looking at auditing in Exchange 2007 SP2 is to read the very detailed white paper that has been published http://technet.microsoft.com/en-us/library/ee331009.aspx .
In this post I'm going to look at the options for reading the new Exchange Auditing event logs where folder access information is written. The Exchange Auditing logs themselves if your using Windows 2008 use the new evtx event log framework and are stored under the Application and Services Logs group
With the new .net 3.5 System.Diagnostics.Eventing.Reader class now allows you to easily parse back the information store in the event log. But before looking at this lets look at a more convention approach using Get-Eventlog can be used on both windows 2003 and 2008. Using and then you can do the text from the event log message which you can then parse out. To do a conventional parse you can split it by the Newline character and then split by semicollumn. eg
if($_.Timewritten -gt [System.DateTime]::Now.addhours(-24)){
$arry = $_.Message.ToString().Split("`n")
$mbMailbox = ""
$folderName = ""
$AccessingUser = ""
$processname = ""
$machinename = ""
$ApplicationId = ""
$Address = ""
foreach($line in $arry){
if ($line -match "Mailbox:"){
$laLineArray = $line.Split(":")
if ($laLineArray[1] -match "" -eq $false){
$mbMailbox = $laLineArray[1].Substring(1,($laLineArray[1].length-3))
}
}
if ($line -match "Display Name:"){
$laLineArray = $line.Split(":")
$folderName = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
if ($line -match "Accessing User:"){
$laLineArray = $line.Split(":")
$AccessingUser = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
if ($line -match "Process Name:"){
$laLineArray = $line.Split(":")
$processname = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
if ($line -match "Machine Name:"){
$laLineArray = $line.Split(":")
$machinename = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
if ($line -match "Application Id:"){
$laLineArray = $line.Split(":")
$ApplicationId = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
if ($line -match "Address:"){
$laLineArray = $line.Split(":")
$Address = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
}
}
Its a little messy like any string parse but it does the job, But whats more interesting is if you have Vista and or Windows 2008 you can start using the new eventing classes. Powershell V2 introduces the Get-WinEvent cmdlet which is a nice wrapper around these classes but as V2 isn't out yet and the underlying classes are pretty simple to use anyway i prefer the more direct method. One of the advantages of using thesse new classes is that you can do away with having to parse text out of the message fields as this information is already stored in a structure format you can access using the Eventing.Reader.EventLogRecord properties collection the order or parameters are documented in the Whitepaper i mentioned earlier.
One of the challenges of using the Eventing.Reader class is that you need to work out what the correct XPATH query will be for the data you want to retrieve from the event logs. XPath for those uninitiated is a standard used for querying XML data the event logs only supports Version 1 of XPath. This limitation affects you when you want to query for dattime within a certain timeframe which is something in the real world you generally do a lot. With Version 1 of Xpath you can only do numeric comparisons so when creating a restriction around a timespan one method you can to use the timediff function which gives you the differance between the Time of the log entry and the current time in milliseconds. So mathamatically with a little code you can query a TimeSpan using TimeSpan in Powershell.
So to put this all together into a script that will allow you to query the Exchange Auditing EventLog for events of type 10100 and where the event relates to one user accessing another users mailbox (you need to check if mailboxExchangeLegacyDN is set). This script uses a custom object to store the result and then exports the result to a csv file.
The following three varibles need to be set first before you run the code the first in the servername of the server to query the log. The next two varbile set the time From (Lastest) to the To (earliest) entry you want to retrieve.
$ServerName = "servername"
$DateFrom = [System.DateTime]::Now.addDays(-2)
$DateTo = [System.DateTime]::Now.addDays(-3)
I've put a download of this script here the script looks like
[System.Reflection.Assembly]::LoadWithPartialName("System.Core")
$ServerName = "servername"
$DateFrom = [System.DateTime]::Now.addDays(-2)
$DateTo = [System.DateTime]::Now.addDays(-3)
$DateFromTS = New-TimeSpan -Start $DateFrom -End ([System.DateTime]::Now)
$DateToTS = New-TimeSpan -Start $DateTo -End ([System.DateTime]::Now)
$eacombCollection = @()
$elLogQuery = "<QueryList><Query Id=`"0`" Path=`"Security`"><Select Path=`"Exchange Auditing`">*[System[(EventID=10100) and TimeCreated[timediff(@SystemTime) <=" + $DateToTS.TotalMilliseconds +"] and TimeCreated[timediff(@SystemTime) >= " + $DateFromTS.TotalMilliseconds +"]]]</Select></Query></QueryList>"
$eqEventLogQuery = new-object System.Diagnostics.Eventing.Reader.EventLogQuery("Exchange Auditing", [System.Diagnostics.Eventing.Reader.PathType]::LogName, $elLogQuery);
$eqEventLogQuery.Session = new-object System.Diagnostics.Eventing.Reader.EventLogSession($ServerName);
$lrEventLogReader = new-object System.Diagnostics.Eventing.Reader.EventLogReader($eqEventLogQuery)
for($eventInstance = $lrEventLogReader.ReadEvent();$eventInstance -ne $null; $eventInstance = $lrEventLogReader.ReadEvent()){
[System.Diagnostics.Eventing.Reader.EventLogRecord]$erEventRecord = [System.Diagnostics.Eventing.Reader.EventLogRecord]$eventInstance
if($erEventRecord.Properties[5].Value -match "<NULL>" -eq $false){
$exAuditObject = "" | select RecordID,TimeCreated,FolderPath,FolderName,Mailbox,AccessingUser,
MailboxLegacyExchangeDN,AccessingUserLegacyExchangeDN,MachineName,Address,
ProcessName,ApplicationId
$exAuditObject.RecordID = $erEventRecord.RecordID
$exAuditObject.TimeCreated = $erEventRecord.TimeCreated
$exAuditObject.FolderPath = $erEventRecord.Properties[0].Value.ToString()
$exAuditObject.FolderName = $erEventRecord.Properties[1].Value.ToString()
$exAuditObject.Mailbox = $erEventRecord.Properties[2].Value.ToString()
$exAuditObject.AccessingUser = $erEventRecord.Properties[3].Value.ToString()
$exAuditObject.AccessingUserLegacyExchangeDN = $erEventRecord.Properties[4].Value.ToString()
$exAuditObject.MailboxLegacyExchangeDN = $erEventRecord.Properties[5].Value.ToString()
$exAuditObject.MachineName = $erEventRecord.Properties[8].Value.ToString()
$exAuditObject.Address = $erEventRecord.Properties[9].Value.ToString()
$exAuditObject.ProcessName = $erEventRecord.Properties[10].Value.ToString()
$exAuditObject.ApplicationId = $erEventRecord.Properties[12].Value.ToString()
$eacombCollection +=$exAuditObject
}
}
$eacombCollection | export-csv –encoding "unicode" -noTypeInformation "c:\temp\exauditOutput.csv"
In this post I'm going to look at the options for reading the new Exchange Auditing event logs where folder access information is written. The Exchange Auditing logs themselves if your using Windows 2008 use the new evtx event log framework and are stored under the Application and Services Logs group
With the new .net 3.5 System.Diagnostics.Eventing.Reader class now allows you to easily parse back the information store in the event log. But before looking at this lets look at a more convention approach using Get-Eventlog can be used on both windows 2003 and 2008. Using and then you can do the text from the event log message which you can then parse out. To do a conventional parse you can split it by the Newline character and then split by semicollumn. eg
if($_.Timewritten -gt [System.DateTime]::Now.addhours(-24)){
$arry = $_.Message.ToString().Split("`n")
$mbMailbox = ""
$folderName = ""
$AccessingUser = ""
$processname = ""
$machinename = ""
$ApplicationId = ""
$Address = ""
foreach($line in $arry){
if ($line -match "Mailbox:"){
$laLineArray = $line.Split(":")
if ($laLineArray[1] -match "
$mbMailbox = $laLineArray[1].Substring(1,($laLineArray[1].length-3))
}
}
if ($line -match "Display Name:"){
$laLineArray = $line.Split(":")
$folderName = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
if ($line -match "Accessing User:"){
$laLineArray = $line.Split(":")
$AccessingUser = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
if ($line -match "Process Name:"){
$laLineArray = $line.Split(":")
$processname = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
if ($line -match "Machine Name:"){
$laLineArray = $line.Split(":")
$machinename = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
if ($line -match "Application Id:"){
$laLineArray = $line.Split(":")
$ApplicationId = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
if ($line -match "Address:"){
$laLineArray = $line.Split(":")
$Address = $laLineArray[1].Substring(1,($laLineArray[1].length-2))
}
}
}
Its a little messy like any string parse but it does the job, But whats more interesting is if you have Vista and or Windows 2008 you can start using the new eventing classes. Powershell V2 introduces the Get-WinEvent cmdlet which is a nice wrapper around these classes but as V2 isn't out yet and the underlying classes are pretty simple to use anyway i prefer the more direct method. One of the advantages of using thesse new classes is that you can do away with having to parse text out of the message fields as this information is already stored in a structure format you can access using the Eventing.Reader.EventLogRecord properties collection the order or parameters are documented in the Whitepaper i mentioned earlier.
One of the challenges of using the Eventing.Reader class is that you need to work out what the correct XPATH query will be for the data you want to retrieve from the event logs. XPath for those uninitiated is a standard used for querying XML data the event logs only supports Version 1 of XPath. This limitation affects you when you want to query for dattime within a certain timeframe which is something in the real world you generally do a lot. With Version 1 of Xpath you can only do numeric comparisons so when creating a restriction around a timespan one method you can to use the timediff function which gives you the differance between the Time of the log entry and the current time in milliseconds. So mathamatically with a little code you can query a TimeSpan using TimeSpan in Powershell.
So to put this all together into a script that will allow you to query the Exchange Auditing EventLog for events of type 10100 and where the event relates to one user accessing another users mailbox (you need to check if mailboxExchangeLegacyDN is set). This script uses a custom object to store the result and then exports the result to a csv file.
The following three varibles need to be set first before you run the code the first in the servername of the server to query the log. The next two varbile set the time From (Lastest) to the To (earliest) entry you want to retrieve.
$ServerName = "servername"
$DateFrom = [System.DateTime]::Now.addDays(-2)
$DateTo = [System.DateTime]::Now.addDays(-3)
I've put a download of this script here the script looks like
[System.Reflection.Assembly]::LoadWithPartialName("System.Core")
$ServerName = "servername"
$DateFrom = [System.DateTime]::Now.addDays(-2)
$DateTo = [System.DateTime]::Now.addDays(-3)
$DateFromTS = New-TimeSpan -Start $DateFrom -End ([System.DateTime]::Now)
$DateToTS = New-TimeSpan -Start $DateTo -End ([System.DateTime]::Now)
$eacombCollection = @()
$elLogQuery = "<QueryList><Query Id=`"0`" Path=`"Security`"><Select Path=`"Exchange Auditing`">*[System[(EventID=10100) and TimeCreated[timediff(@SystemTime) <=" + $DateToTS.TotalMilliseconds +"] and TimeCreated[timediff(@SystemTime) >= " + $DateFromTS.TotalMilliseconds +"]]]</Select></Query></QueryList>"
$eqEventLogQuery = new-object System.Diagnostics.Eventing.Reader.EventLogQuery("Exchange Auditing", [System.Diagnostics.Eventing.Reader.PathType]::LogName, $elLogQuery);
$eqEventLogQuery.Session = new-object System.Diagnostics.Eventing.Reader.EventLogSession($ServerName);
$lrEventLogReader = new-object System.Diagnostics.Eventing.Reader.EventLogReader($eqEventLogQuery)
for($eventInstance = $lrEventLogReader.ReadEvent();$eventInstance -ne $null; $eventInstance = $lrEventLogReader.ReadEvent()){
[System.Diagnostics.Eventing.Reader.EventLogRecord]$erEventRecord = [System.Diagnostics.Eventing.Reader.EventLogRecord]$eventInstance
if($erEventRecord.Properties[5].Value -match "<NULL>" -eq $false){
$exAuditObject = "" | select RecordID,TimeCreated,FolderPath,FolderName,Mailbox,AccessingUser,
MailboxLegacyExchangeDN,AccessingUserLegacyExchangeDN,MachineName,Address,
ProcessName,ApplicationId
$exAuditObject.RecordID = $erEventRecord.RecordID
$exAuditObject.TimeCreated = $erEventRecord.TimeCreated
$exAuditObject.FolderPath = $erEventRecord.Properties[0].Value.ToString()
$exAuditObject.FolderName = $erEventRecord.Properties[1].Value.ToString()
$exAuditObject.Mailbox = $erEventRecord.Properties[2].Value.ToString()
$exAuditObject.AccessingUser = $erEventRecord.Properties[3].Value.ToString()
$exAuditObject.AccessingUserLegacyExchangeDN = $erEventRecord.Properties[4].Value.ToString()
$exAuditObject.MailboxLegacyExchangeDN = $erEventRecord.Properties[5].Value.ToString()
$exAuditObject.MachineName = $erEventRecord.Properties[8].Value.ToString()
$exAuditObject.Address = $erEventRecord.Properties[9].Value.ToString()
$exAuditObject.ProcessName = $erEventRecord.Properties[10].Value.ToString()
$exAuditObject.ApplicationId = $erEventRecord.Properties[12].Value.ToString()
$eacombCollection +=$exAuditObject
}
}
$eacombCollection | export-csv –encoding "unicode" -noTypeInformation "c:\temp\exauditOutput.csv"
Saturday, August 29, 2009
Connections Online Exchange Powershell Talk
For those of you who have ever wished to see what it takes to build some of the more advanced scripts that i post on this blog as part of the Connections Online conference I put together the following talk http://videos.devconnections.com/product/Exchange-Management-Shell-Beyond-the-One-Liner,5789-3.aspx . While i cant promise that this will teach you to be a guru in 60 minutes hopefully it may present a few different idea's and methods you may not have considered before and allow you to bash out a few more of your own scripts. There are also a lot of great videos on various aspects of Exchange posted by other Exchange MVP's that are defiantly worth checking out http://videos.devconnections.com/catalog/Exchange,390.aspx
Changes to SearchFilters in the EWS Managed API RC
With the recent release of the RC for the EWS Managed API there was one change that affected a lot of the scripts i've posted on this blog. While i will update them all in time if you have created any scripts of your own based on these you will need to look at making the following changes to allows your scripts to function correctly when you update to the RC of the Managed API.
The change was
$Sf = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::IsRead, $false)
$view = New-Object Microsoft.Exchange.WebServices.Data.ItemView(1)
$findResults = $service.FindItems([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox,$Sf,$view)
or if your binding to the folder directly eg
$inbox = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox)
$findResults = $inbox.Finditems($sf,$view)
The change was
- In a FindItems call, the search filter now has to be passed as a parameter to FindItems, as opposed to being a property of ItemView
$Sf = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.EmailMessageSchema]::IsRead, $false)
$view = New-Object Microsoft.Exchange.WebServices.Data.ItemView(1)
$findResults = $service.FindItems([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox,$Sf,$view)
or if your binding to the folder directly eg
$inbox = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox)
$findResults = $inbox.Finditems($sf,$view)
Sunday, August 23, 2009
Setting the Out of Office (OOF) with powershell and the EWS Managed API
Along with the RC of Exchange 2010 this week the RC of the EWS Managed API made its debut. There are a number of changes some of which break a few of the scripts i've posted here there is full list of the changes on http://social.technet.microsoft.com/Forums/en-US/exchangesvrdevelopment/thread/7053c628-9227-4cd5-ba9d-ed6fe8d484cb. But the good thing is now OOF and Freebusy work and the DNS lookup for autodisover is another major improvement.
Setting the OOF is pretty easy with the API lets look at a few examples as before you first need to create a Service object and authenticate if you haven't used the EWS Managed API in Powershell before see this post as a primer. To use this OOF code you first need to download and install the RC version of the ManagedAPI from here.
So lets take it as read we have our $service object now to get the Offsettings its just one call with the email address of the Account you want to pull the settings for.
$oofSetting = $service.GetUserOofSettings("user@domain.com")
A good point to remember when using any of the availability service Call like OOF and FreeBusy you need to always use delegate Access as EWS impersonation doesn't work with the availability service.
To show the OOF state just look at the State property
$oofSetting.State.ToString()
To show the oof message you have to look at the InternalReply or ExternalReply properties
$oofSetting.InternalReply.ToString()
$oofSetting.ExternalReply.ToString()
To set the State and or the Message property you should first get the current setting modify the property you want to modify and then update the OOF.
eg to set the OOF of the currently logged on user you could use the following code.
$dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"
[void][Reflection.Assembly]::LoadFile($dllpath)
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)
$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"
$aceuser = [ADSI]$sidbind
$service.AutodiscoverUrl($aceuser.mail.ToString())
$oofSetting = $service.GetUserOofSettings($aceuser.mail.ToString())
$oofSetting.State.ToString()
$oofSetting.InternalReply = new-object Microsoft.Exchange.WebServices.Data.OofReply("Test 123")
$oofSetting.ExternalReply.ToString()
$oofSetting.State = [Microsoft.Exchange.WebServices.Data.OofState]::Enabled
$service.SetUserOofSettings($aceuser.mail.ToString(), $oofSetting);
Setting the OOF is pretty easy with the API lets look at a few examples as before you first need to create a Service object and authenticate if you haven't used the EWS Managed API in Powershell before see this post as a primer. To use this OOF code you first need to download and install the RC version of the ManagedAPI from here.
So lets take it as read we have our $service object now to get the Offsettings its just one call with the email address of the Account you want to pull the settings for.
$oofSetting = $service.GetUserOofSettings("user@domain.com")
A good point to remember when using any of the availability service Call like OOF and FreeBusy you need to always use delegate Access as EWS impersonation doesn't work with the availability service.
To show the OOF state just look at the State property
$oofSetting.State.ToString()
To show the oof message you have to look at the InternalReply or ExternalReply properties
$oofSetting.InternalReply.ToString()
$oofSetting.ExternalReply.ToString()
To set the State and or the Message property you should first get the current setting modify the property you want to modify and then update the OOF.
eg to set the OOF of the currently logged on user you could use the following code.
$dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"
[void][Reflection.Assembly]::LoadFile($dllpath)
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)
$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"
$aceuser = [ADSI]$sidbind
$service.AutodiscoverUrl($aceuser.mail.ToString())
$oofSetting = $service.GetUserOofSettings($aceuser.mail.ToString())
$oofSetting.State.ToString()
$oofSetting.InternalReply = new-object Microsoft.Exchange.WebServices.Data.OofReply("Test 123")
$oofSetting.ExternalReply.ToString()
$oofSetting.State = [Microsoft.Exchange.WebServices.Data.OofState]::Enabled
$service.SetUserOofSettings($aceuser.mail.ToString(), $oofSetting);
Saturday, August 22, 2009
Phone List AD GAL update utility – An alternate to bulk imports
If you have been administrating mail systems for a while (and then some) then you have probably had to do a bulk update or two of one or more AD properties like phone numbers and address information. Depending on the time you have and your skill at building scripts you may have had some good and not so good experiences at this. The frustrating thing can be a script you build for one problem maybe be completely useless for the next and you may find yourself again spending time you don’t have building another script. Well because I’ve had to do this one too many times I came up with the following little script that allows dynamic matching of columns in a CSV file to import data into Active Directory. The other thing this script does is actually checks the current value within AD as not to update an already existing property and it’s a latched script so doesn’t allow you to update anything without clicking yes. The later could get frustrating but it’s a lot less frustrating then trying to fix a poorly tested bulk import.
How does it work
Powershell has a great little commandlet called import-csv that make importing data from a CSV prospective pretty easy. The script will pick the first line as column headers and then populate enough drop down Gui elements so you can map as many of the fields you want from the CSV file to AD properties.
Primary Mapping Field
This is the field that is used to identify the AD object to update note the field you select here doesn't get updated. In this field you generally need to pick a field that can be used to unique identify the accounts you want to update. The best to use generally is the Mail property mostly because phone lists generally always contain this information. What happens when the script runs is it uses this information to create a dynamic ADSI query based on the values that you select. Eg if you select the mail property it will try to find the AD account to update by doing a search for any user account with this email address, note if you want to do object other then AD useraccount you will need to fiddle with the LDAP query.
Update fields
I've limited the update fields to fields that are reasonably safe to to do a bulk update on instead of reusing the dynamic map from the primary map. If you want to add any fields you need to add the exact name of the Ldap attribute to the list eg
$ADprops.Add("homePhone",0)
$ADprops.Add("mobile",0)
(the 0 is just a blank for the hashtable.)
I've put a download of this code here
The code itself looks like
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')
$form = new-object System.Windows.Forms.form
$Filecolumns = @{ }
$ADprops = @{ }
$DropDownValues = @{ }
$mbcombCollection = @()
function UpdateAD(){
$dd = "Name"
Import-Csv $fileOpen.FileName.ToString() foreach-object{
#Find User
$root = [ADSI]'LDAP://RootDSE'
$dfDefaultRootPath = "LDAP://" + $root.DefaultNamingContext.tostring()
$dfRoot = [ADSI]$dfDefaultRootPath
$gfGALQueryFilter = "(&(&(&(& (mailnickname=*)(objectCategory=person)(objectClass=user)(" + $ppDrop.SelectedItem.ToString() + "=" + $_.($ppDrop1.SelectedItem.ToString()).ToString() + ")))))"
$dfsearcher = new-object System.DirectoryServices.DirectorySearcher($dfRoot)
$dfsearcher.Filter = $gfGALQueryFilter
$updateString = ""
$srSearchResult = $dfsearcher.FindOne()
if ($srSearchResult -ne $null){
$uoUserobject = $srSearchResult.GetDirectoryEntry()
Write-host $uoUserobject.DisplayName
foreach($mapping in $mbcombCollection){
if ($mapping.CSVField.SelectedItem -ne $null){
$updateString = $updateString + "User : " + $uoUserobject.DisplayName.ToString() + "`r`n"
$nval = $_.($mapping.CSVField.SelectedItem.ToString()).ToString()
$updateString = $updateString + "Property : " + $mapping.ADField.SelectedItem.ToString() + " Current Value : " + $uoUserobject.($mapping.ADField.SelectedItem.ToString()).ToString() + "`r`n"
$updateString = $updateString + "Update To Value : " + $_.($mapping.CSVField.SelectedItem.ToString()).ToString()
if (($uoUserobject.($mapping.ADField.SelectedItem.ToString()).ToString()) -ne $nval){
$result = [Microsoft.VisualBasic.Interaction]::MsgBox($updateString , 'YesNo,Question', "Confirm Change")
switch ($result) {
'Yes' {
$uoUserobject.($mapping.ADField.SelectedItem.ToString()) = $nval
$uoUserobject.setinfo()
}
}
}
}
}
}
}
}
$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"
$aceuser = [ADSI]$sidbind
# Add Primary Prop Drop Down
$ppDrop = new-object System.Windows.Forms.ComboBox
$ppDrop.Location = new-object System.Drawing.Size(190,40)
$ppDrop.Size = new-object System.Drawing.Size(200,30)
foreach ($prop in $aceuser.psbase.properties){
foreach($name in $prop.PropertyNames){
if ($name -ne $null){$ppDrop.Items.Add($name)}
}
}
$ADprops.Add("cn",0)
$ADprops.Add("sn",0)
$ADprops.Add("c",0)
$ADprops.Add("l",0)
$ADprops.Add("st",0)
$ADprops.Add("title",0)
$ADprops.Add("postalCode",0)
$ADprops.Add("postOfficeBox",0)
$ADprops.Add("physicalDeliveryOfficeName",0)
$ADprops.Add("telephoneNumber",0)
$ADprops.Add("facsimileTelephoneNumber",0)
$ADprops.Add("givenName",0)
$ADprops.Add("displayName",0)
$ADprops.Add("co",0)
$ADprops.Add("department",0)
$ADprops.Add("streetAddress",0)
$ADprops.Add("extensionAttribute1",0)
$ADprops.Add("mailNickname",0)
$ADprops.Add("wWWHomePage",0)
$ADprops.Add("name",0)
$ADprops.Add("countryCode",0)
$ADprops.Add("ipPhone",0)
$ADprops.Add("homePhone",0)
$ADprops.Add("mobile",0)
$form.Controls.Add($ppDrop)
# Add Primary Prop Drop Down
$ppDrop1 = new-object System.Windows.Forms.ComboBox
$ppDrop1.Location = new-object System.Drawing.Size(20,40)
$ppDrop1.Size = new-object System.Drawing.Size(150,30)
$fileOpen = New-Object System.Windows.Forms.OpenFileDialog
$fileOpen.InitialDirectory = $Directory
$fileOpen.Filter = "csv files (*.csv)*.csv"
$fileOpen.Title = "Import File"
$fileOpen.ShowDialog()
$fileOpen.FileName
Import-Csv $fileOpen.FileName.ToString() select -first 1 %{$_.PSObject.Properties} foreach-object {
$Filecolumns.add($_.name.ToString(),0)
}
$Filecolumns.GetEnumerator() sort name foreach-object {
$ppDrop1.Items.Add($_.Key.ToString())
}
$form.Controls.Add($ppDrop1)
$dloc = 120
$Filecolumns.GetEnumerator() foreach-object {
$mbcomb = "" select CSVField,ADfield
$dloc = $dloc + 25
$ppDrop2 = new-object System.Windows.Forms.ComboBox
$ppDrop2.Size = new-object System.Drawing.Size(200,30)
$ppDrop2.Location = new-object System.Drawing.Size(190,$dloc)
$ADprops.GetEnumerator() sort name foreach-object {
$ppDrop2.Items.Add($_.Key.ToString())
}
$mbcomb.ADfield = $ppDrop2
$form.Controls.Add($ppDrop2)
$ppDrop3 = new-object System.Windows.Forms.ComboBox
$ppDrop3.Size = new-object System.Drawing.Size(150,30)
$ppDrop3.Location = new-object System.Drawing.Size(20,$dloc)
$Filecolumns.GetEnumerator() sort name foreach-object {
$ppDrop3.Items.Add($_.Key.ToString())
}
$mbcomb.CSVField = $ppDrop3
$form.Controls.Add($ppDrop3)
$mbcombCollection += $mbcomb
}
$Gbox = new-object System.Windows.Forms.GroupBox
$Gbox.Location = new-object System.Drawing.Size(10,5)
$Gbox.Size = new-object System.Drawing.Size(400,100)
$Gbox.Text = "Primary Mapping field"
$form.Controls.Add($Gbox)
$Gbox = new-object System.Windows.Forms.GroupBox
$Gbox.Location = new-object System.Drawing.Size(10,120)
$Gbox.Size = new-object System.Drawing.Size(400,800)
$Gbox.Text = "Update Fields "
$form.Controls.Add($Gbox)
# Add Export MB Button
$exButton1 = new-object System.Windows.Forms.Button
$exButton1.Location = new-object System.Drawing.Size(420,10)
$exButton1.Size = new-object System.Drawing.Size(125,20)
$exButton1.Text = "Update AD"
$exButton1.Add_Click({UpdateAD})
$form.Controls.Add($exButton1)
$form.Text = "AD Phone List Update GUI"
$form.size = new-object System.Drawing.Size(1000,700)
$form.autoscroll = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()
How does it work
Powershell has a great little commandlet called import-csv that make importing data from a CSV prospective pretty easy. The script will pick the first line as column headers and then populate enough drop down Gui elements so you can map as many of the fields you want from the CSV file to AD properties.
Primary Mapping Field
This is the field that is used to identify the AD object to update note the field you select here doesn't get updated. In this field you generally need to pick a field that can be used to unique identify the accounts you want to update. The best to use generally is the Mail property mostly because phone lists generally always contain this information. What happens when the script runs is it uses this information to create a dynamic ADSI query based on the values that you select. Eg if you select the mail property it will try to find the AD account to update by doing a search for any user account with this email address, note if you want to do object other then AD useraccount you will need to fiddle with the LDAP query.
Update fields
I've limited the update fields to fields that are reasonably safe to to do a bulk update on instead of reusing the dynamic map from the primary map. If you want to add any fields you need to add the exact name of the Ldap attribute to the list eg
$ADprops.Add("homePhone",0)
$ADprops.Add("mobile",0)
(the 0 is just a blank for the hashtable.)
I've put a download of this code here
The code itself looks like
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')
$form = new-object System.Windows.Forms.form
$Filecolumns = @{ }
$ADprops = @{ }
$DropDownValues = @{ }
$mbcombCollection = @()
function UpdateAD(){
$dd = "Name"
Import-Csv $fileOpen.FileName.ToString() foreach-object{
#Find User
$root = [ADSI]'LDAP://RootDSE'
$dfDefaultRootPath = "LDAP://" + $root.DefaultNamingContext.tostring()
$dfRoot = [ADSI]$dfDefaultRootPath
$gfGALQueryFilter = "(&(&(&(& (mailnickname=*)(objectCategory=person)(objectClass=user)(" + $ppDrop.SelectedItem.ToString() + "=" + $_.($ppDrop1.SelectedItem.ToString()).ToString() + ")))))"
$dfsearcher = new-object System.DirectoryServices.DirectorySearcher($dfRoot)
$dfsearcher.Filter = $gfGALQueryFilter
$updateString = ""
$srSearchResult = $dfsearcher.FindOne()
if ($srSearchResult -ne $null){
$uoUserobject = $srSearchResult.GetDirectoryEntry()
Write-host $uoUserobject.DisplayName
foreach($mapping in $mbcombCollection){
if ($mapping.CSVField.SelectedItem -ne $null){
$updateString = $updateString + "User : " + $uoUserobject.DisplayName.ToString() + "`r`n"
$nval = $_.($mapping.CSVField.SelectedItem.ToString()).ToString()
$updateString = $updateString + "Property : " + $mapping.ADField.SelectedItem.ToString() + " Current Value : " + $uoUserobject.($mapping.ADField.SelectedItem.ToString()).ToString() + "`r`n"
$updateString = $updateString + "Update To Value : " + $_.($mapping.CSVField.SelectedItem.ToString()).ToString()
if (($uoUserobject.($mapping.ADField.SelectedItem.ToString()).ToString()) -ne $nval){
$result = [Microsoft.VisualBasic.Interaction]::MsgBox($updateString , 'YesNo,Question', "Confirm Change")
switch ($result) {
'Yes' {
$uoUserobject.($mapping.ADField.SelectedItem.ToString()) = $nval
$uoUserobject.setinfo()
}
}
}
}
}
}
}
}
$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"
$aceuser = [ADSI]$sidbind
# Add Primary Prop Drop Down
$ppDrop = new-object System.Windows.Forms.ComboBox
$ppDrop.Location = new-object System.Drawing.Size(190,40)
$ppDrop.Size = new-object System.Drawing.Size(200,30)
foreach ($prop in $aceuser.psbase.properties){
foreach($name in $prop.PropertyNames){
if ($name -ne $null){$ppDrop.Items.Add($name)}
}
}
$ADprops.Add("cn",0)
$ADprops.Add("sn",0)
$ADprops.Add("c",0)
$ADprops.Add("l",0)
$ADprops.Add("st",0)
$ADprops.Add("title",0)
$ADprops.Add("postalCode",0)
$ADprops.Add("postOfficeBox",0)
$ADprops.Add("physicalDeliveryOfficeName",0)
$ADprops.Add("telephoneNumber",0)
$ADprops.Add("facsimileTelephoneNumber",0)
$ADprops.Add("givenName",0)
$ADprops.Add("displayName",0)
$ADprops.Add("co",0)
$ADprops.Add("department",0)
$ADprops.Add("streetAddress",0)
$ADprops.Add("extensionAttribute1",0)
$ADprops.Add("mailNickname",0)
$ADprops.Add("wWWHomePage",0)
$ADprops.Add("name",0)
$ADprops.Add("countryCode",0)
$ADprops.Add("ipPhone",0)
$ADprops.Add("homePhone",0)
$ADprops.Add("mobile",0)
$form.Controls.Add($ppDrop)
# Add Primary Prop Drop Down
$ppDrop1 = new-object System.Windows.Forms.ComboBox
$ppDrop1.Location = new-object System.Drawing.Size(20,40)
$ppDrop1.Size = new-object System.Drawing.Size(150,30)
$fileOpen = New-Object System.Windows.Forms.OpenFileDialog
$fileOpen.InitialDirectory = $Directory
$fileOpen.Filter = "csv files (*.csv)*.csv"
$fileOpen.Title = "Import File"
$fileOpen.ShowDialog()
$fileOpen.FileName
Import-Csv $fileOpen.FileName.ToString() select -first 1 %{$_.PSObject.Properties} foreach-object {
$Filecolumns.add($_.name.ToString(),0)
}
$Filecolumns.GetEnumerator() sort name foreach-object {
$ppDrop1.Items.Add($_.Key.ToString())
}
$form.Controls.Add($ppDrop1)
$dloc = 120
$Filecolumns.GetEnumerator() foreach-object {
$mbcomb = "" select CSVField,ADfield
$dloc = $dloc + 25
$ppDrop2 = new-object System.Windows.Forms.ComboBox
$ppDrop2.Size = new-object System.Drawing.Size(200,30)
$ppDrop2.Location = new-object System.Drawing.Size(190,$dloc)
$ADprops.GetEnumerator() sort name foreach-object {
$ppDrop2.Items.Add($_.Key.ToString())
}
$mbcomb.ADfield = $ppDrop2
$form.Controls.Add($ppDrop2)
$ppDrop3 = new-object System.Windows.Forms.ComboBox
$ppDrop3.Size = new-object System.Drawing.Size(150,30)
$ppDrop3.Location = new-object System.Drawing.Size(20,$dloc)
$Filecolumns.GetEnumerator() sort name foreach-object {
$ppDrop3.Items.Add($_.Key.ToString())
}
$mbcomb.CSVField = $ppDrop3
$form.Controls.Add($ppDrop3)
$mbcombCollection += $mbcomb
}
$Gbox = new-object System.Windows.Forms.GroupBox
$Gbox.Location = new-object System.Drawing.Size(10,5)
$Gbox.Size = new-object System.Drawing.Size(400,100)
$Gbox.Text = "Primary Mapping field"
$form.Controls.Add($Gbox)
$Gbox = new-object System.Windows.Forms.GroupBox
$Gbox.Location = new-object System.Drawing.Size(10,120)
$Gbox.Size = new-object System.Drawing.Size(400,800)
$Gbox.Text = "Update Fields "
$form.Controls.Add($Gbox)
# Add Export MB Button
$exButton1 = new-object System.Windows.Forms.Button
$exButton1.Location = new-object System.Drawing.Size(420,10)
$exButton1.Size = new-object System.Drawing.Size(125,20)
$exButton1.Text = "Update AD"
$exButton1.Add_Click({UpdateAD})
$form.Controls.Add($exButton1)
$form.Text = "AD Phone List Update GUI"
$form.size = new-object System.Drawing.Size(1000,700)
$form.autoscroll = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()
Subscribe to:
Posts (Atom)
