09-21-2018 03:17 AM
Just upgraded from EV11.0.1 to EV12.3. An old file server that was archived was since decommissioned and no longer on the network but is still present in EV. Need to determine the size of archives from that file server (a breakdown per folder if possibe). Tried FSAUtility (in report mode) but did not work as it requires the original file being on the network (Dtrace shows attempt to conncet to it).
Is there any other way to determine the size of arcived items that came from a that file server? maybe an SQL script?
Thanks
Solved! Go to Solution.
09-21-2018 05:00 AM
Hi Ptolemee,
You should be able to use the following replacing the SET @ArchiveID = '11FAB9DF012C208408C1F1007904C12351110000EVServer' entry with the ID of your archive. You can take the results to Excel and get data by folders. The query should be run against the vault store database.
DECLARE @ArchiveName nvarchar(75)
DECLARE @ArchiveID nvarchar(75)
DECLARE @FolderID nvarchar(75)
DECLARE @Idtransaction nvarchar(40)
SET @ArchiveID = '1234B9DF012C208408C1F1007904C12351110000EVServer'
SET @ArchiveName = NULL
SET @FolderID = NULL
SET @Idtransaction = NULL
SELECT
Archive.ArchiveName,
R1.VaultEntryId as ArchiveID,
R2.VaultEntryId as FolderID,
SP.SavesetIdentity,
ArchivedDate,
IdTransaction,
ItemSize,
OriginalSize/1024 AS OriginalSizeKB,
FolderPath,
CASE
WHEN LEFT(sp.properties,1) = '<' and RIGHT(sp.properties,1) = '>' THEN SUBSTRING(properties, (CHARINDEX('<filename>', properties)+10), (CHARINDEX('</filename>', properties)-(CHARINDEX('<filename>', properties)+10)))
WHEN LEFT(sp.properties,6) = 'FSA/3/' THEN SUBSTRING(properties, 7, CHARINDEX('/', properties, 7)-7)
END AS Filename,
ss.CollectionIdentity,
RelativeFileName
FROM
EnterpriseVaultDirectory.dbo.root r1
JOIN EnterpriseVaultDirectory.dbo.Root r2 on r1.RootIdentity = r2.ContainerRootIdentity
JOIN EnterpriseVaultDirectory.dbo.archive on r1.RootIdentity = Archive.RootIdentity
JOIN EnterpriseVaultDirectory.dbo.ArchiveFolder on r2.RootIdentity = ArchiveFolder.RootIdentity
JOIN Vault on r2.VaultEntryId = Vault.Vaultid
JOIN Saveset ss on Vault.VaultIdentity = ss.VaultIdentity
JOIN SavesetProperty sp on ss.savesetidentity = sp.SavesetIdentity
LEFT JOIN Collection on ss.CollectionIdentity = Collection.CollectionIdentity
WHERE
r1.VaultEntryid= ISNULL(@ArchiveID, r1.VaultEntryid)
AND ArchiveName = ISNULL(@ArchiveName, ArchiveName)
AND r2.VaultEntryid = ISNULL(@FolderID, r2.VaultEntryId)
AND ss.IdTransaction = ISNULL(@Idtransaction, ss.IdTransaction)
ORDER BY
ArchivedDate DESC
Regards,
Patrick
09-21-2018 05:00 AM
Hi Ptolemee,
You should be able to use the following replacing the SET @ArchiveID = '11FAB9DF012C208408C1F1007904C12351110000EVServer' entry with the ID of your archive. You can take the results to Excel and get data by folders. The query should be run against the vault store database.
DECLARE @ArchiveName nvarchar(75)
DECLARE @ArchiveID nvarchar(75)
DECLARE @FolderID nvarchar(75)
DECLARE @Idtransaction nvarchar(40)
SET @ArchiveID = '1234B9DF012C208408C1F1007904C12351110000EVServer'
SET @ArchiveName = NULL
SET @FolderID = NULL
SET @Idtransaction = NULL
SELECT
Archive.ArchiveName,
R1.VaultEntryId as ArchiveID,
R2.VaultEntryId as FolderID,
SP.SavesetIdentity,
ArchivedDate,
IdTransaction,
ItemSize,
OriginalSize/1024 AS OriginalSizeKB,
FolderPath,
CASE
WHEN LEFT(sp.properties,1) = '<' and RIGHT(sp.properties,1) = '>' THEN SUBSTRING(properties, (CHARINDEX('<filename>', properties)+10), (CHARINDEX('</filename>', properties)-(CHARINDEX('<filename>', properties)+10)))
WHEN LEFT(sp.properties,6) = 'FSA/3/' THEN SUBSTRING(properties, 7, CHARINDEX('/', properties, 7)-7)
END AS Filename,
ss.CollectionIdentity,
RelativeFileName
FROM
EnterpriseVaultDirectory.dbo.root r1
JOIN EnterpriseVaultDirectory.dbo.Root r2 on r1.RootIdentity = r2.ContainerRootIdentity
JOIN EnterpriseVaultDirectory.dbo.archive on r1.RootIdentity = Archive.RootIdentity
JOIN EnterpriseVaultDirectory.dbo.ArchiveFolder on r2.RootIdentity = ArchiveFolder.RootIdentity
JOIN Vault on r2.VaultEntryId = Vault.Vaultid
JOIN Saveset ss on Vault.VaultIdentity = ss.VaultIdentity
JOIN SavesetProperty sp on ss.savesetidentity = sp.SavesetIdentity
LEFT JOIN Collection on ss.CollectionIdentity = Collection.CollectionIdentity
WHERE
r1.VaultEntryid= ISNULL(@ArchiveID, r1.VaultEntryid)
AND ArchiveName = ISNULL(@ArchiveName, ArchiveName)
AND r2.VaultEntryid = ISNULL(@FolderID, r2.VaultEntryId)
AND ss.IdTransaction = ISNULL(@Idtransaction, ss.IdTransaction)
ORDER BY
ArchivedDate DESC
Regards,
Patrick
09-24-2018 02:07 AM
Many thanks, will give it a go
09-24-2018 06:00 AM
The script worked well and returned all the results I wanted.
Much appreciated.
09-24-2018 06:04 AM
Great. Glad it worked! :)