cancel
Showing results for 
Search instead for 
Did you mean: 

File System Archiving: File Server no longer on the network - want to know size of archives in EV

Ptolemee
Level 4
Partner Accredited Certified

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

1 ACCEPTED SOLUTION

Accepted Solutions

plaudone1
Level 6
Employee

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 

View solution in original post

4 REPLIES 4

plaudone1
Level 6
Employee

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 

Ptolemee
Level 4
Partner Accredited Certified

Many thanks, will give it a go

Ptolemee
Level 4
Partner Accredited Certified

The script worked well and returned all the results I wanted.

Much appreciated.

Great. Glad it worked! :)