Adventures in querying the EventHistory table
Published Jun 24 2013 12:14 PM 60.5K Views

Beginning with Exchange 2007 the Exchange database has had an internal table called EventHistory.  This table has been used to track the events upon which several of the assistants are based and for other short term internal record keeping.  The way to query the table hasn’t been publicized before but it has a number of uses:

  • It may tell you the fate of a deleted item (for situations where Audit logging or store tracing was not in place at the time of the delete)
  • It can list accounts who have recently touched a mailbox
  • It can show you the clients that have touched a mailbox

Events are kept in the EventHistory table for up to 7 days by default.  You can check what your retention period is for all databases by running:

Get-mailboxdatabase | fl name,event*
Name                        : MainDB
EventHistoryRetentionPeriod : 7.00:00:00

There are a number of approaches to querying the table.  Let’s start with a script (please review my caveats before actually running the script) and review the data that is displayed.  The script is:

Add-PSSnapin Microsoft.Exchange.Management.Powershell.Support
$db = (get-mailbox <user alias>).database
$mb=(get-mailbox <user alias>).exchangeguid
Get-DatabaseEvent $db -MailboxGuid $mb -resultsize unlimited | ? {$_.documentid -ne 0 -and $_.CreateTime -ge  “<mm/dd/yyyy>”} | fl > c:\temp\EventHistory.txt

For the CreateTime specify the day of the event you are looking for.  By default a maximum of 7 days are tracked.  Depending on the date range selected and the activity in the mailbox the resulting file size starts at about 5KB and I have seen it rise to nearly 1GB.  You can also replace the “| fl > c:\temp\EventHistory.txt” with “| export-csv c:\temp\EventHistory.csv”.  I am using the FL output because it is easier for illustration purposes.

Inside the EventHistory.txt file will be events like this one (this one is a bulk delete of emails using OWA):

Counter          : 15328155
CreateTime       : 1/28/2013 9:46:16 PM
ItemType         : MAPI_MESSAGE
EventName        : ObjectMoved
Flags            : None
MailboxGuid      : d05f83c1-255c-42ae-b74f-1ac3329b306a
ObjectClass      : IPM.Note
ItemEntryId      : 000000008CFDF3C2BA873648866A1C17D0E3F1AB0700BC9C9BA42124CD4F896E8915C86B2BD00000006027C20000BC9C9BA4
2124CD4F896E8915C86B2BD0000041B6E6570000

ParentEntryId    : 000000008CFDF3C2BA873648866A1C17D0E3F1AB0100BC9C9BA42124CD4F896E8915C86B2BD00000006027C20000
OldItemEntryId   : 000000008CFDF3C2BA873648866A1C17D0E3F1AB0700BC9C9BA42124CD4F896E8915C86B2BD00000006027BF0000BC9C9BA4
2124CD4F896E8915C86B2BD0000041B6D6260000

OldParentEntryId : 000000008CFDF3C2BA873648866A1C17D0E3F1AB0100BC9C9BA42124CD4F896E8915C86B2BD00000006027BF0000
ItemCount        : 0
UnreadItemCount  : 0
ExtendedFlags    : 2147483648
ClientCategory : WebServices
PrincipalName : Contoso\TestUser
PrincipalSid : S-1-5-21-915020002-1829042167-1583638127-1930
Database         : Mailbox Database 1858470524
DocumentId       : 10876

The EventName shows what was done with the object.  End user deletes will be listed as moves.  When you delete an item it is moved to either Deleted Items or to the Recoverable Items subtree

I highlighted the ItemEntryID because that ties directly to the Item you need to locate.  The subject and other human readable properties are not included in this table.  The ItemEntryID is the database engine’s way of uniquely identifying each item.  You can use this to search the mailbox in MFCMAPI and get properties like Subject, From, To, etc.

  • The ParentEntryID is the folder in which the item presently resides.
  • The OldItemEntryID is the previous ItemEntryID before the item was deleted.
  • The OldParentEntryID is the folder it used to reside in.

Flags will often show values like SearchFolder.  Many events flagged as being related to search folders or folders are not going to be interesting to your investigations.  If you are researching the fate of a deleted item they can be ignored.

ClientCategory is the type of client that requested the operation.  In this case webservices means that OWA was used to remove the item as part of a bulk operation conducted against a 2010 mailbox.  If it was deleted individually then Exchange 2010 would list OWA here.   The way ClientCategories are tracked in Exchange 2013 is a little different; you should see OWA for all End User deletes through that tool.

PrincipalName and PrincipalSid give you the identity of the account that was passed to the information store when the operation was requested.  At the time of writing these are not displayed by Exchange 2013.

So – we have an output file.  What do we do with it?  The easy uses for the file (once it is imported into your favorite data analysis tool) at this time are:

  • List of all accounts that have caused an event to be logged in the time period you specified
  • Get a summary of operations (deletes, moves, new items, etc.) conducted on the days you specified
  • Get a list of client types that have changed something in the mailbox
  • Search the records returned for a particular ItemEntryID

In our output the ItemEntryID is not immediately useful.  To find out what the ItemEntryID in each record actually is we need to use MFCMAPI (steps related to MFCMAPI are at the end of this blog).  Once you are in MFCMAPI you can go to the Tools menu, select “Entry ID” and then “Open given entry ID”.  In the dialog that appears paste in the ItemEntryId or the OldItemEntryId that you want to investigate.  When you click OK MFCMAPI will take you to the item you specified (if it is still in the mailbox).  Once MFCMAPI takes you to the mail item you will see the Subject, From, To, Creation date and other meaningful properties.  You will also see there is a property called PR_ENTRYID.  PR_ENTRYID is the MAPI name for ItemEntryID.  This field is our link between the representation of the data in our PowrShell cmdlet and in the more human readable presentation in MFCMAPI.

Pulling ItemEntryIDs from the PowerShell output and looking them up one at a time in MFCMAPI may be a little too tedious for most Exchange administrators.  If you have mo re than a handful of items you want to check (to see if they are useful and meaningful) it will take a long time to locate them all. 

The alternative is to start in MFCMAPI.  If you can find the item you want there by looking at the subject line, date or other properties you can use the content of the PR_ENTRYID field in MFCMAPI to modify the Get-DatabaseEvent query to pull up the history for just that item.  To do this you need access to either a restored copy of the mailbox in a lab or the item of interest must still be in the mailbox (possibly in deleted items or recoverable items).  Here is a sample of how the get-databaseevent cmdlet would be used if you have the PR_ENTRYID:

Get-DatabaseEvent $db -MailboxGuid $mb -resultsize unlimited | ? {$_.ItemEntryID -eq 
“000000008CFDF3C2BA873648866A1C17D0E3F1AB0700BC9C9BA42124CD4F896E8915C86B2BD00000006027C20000BC9C9
BA42124CD4F896E8915C86B2BD0000041B6E6570000”
–or $_.OldItemEntryId –eq
“000000008CFDF3C2BA873648866A1C17D0E3F1AB0700BC9C9BA42124CD4F896E8915C86B2BD00000006027C20000BC9C9
BA42124CD4F896E8915C86B2BD0000041B6E6570000”} | export-csv c:\temp\SingleItemEventHistory.txt

Sometimes I have not been able to locate an item using this technique.  If that happens it is useful to note that the PR_ENTRYID contains the ID of the mailbox, the folder and the item.  For example here is the PR_ENTRYID of an item in the Inbox followed by the PR_ENTRYID of the Inbox itself:

000000006064986ABA58DF40A86C0C67E716264807004885B50069B1D04994374C02417D45A100000000324E00003DEF8F7
FFC1E3448B9D276F022E0E42D0000396D1B280000
000000006064986ABA58DF40A86C0C67E716264801004885B50069B1D04994374C02417D45A100000000324E0000

For the sake of comparison here are the PR_ENTRYIDs of two more folders in the same mailbox:

000000006064986ABA58DF40A86C0C67E716264801004885B50069B1D04994374C02417D45A10000000032510000 - deleted items folder
000000006064986ABA58DF40A86C0C67E716264801004885B50069B1D04994374C02417D45A100000000324B0000 - ipm_subtree folder

From this you should be able to get an idea of how the field is divided up by looking at where the repeated digits end.  For the purpose of tracking down an individual item that may be in a different folder (because of multiple moves) we want to be able to isolate the portion of the PR_ENTRYID that is specific to the item and modify our PowerShell statement appropriately.  The final statement would look like this:

Get-DatabaseEvent $db –MailboxGuid $mb -resultsize unlimited | ? {$_.ItemEntryID -like  “*3DEF8F7FFC1E3448B9D276F022E0E42D0000396D1B280000” –or $_.OldItemEntryId –like “*3DEF8F7FFC1E3448B9D276F022E0E42D0000396D1B280000”} | export-csv c:\temp\SingleItemEventHistory.txt

At this point if we still can’t find the item we want then our last chances are to remove the $_.MailboxGuid from the conditions (meaning we will search all mailboxes in the database – a very expensive operation please review the caveats) or to search other databases in the organization (databases containing delegates of the current user would be the ones to start with).  If the data still can’t be found you have either made an error or the records are no longer present.  If the records are present you should see all actions taken on the item recently.

Caveats:

  • At the time of writing Exchange 2013 is not reporting the account information in the EventHistory records.  You can use the technique – you just won’t get any account names or SIDs from it.
  • You can change the length of time items stay in the EventHistory table with Set-MailboxDatabase -EventHistoryRetentionPeriod.  You can choose a period from 1 second up to 30 days.  I don’t recommend setting a time that is too short as I have not tested how Event based assistants would react to that.  For the full syntax of Set-MailboxDatabase please check the TechNet article for your Exchange version.
  • If you choose to direct your output to a variable instead of a text file you should make sure you are running the PowerShell cmdlets from a workstation with the management tools installed.  The variable (and the PowerShell session) are likely to consume a substantial amount of memory. 
  • These queries of the EventHistory table are expensive to run.  Use good judgment in when you choose to run them based on the demands of your environment.  In the labs I use all these queries take a second or two, but on a busy server with large databases  you can easily be looking at 20-30 minutes per query.  There will also be an I/O impact, but I don’t have a way to estimate that for you in advance.

You can make the operation less expensive by lowering the number of records returned by Get-DatabaseEvent.  We are already including the database and mailbox to look for.  You can also add the EventNames and the StartCounter.  The latter of these might be a little tricky.  The StartCounter is an internal number that is specific to this table in the current database.  You probably won’t know what counter value to use until you have already run a query and noted the counter values.  This means StartCounter is mostly useful for reducing the impact of your second and subsequent queries of the same table in the same database.

Assuming you know a relevant StartCounter value here is an example of doing this:

Get-DatabaseEvent $db -MailboxGuid $mb –EventNames objectmodified, objectdeleted –StartCounter 15328155 -resultsize unlimited | ? {$_.documentid -ne 0 -and $_.CreateTime -ge  “<mm/dd/yyyy>”} | fl > c:\temp\EventHistory.txt

The example above searches a mailbox on a particular database for the event types specified and ignores any rows with a lower counter value than specified.  This smaller dataset is then passed to the PowerShell pipeline for additional filtering and is ultimately saved to a CSV file that you can import into your favorite analysis tool.  If you prefer to conduct your analysis in PowerShell you also have the option of assigning the result of Get-DatabaseEvent to a PowerShell variable (just remember the variable and the PowerShell session will consume memory proportional to the resultset returned).

So how do you find the PR_ENTRYIDs I mentioned above in MFCMAPI?

You can download MFCMAPI from https://mfcmapi.codeplex.com.

1. We need an Outlook profile for the mailbox we are searching.  That profile should NOT be configured for Cached mode.  If you are doing this from your machine make sure you have Full Access to the mailbox of the user.  You can then create a profile for that specific user.

2. Once you have the profile open MFCMAPI and Log on

image

3. Select the profile you created for Step 1.  You will see a screen like this one:

image

4. Double-click the mailbox which will open a window showing you the mailbox details.

5. If you already know the ItemEntryID you want to open and inspect you can locate it with this menu option:

image

6. If you don’t have the ItemEntryID expand the Root Container, Recoverable Item and Top of Information Store.  If you are trying to locate details on a deleted item look in the Deleted Items folder and the Recoverable Items folder (and it’s subfolders)

image

7. Double-click Deleted Items to open a window that looks like this one:

image

8. Click the item to fill in the lower half of the window with the properties

9. Locate the PR_EntryID property and double-click it

image

10. The Binary box contains the value of the PR_ENTRYID field that you can use to search the EventHistory table in the Store.  If you locate this value with MFCMAPI first you can use it to limit the search as I described above.  If you don’t have this value you can pull the full history and use the ItemEntryIDs as a basis to search MFCMAPI.

Thanks to Jesse Tedoff for the idea!

Chris Pollitt

10 Comments
Not applicable

Great article thanks. Now I will be able to prove to users that items do not get deleted by themselves :)

Not applicable

Great article! Very helpful! I actually just needed to use this to figure out who disconnected a mailbox and it worked perfectly. Not to sound unappreciative of this blog post, but are there any plans on creating a TechNet article for the Get-DatabaseEvent cmdlet in order to see what the possible properties are, such as the different values of the EventName property?

FYI - When running this in the Exchange 2007 Management Shell, it didn't recognize the -MailboxGuid parameter, so I had to add that to the where filter instead:

Get-DatabaseEvent $db -resultsize unlimited | ? {$_.documentid -ne 0 -and $_.CreateTime -ge  “<mm/dd/yyyy>” -and $_.MailboxGuid -eq $mb} | fl > c:tempEventHistory.txt

Thanks!

-Cory

Not applicable

Excellent

Not applicable

Dear Chris,

First of all thank you very much for revealing this EventHistory Feature! It will for sure help to find the causes of some "miracles".. ;)

And here is already a question based on a "miracle" where Exchange changes meetings "on its own", and as user says, "I didn't do anything at this night time".

This is a sniplet of the relevant output:

ItemType EventName Flags ObjectClass ItemCount UnreadItemCount ExtendedFlags ClientCategory PrincipalName

MAPI_FOLDER ObjectModified ContentOnly -1 -1 1073741824 AirSync NT AUTHORITYSYSTEM

MAPI_MESSAGE ObjectModified None Exchange.ContentsSyncData 0 0 1073742019 AirSync NT AUTHORITYSYSTEM

MAPI_FOLDER ObjectModified None -1 -1 1073741824 AirSync DomainUser

MAPI_FOLDER ObjectModified ContentOnly 23832 -1 1073741824 WebServices NT AUTHORITYSYSTEM

MAPI_FOLDER ObjectModified ContentOnly IPF.Appointment -1 -1 2147483648 WebServices NT AUTHORITYSYSTEM

MAPI_MESSAGE ObjectModified SearchFolder IPM.Appointment 0 0 1073741824 WebServices NT AUTHORITYSYSTEM

MAPI_MESSAGE ObjectModified SearchFolder IPM.Appointment 0 0 1073741824 WebServices NT AUTHORITYSYSTEM

Can you please shed light on those events?

I actually don't understand, why "SYSTEM" 'starts' with AirSync and modifies the object, ContentOnly.

Is this the Calendar Repair Agent?

And the same question like Cory, "Will this be documented in detail?"

Thank you vey much in advance!

Cheers,

Alex

Not applicable

Well, I can't get this working at all. I can't see any pre-requisite requirement, what's going wrong? I've tried with the full script provided, as well as breaking it down to the most simply possible.  Always get the same error:

[PS] C:scriptsps>Get-DatabaseEvent -Identity DAGTest

Get-DatabaseEvent : Value cannot be null.

Parameter name: parameters

At line:1 char:18

+ Get-DatabaseEvent <<<<  -Identity DAGTest

   + CategoryInfo          : NotSpecified: (:) [Get-DatabaseEvent], ArgumentNullException

   + FullyQualifiedErrorId : System.ArgumentNullException,Microsoft.Exchange.Management.Powershell.Support.GetDatabas

  eEvent

Exchange 2010 SP1. Would really like to get this working, and learn more about it.

Thanks for the article :)

Not applicable

Well, figured this out, I had to run it on either a real Exchange server, or the mailbox server where a copy of the database exists. Just having the Exchange Management Tools installed isn't enough.

Not applicable

Great article!  Fascinating insight and details.

Not applicable

Getting this error??

[PS] C:Windowssystem32>Get-DatabaseEvent $db -MailboxGuid $mb -resultsize unlimited | ? {$_.documentid -ne 0 -and $_.CreateTime -ge  "<mm/dd/yy

yy>"} | fl > D:tempEventHistory.txt

Get-DatabaseEvent : Value cannot be null.

Parameter name: parameters

At line:1 char:18

+ Get-DatabaseEvent <<<<  $db -MailboxGuid $mb -resultsize unlimited | ? {$_.documentid -ne 0 -and $_.CreateTime -ge  "<mm/dd/yyyy>"} | fl > D:

tempEventHistory.txt

   + CategoryInfo          : NotSpecified: (:) [Get-DatabaseEvent], ArgumentNullException

   + FullyQualifiedErrorId : System.ArgumentNullException,Microsoft.Exchange.Management.Powershell.Support.GetDatabaseEvent

Not applicable

I finally had a chance to test this on a real problem. User complained all email disappeared from his Inbox when he came into work this morning and started Outlook. When I run the script and get the output I see around 50 emails were moved to Deleted Items from Inbox via OWA by the account "RESOURCEFORESTDOMAINCASCOMPUTERNAME$".

I'm not sure what this means. It appears to back up his story that he didn't do it, but why did the CAS computer account delete items?

Not applicable

Hi Chris, its a nice article, i had a chance to test on my own mailbox (exchange 2010) and from output i could see that objects deleted by the client category :transport under exchange account (domainserver$).

Also using mfcmapi navigated to parent ID and from their i could see so many messages in transport folder.

Could you please explain what Transport queue or folder? and based on what criteria mails are moving to transport folder?

Version history
Last update:
‎Jul 01 2019 04:13 PM
Updated by: