Screencasts - Hilfsvideos

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

Created: 15 Nov. 2013 • Aktualisiert: 20 Nov. 2013 | 6 Kommentare
das Bild der Saswata Basus
Dieses Problem wurde gelöst. Siehe Lösung.

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:

Kommentare KommentareZum neuesten Kommentar

das Bild der JesusWept3s

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

das Bild der Saswata Basus

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

das Bild der Rob.Wilcoxs

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

das Bild der Saswata Basus

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.

LÖSUNG
das Bild der JesusWept3s

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

das Bild der SHI-CROs

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.