EV FSA Query to list top folders

[VirgilDobos]

Hello

I am looking to create a SQL query to list the top folders archived on a particular archive or vault store, if possible.

For example: In the last 1 year, list the top 5 folders that contributed writing to archival storage.

I was able to list the archives, but not able to find a way to list the folders.

Thanks in advance for your insights.

[Prone2Typos]

I had this in my bag of tricks and do not have a lab to run it against. I think it brings a count per folder. I thought perhaps it could help your hunt. 

----This is to find items per folderpath





Use EnterpriseVaultStore ----------------replace with your file server vaultstore



declare @folderpath Varchar(300)



set @folderpath = '\\cs2.dodo1.local\users7\Userdata' ----- --replace your foldername there









select folderpath, PartitionRootPath



+ '\' + convert(char(4),datepart(year, IdDatetime))

+ '\' + case when datepart(month, IdDatetime) < 10 then '0' + convert(char(1),datepart(month, IdDatetime)) else convert(char(2),datepart(month, IdDatetime)) end

+ '\' + case when datepart(day, IdDatetime) < 10 then '0' + convert(char(1),datepart(day, IdDatetime)) else convert(char(2),datepart(day, IdDatetime)) end

+ '\' + case when datepart(hour, IdDatetime) < 10 then '0' + convert(char(1),datepart(hour, IdDatetime)) else convert(char(2),datepart(hour, IdDatetime)) end

+ '\' + LEFT(CAST(idchecksumhigh as varchar(7))+'0000000', 7)

+ left (CAST(idchecksumlow as varchar(8)) + '00000000', 8)

+ '~'

+ REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(200),iddatetime,121),'.',''),':',''),'-',''),' ','') + '0~'

+ CAST (iduniqueno as varchar (1))

+ '.dvs'



as dvs_path, idtransaction from saveset

join vault on saveset.vaultidentity = vault.vaultidentity

join enterprisevaultdirectory..root on vault.vaultid = enterprisevaultdirectory..root.vaultentryid

join enterprisevaultdirectory..archivefolder on enterprisevaultdirectory..root.rootidentity = enterprisevaultdirectory..archivefolder.rootidentity

join EnterpriseVaultDirectory..VaultEntryView VaultEntryView on Vault.VaultId = VaultEntryView.VaultEntryId

join EnterpriseVaultDirectory..PartitionEntry PartitionENtry on Saveset.IdPartition = PartitionEntry.IdPartition and PartitionEntry.VaultStoreEntryId = VaultEntryView.VaultStoreEntryId



where folderpath like @folderpath

Perhaps it can send you in the right direction?

[VirgilDobos]

Thank you, @Prone2Typos.

Tested the query and it shows the folder and the vault store partition location where the items are stored.

I am looking to create a query that lists the top 5 folders that contributed writing to archival storage during the last 1 month for an entire file server/vault store.

[plaudone1]

Hi Virgil,

This may work for you. You can add the Archive Name or Id in one of the SET lines to perform against just that archive. Otherwise it will run against the entire vault store.  You will then need to take the results to Excel and perform some sorting and filtering and such.  This will only pull items with an archived date greater than GETUTCDATE() -30.  

USE VAULT_STORE_DB_NAME -- REPLACE WITH CORRECT VS DB NAME 

DECLARE @ArchiveName nvarchar(75)

DECLARE @ArchiveID nvarchar(75)

DECLARE @FolderID nvarchar(75)

DECLARE @Idtransaction nvarchar(40)

SET @ArchiveID = NULL

SET @ArchiveName = NULL

SET @FolderID = NULL

SET @Idtransaction = NULL

Select

Archive.ArchiveName,

FolderPath,

cast(FolderPath as nvarchar(max)) + '\' +

CASE 

        WHEN LEFT(sp.properties,2) = '<?' 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

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

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)

AND ss.ArchivedDate > getutcdate()-30 

Regards,

Patrick 

[VirgilDobos]

Thank you, Plaudone1!

The query is closer to what I need indeed, it lists all folders and the files inside the folders. 

It would be great if instead of listing all files, it would show the count of files per folder or even better the size of files in each folder.

Could you help with this?

[jack_liu · Accepted solution]

The script can be an easy one based on Plaudone1's (run it against your FSA VS DB and assume the EVD db is also on the same SQL instance):

SELECT TOP 5
FolderPath,
SUM(ItemSize)/1024 AS 'Archived Size (MB)'
FROM
EnterpriseVaultDirectory.dbo.root r1
JOIN EnterpriseVaultDirectory.dbo.Root r2 on r1.RootIdentity = r2.ContainerRootIdentity
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
WHERE ss.ArchivedDate BETWEEN '2020-08-03 23:03:00.000' AND '2022-08-03 23:03:00.000'
GROUP BY
FolderPath
ORDER BY
'Archived Size (MB)' desc

Just update the date range as you wish, or as you asked, a year for the current time.
SELECT TOP 5
FolderPath,
SUM(ItemSize)/1024 AS 'Archived Size (MB)'
FROM
EnterpriseVaultDirectory.dbo.root r1
JOIN EnterpriseVaultDirectory.dbo.Root r2 on r1.RootIdentity = r2.ContainerRootIdentity
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
WHERE ss.ArchivedDate <DATEADD(YEAR, -1, GETUTCDATE())
GROUP BY
FolderPath
ORDER BY
'Archived Size (MB)' desc

[VirgilDobos]

Thank you Jack!