Video Screencast Help

EV 6.0 query to find the oldest and newest item in each user's archive

Created: 15 Nov 2013 • Updated: 20 Nov 2013 | 6 comments
Saswata Basu's picture
This issue has been solved. See solution.

Hi All

I need a SQL query to find the oldest and newest item in each user's archive. I found the solution in the post below :-

https://www-secure.symantec.com/connect/forums/ev-902-email-archiving

However it does not work for EV 6.0 which we are using. I find the IndexVolume table in Directory database is not present however the Oldest and Newest item data is contained in the 'Index' Table for 6.0.

If anybody can help me create a query for EV 6.0 I would greatly appreciate. I need to get this data out for legal lawsuit ASAP. Let me know if there is any other way to get this data from EV.

Thanks in Advanvce

 

Operating Systems:

Comments 6 CommentsJump to latest comment

JesusWept3's picture

Probably have to use the Saveset table
Like

SELECT A.Archivename, MAX(s.iddatetime) "Oldest item, MIN(s.iddatetime) "Newest Item"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
YourVaultStore.dbo.ArchivePoint AP
YourVaultStore.dbo.Saveset S
WHERE S.Archivepointidentity = AP.Archivepointidentity
AND AP.Archivepointidentity = R.VaultEntryId
AND R.Rootidentity = A.rootidentity
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName

Will need to replace 'yourVaultStore' with the. And of the vs database and will need to run that query for each vault store you have

Saswata Basu's picture

Thanks a ton Jesus for the quick reponse. I will try this and let us know.

Rob.Wilcox's picture

Did it work? If not let us know, and if it did then mark the forum post as solved, please.

Saswata Basu's picture

Hi Rob

The query by JesusWept did not work hence we involved our own SQL DBA to modify the script which queries the Index table.

Following is the modified query that worked for EV 6.0 databases :-

SELECT A.ArchiveName "Archive Name",
AP.ArchivedItems "No. Archived Items",
AP.ArchivedItemsSize "Size of Archive (KB)",
AP.ArchivedItemsSize/1024 "Size Of Archive (MB)",
IV.OldestItem "Oldest Item (Sent)",
IV.YoungestItem "Newest Item (Sent)"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
[EnterpriseVaultDirectory].[dbo].[Index] IV,
yourvaultstoreDB.dbo.ArchivePoint AP
WHERE A.RootIdentity = R.RootIdentity
AND R.RootIdentity = IV.RootIdentity
AND R.VaultEntryId = AP.ArchivePointId
ORDER BY ArchiveName

Hope this will other people still stuck on old EV 6.0 environment.. Please mark this as a solution.

SOLUTION
JesusWept3's picture

Well if you could have let us know what didn't work; could have corrected it for you, that being said, for a legal lawsuit I wouldn't recommend querying the index table as it can be very inaccurate, only the Saveset table is accurate

SHI-CRO's picture

I used the first query (parsed fine after adding a comma and close quotes):

SELECT A.Archivename, MAX(s.IdDateTime) "Oldest item", MIN(s.IdDateTime) "Newest Item"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
evvsmailvaultstore_1.dbo.ArchivePoint AP,
evvsmailvaultstore_1.dbo.Saveset S
WHERE S.Archivepointidentity = AP.Archivepointidentity
AND AP.Archivepointidentity = R.VaultEntryId
AND R.Rootidentity = A.rootidentity
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName

But I'm getting an error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '10001950C743E2D4896831333AA21DFA41110000vaultserver1' to data type int.