Ayuda de vídeo de Screencast
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

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
el cuadro de los Saswata Basu
Se ha solucionado este problema. Vea la solución.

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:

Comentarios ComentariosIr al último comentario

el cuadro de los JesusWept3

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

el cuadro de los Saswata Basu

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

el cuadro de los Rob.Wilcox

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

el cuadro de los Saswata Basu

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.

SOLUCIÓN
el cuadro de los JesusWept3

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

el cuadro de los SHI-CRO

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.