β06-04-2013 05:28 AM
hello,
back in may i posted a question which resolved my issue by a user(Tony Sterling) posted script, pls see below.
this worked in ev8 no problem but i need it to work in ev6, i get the following error when running the script in sql
What version are you? If running EV 10 sp2 there is a nice archiving report created after the schedule completes.
As for Retention categories, you could use this sql, it will run against the Vault Store database and you need to place the name of the archive where it says Your ArchiveName.
If you comment that line out it will run against all achives but may take some time.
SELECT A.ArchiveName
,CAST(AF.FolderPath AS NVARCHAR(MAX))
,AF.FolderName
,COUNT(S.IdTransaction) "Number of Archived Items"
,rc.RetentionCategoryName AS "Retention Category"
-- ,SUM(S.ItemSize) "Total Size (KB)"
FROM Saveset S,
ArchivePoint Ap,
Vault V,
EnterpriseVaultDirectory.dbo.Root R,
EnterpriseVaultDirectory.dbo.Root R2,
EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc
WHERE S.VaultIdentity = V.VaultIdentity
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointID = R.VaultEntryID
AND V.VaultID = R2.VaultEntryID
AND R.RootIdentity = A.RootIdentity
AND R2.RootIdentity = AF.RootIdentity
AND rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
AND A.ArchiveName = 'Your ArchiveName'
GROUP BY A.ArchiveName, CAST(AF.FolderPath AS NVARCHAR(MAX)),AF.FolderName, rc.RetentionCategoryName
Solved! Go to Solution.
β06-04-2013 08:11 AM
Not entirely sure why you need the above SQL query, however the one below should achieve what you have above as well:
select afv.ArchiveName,emaf.FolderName,emaf.FolderPath,rc.RetentionCategoryName,ssrv.RCCount as ArcItemCount from Vault v
inner join
(select VaultIdentity,retentioncategoryidentity,count(*) as RCCount from saveset ss1
group by ss1.VaultIdentity,ss1.RetentionCategoryIdentity) ssrv
on v.VaultIdentity = ssrv.VaultIdentity
inner join EnterpriseVaultDirectory..ExchangeMbxArchiveFolderview emaf on emaf.VaultEntryId = v.VaultID
inner join EnterpriseVaultDirectory..ArchiveView afv on afv.VaultEntryId = emaf.ArchiveVEID
inner join EnterpriseVaultDirectory..RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = ssrv.RetentionCategoryIdentity
order by afv.ArchiveName
β06-04-2013 05:55 AM
β06-04-2013 06:07 AM
tried it and no good.
what would that look like?
thanks
β06-04-2013 06:13 AM
It would look like this:
SELECT A.ArchiveName
,AF.FolderPath
,AF.FolderName
,COUNT(S.IdTransaction) "Number of Archived Items"
,rc.RetentionCategoryName AS "Retention Category"
-- ,SUM(S.ItemSize) "Total Size (KB)"
FROM Saveset S,
ArchivePoint Ap,
Vault V,
EnterpriseVaultDirectory.dbo.Root R,
EnterpriseVaultDirectory.dbo.Root R2,
EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.ArchiveFolder AF,
EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc
WHERE S.VaultIdentity = V.VaultIdentity
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointID = R.VaultEntryID
AND V.VaultID = R2.VaultEntryID
AND R.RootIdentity = A.RootIdentity
AND R2.RootIdentity = AF.RootIdentity
AND rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
AND A.ArchiveName = 'Your ArchiveName'
GROUP BY A.ArchiveName, AF.FolderPath, AF.FolderName, rc.RetentionCategoryName
β06-04-2013 07:12 AM
β06-04-2013 07:16 AM
β06-04-2013 07:46 AM
sql server 8.0.2039
i know its old!!
we just need to get the data off.
thanks
β06-04-2013 08:01 AM
for clarification, do you need to get the data out of EV and put it somewhere else (PST, Exchange, another archive system) or just stats about the data for reporting purposes?
β06-04-2013 08:11 AM
Not entirely sure why you need the above SQL query, however the one below should achieve what you have above as well:
select afv.ArchiveName,emaf.FolderName,emaf.FolderPath,rc.RetentionCategoryName,ssrv.RCCount as ArcItemCount from Vault v
inner join
(select VaultIdentity,retentioncategoryidentity,count(*) as RCCount from saveset ss1
group by ss1.VaultIdentity,ss1.RetentionCategoryIdentity) ssrv
on v.VaultIdentity = ssrv.VaultIdentity
inner join EnterpriseVaultDirectory..ExchangeMbxArchiveFolderview emaf on emaf.VaultEntryId = v.VaultID
inner join EnterpriseVaultDirectory..ArchiveView afv on afv.VaultEntryId = emaf.ArchiveVEID
inner join EnterpriseVaultDirectory..RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = ssrv.RetentionCategoryIdentity
order by afv.ArchiveName
β06-04-2013 09:16 AM
andrewb: both but right now need to see the what in there as it pertains to folders and retention.
ghost: the script above gave me the user name, and all there folders, with the retention of each folder
thanks