Query on when and who archived an email

[philt]

Hi All,

Is there a way to determine when an email was archived and by who (eg maybe a delegate on the mailbox did it).

I have a user where most of his recent emails has been archived. These emails fall well outside our policy so the only way I feel this can happen is via the user doing a "Store in Vault" by mistake. Of course the user says "I'm pretty sure I didn't".

We are EV9 SP1. Exchange 2007 SP2.

Thanks in advance.

Cheers,

Phil

[JesusWept3]

Do you have auditing enabled?
That may be the only way that you can really tell

Your best bet really is to look at the IIS logs (Start -> Run -> "logfiles" and press OK)
You may see some references to clientaction.asp that will reference his vault ID and the IP Address / NTLM username etc that did it.

Maybe he has a different policy than other people? perhaps EVPM?


 

[mashles]

To find out the archived date you can use a SQL query.

You'll need to get the transaction id first:

1. Open Microsoft Outlook and select the archived item.
2. Press CTRL + SHIFT and left-click any one of the EV toolbar buttons to open the EV Diagnostic window.
3. Click the Vault Information button.  
4. In the section titled Selected Item Properties, scroll down to the Saveset ID.  A character string should be displayed similar to:
140400000000000~200812042231320000~Z~EE3C5E02AA0946E49F3362BDE9986C51

5.  Identify the Transaction ID of the Saveset.  This is the last component of the Saveset Identity.

      Ex. 140400000000000~200812042231320000~0~EE3C5E02AA0946E49F3362BDE9986C51

      In this example, the IdTransaction is "EE3C5E02AA0946E49F3362BDE9986C51"

6. Convert the Transaction ID into the IdTransaction value.

       This is performed by placing "-" after the first 8 characters, then after the next 4 (three times).

       Ex. Transaction ID :  "EE3C5E02AA0946E49F3362BDE9986C51"

        IdTransaction  :  "EE3C5E02-AA09-46E4-9F33-62BDE9986C51"

7. Run the following SQL Query to identify the CollectionIdentity Value

   (This is an example using the IdTransaction value identified earlier in article)

USE VAULTSTORE_DB_NAME

SELECT archiveddate

FROM Saveset

WHERE IdTransaction = 'EE3C5E02-AA09-46E4-9F33-62BDE9986C51'

Without auditing then you could look into the IIS logs at the archivedate date and time, and see which user did the archiving. That will only work if you are using the HTTP client though, if you are using the DCOM client then it wont have an IIS entry.

Hope that helps.

[JesusWept3]

I'm pretty sure even with a full client it still pushes the item through ClientAction.asp

[philt]

Thanks for your replies\advice.

Just to make things more difficult were don't use placeholders so don't have "Selected Item Properties" for the archived item in System Information. Currently we don't have auditing enabled, we only use auditing if\when journal searches are being performed.

So what I did was use mashles suggestion but a little differently.

Used Browser search to search for an item in question, opened the item and obtained the Transaction ID in the address bar. Transformed this to IdTransaction (8 chars,-,4 chars,-,4 chars,-,4 chars,-,12 chars)

Ran the query and bingo I have an archived time and date...in GMT by the way.

I only did this on my personal archive but at least have the steps sorted if\when the use has the same issue.

Thanks again for advice etc and hope my little bit help anyone too.

Cheers,

Phil

[Rob_Wilcox1]

An easy way of finding the archive date :

Open browser search, you know search.asp.  Edit the URL and change it to it says : search.asp?advanced=3

In the other attributes field, put adat

Run the query with any other parameters you need.  

The archive date will show in the output.

Your search page will look like this :

Your results will look like this :

JW2 is right.. if the user archived the items manually themselves, and they used the DCOM client then nothing will be recorded with auditing enabled.  If they used the HTTP client, it'll be in the IIS logs.  If a delegate did it, then that always functions as the HTTP client, so it'll be in the IIS logs. (clientaction.asp in both cases, I think)

If a user dragged and dropped to virtual vault and synchronised, it'll be uploaditem.aspx in the IIS logs.

If background/scheduled archiving task ran against all users, then you could check that other users don't have items archived with a similar archive date.

If a run now was done against the persons individual mailbox, then nothing will show that, AFAIK.

[JesusWept3]

fwiw you can just use search.asp?advanced
Another place you might want to try looking is the client logs in the users temp directory, but only a few days worth of logs will be saved

[Rob_Wilcox1]

Welll.... search.asp?advanced is the same as search.asp?advanced=1, and they don't show the "other result attributes" text box, AFAIK.

Client logs = good idea too, yep.

[JesusWept3]

ah yup sorry, you're right
Maybe thats another blog you should write, what advanced 1,2 and 3 give you :)

[MarkBarefoot]

I could be wrong here, but I also think that a lot of the auditing is recorded against the VSA user - not sure as to what extent though, but I'm sure I researched this at some point!

Mr Wilcox needs to get those articles going - share the love!

[Rob_Wilcox1]

All in good time !

[philt]

Hi all,

This is great info Rob and all others with their suggestions\advice. Surprised I couldn't find it published somewhere...guess that's the beauty of an active forum.

Rob a blog on this would be great one day when you get time.

One last question: how do I find out a user's archive vaultidentity number that is used in the saveset table? I would like to map the saveset.vaultidentity to the actual user vault name.

Thanks for all the advice and help, hope other got something out of this I certainly did.

Cheers,

Phil

[JesusWept3]

Ok so note that in the Saveset table you have ArchivePointIdentity and VaultIdentity
An ArchivePoint is the users Archive
A Vault is a folder within the users archive

So Saveset.ArchivePointIdentity = ArchivePoint.ArchivePointIdentity
And Saveset.VaultIdentity = Vault.VaultIdentity

And then it connects to the EnterpriseVaultDirectory database, both of them to the Root table

So yourVault.dbo.ArchivePoint.ArchivePointId = EnterpriseVaultDirectory.dbo.Root.VaultEntryId
And yourVault.dbo.Vault.VaultID = EnterpriseVaultDirectory.dbo.Root.VaultEntryID

An ArchivePoint in the Root table would connect to the Archive table and a Vault in the Root table would connect to the ArchiveFolder table

So Root.RootIdentity = Archive.RootIdentity and Root.RootIdentity = ArchiveFolder.RootIdentity

 

SELECT A.ArchiveName "Archive Name",
       AF.FolderName "Folder Name",
       AF.FolderPath "Folder Path",
       S.IdTransaction "Transaction ID", 
       S.ArchivedDate "Archived Date",
       S.IdDateTime "Sent Date",
       S.ItemSize "Item Size"
       
FROM EnterpriseVaultDirectory.dbo.Archive A,
     EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
     EnterpriseVaultDirectory.dbo.Root R,
     EnterpriseVaultDirectory.dbo.Root R2,
     yourVault.dbo.Saveset S,
     yourVault.dbo.Vault V,
     yourVault.dbo.ArchivePoint AP
     
WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND S.VaultIdentity = V.VaultIdentity
  AND AP.ArchivePointId = R.VaultEntryId
  AND V.VaultId = R2.VaultEntryId
  AND R.RootIdentity = A.RootIdentity
  AND R2.RootIdentity = AF.RootIdentity
  AND S.IdTransaction = '2238DFDE-0807-4D4D-95EA-874CE8F9A3B0'

If you then wanted to query say all the items in the Inbox of John Doe, you could do something such as

AND A.ArchiveName = 'Doe, John' AND AF.FolderName = 'inbox'

[philt]

Thanks JW2...I just take a little time to digest your query.

And I have another question regarding Rob's solution: What does the "Perms ID" represent in the results of the advanced browser search?...is this who archived the item? and if so can I reverse this to a person?

Again thanks heaps.

Cheers,

Phil

[JesusWept3]

The Perms ID is actually just the folder ID it was archived in.
For instance if you were to copy that ID and then go to the Vault Admin Console and hold CTRL-SHIFT and right click "Archives", you will have a popup called Find Archive or Folder.
Once you paste the ID in there, you will then see it comes up with the user and their details and then the folder.

Also an easier way maybe than the Search.asp?advanced=3, is you can use a SQL Query that would be easier, just modify the top Start Date and End Date to reflect times out of your archiving schedule

So for instance you may want to say 2011-05-17 07:00 and end at 2011-05-17 20:00, which would usually be after the regular archiving schedule and before the next archiving run kicks off

 

DECLARE @StartDate DATETIME 
DECLARE @EndDate DATETIME

SET @StartDate = '2011-0-19 07:00' 
SET @EndDate = '2011-05-19 22:00' 

SELECT A.ArchiveName "Archive",
       COUNT(S.ArchivedDate) "No. Items Archived",
       SUM(S.ItemSize)/1024 "Size of Items Archived (MB)" 
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       yourVault.dbo.ArchivePoint AP, 
       yourVault.dbo.Saveset S 
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity 
  AND  AP.ArchivePointId = R.VaultEntryId 
  AND  R.RootIdentity = A.RootIdentity 
  AND  A.ArchiveName = 'DOE, JOHN'
  AND  S.ArchivedDate  BETWEEN DATEADD(HOUR,DATEDIFF(HOUR,GETDATE(),GETUTCDATE()),@StartDate) 
  AND  DATEADD(HOUR,DATEDIFF(HOUR,GETDATE(),GETUTCDATE()),@EndDate)
GROUP BY ArchiveName

Simply put though, nothing in the database will tell you *who* archived it, just what email got archived, when it was archived, and which archive it belongs to.

As discussed before, your options are

Check the client Traces in the following location
- C:\Documents and Settings\Username\Local Settings\Temp\ev_client_*.log (Windows XP)
- C:\Users\Username\AppData\Local\Temp\ev_client_*.log (Windows 7)

an entry in the client log for a manual archive will look like

20/05/2011 02:22:11.864[3524]: User initiated manual archive action

 
Check the IIS Logs on the EV Server that has the Storage Service the user belongs to
- Open a command prompt and CD to your log file directory
- Type 'find "[archiveID]" *.* > C:\UserArchive.txt'
- Change the [archiveID] to be the ID that is assigned to the user

i,e,

find "1A9760109673CB9428CC5F17C62B7B0DC1d10000evsite" *.* > C:\UserArchive.txt

A ClientAction.asp request to archive will look like the following

2011-05-20 02:22:18 192.168.0.1 GET /EnterpriseVault/clientaction.asp act=0&fdrenc=_&dn=/O%3ddomain/OU%3dsomething/cn%3dRecipients/cn%3dUserDN&svr=yourExchServer&sid=1A9760109673CB9428CC5F17C62B7B0DC1d10000evsite&tsp=2011-05-20T02:22:14&pdl=AAAAAAAANIFBIHBFMLLHJLEDINGGOLOJMMBLPCBMABAAPCNOCCDPDNGGBCEDKDIHMNNLKIFLOBENAAAAABHDJODAAAAA 80 DOMAIN\UserName 192.168.0.2 EnterpriseVaultOutlookExt-V9.0.1.1090 200 0 0 436

If you look at the above, you can tell the user that archived the request by the NTLM Domain\Username request pushed through, though again, as people point out that this may not be present if the users are using DCOM clients (though i'm not sure how true this is, as i'm still sure that DCOM clients will post through ClientAction.asp)

However as Rob pointed out, if the user was archived via a Run Now you will not be able to tell who performed the Run Now.




 

[philt]

Hi JW2,

Thanks again...I have learned heaps from this thread.

I used the EV_client log "User initiated manual archive action" and they line up with the adat via advanced browser search.

Unfortunatly our Server Administrators don't enable IIS logging by default for normal operations, only for ongoing troubleshooting. I will turn on at a later time and do some more testing just incase i need to go through this again.

Having the EV Client log file in the users Windows profile is enough for the user to realise they did in fact manually archive.

Thanks again.

Cheers,

Phil

[JesusWept3 · Accepted solution]

Good job dude :) Don't forget to mark the post as solved!

[philt]

Marked the whole thread as solution as there are many handy hints in this thread.

[Rob_Wilcox1]

FYI, I did eventually write some information on this thread ..  Hope it's useful :

http://www.symantec.com/connect/articles/how-did-mail-get-archived 

[wandarah]

Link is most certainly bung for me.