Forum Discussion

VirgilDobos's avatar
VirgilDobos
Moderator
9 months ago

EV FSA Query to list top folders

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.

ello
  • jack_liu's avatar
    jack_liu
    9 months ago

    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

6 Replies

Sort By
  • jack_liu's avatar
    jack_liu
    Level 2
    9 months ago

    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

  • plaudone1's avatar
    plaudone1
    Level 6
    9 months ago

    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's avatar
      VirgilDobos
      Moderator
      9 months ago

      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?

       

       

  • Prone2Typos's avatar
    Prone2Typos
    Moderator
    9 months ago

     

    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's avatar
      VirgilDobos
      Moderator
      9 months ago

      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.