Forum Discussion

Project_Man's avatar
Project_Man
Level 4
17 years ago

SQL Queries on vaults

Guys,

I have a query already for finding how many items where archived in a particular period of time, see below:

 

Select count(*),min(archiveddate),max(archiveddate)

from saveset where archiveddate >'2008-03-04 22:00'

and archiveddate < '2008-03-05 6:00'

 

Does anyone know a query to find the SIZE of the archived items in a particular period?

 

Currently I note the size every day of the active vault and compare between days.

Enterprise Vault
Information Governance
  • Joseph_Rodgers's avatar
    Joseph_Rodgers
    17 years ago

    Select count(*),min(archiveddate),max(archiveddate), sum(itemsize)

    from saveset where archiveddate >'2008-03-04 22:00'

    and archiveddate < '2008-03-05 6:00'

     

    regards

    Joe

  • Joseph_Rodgers's avatar
    Joseph_Rodgers
    Level 6
    17 years ago

    Select count(*),min(archiveddate),max(archiveddate), sum(itemsize)

    from saveset where archiveddate >'2008-03-04 22:00'

    and archiveddate < '2008-03-05 6:00'

     

    regards

    Joe

  • Project_Man's avatar
    Project_Man
    Level 4
    17 years ago

    Joe,

    Ran that report and got the following output:

     

     113536 - 2008-08-19 23:00:38.150 - 2008-08-20 04:04:38.500 - 10349846

     

    Now the 113536 is the total item numbers and the dates are self explanatory, however the final figure the itemsize is that the original size of the items before they where archived or is it the size of the items now in the vault?

  • MichelZ's avatar
    MichelZ
    Level 6
    17 years ago

    It's the Size after compression, I think.

    The OriginalSize in bytes is in the SavesetProperty table.

     

    /Michel

  • MichelZ's avatar
    MichelZ
    Level 6
    17 years ago

    Which leads us to:

     

    SELECT COUNT(*) AS 'Number of Items', MIN(ArchivedDate) as 'Date From', MAX (ArchivedDate) AS 'Date to', SUM(ItemSize)/1024 AS 'Saveset Size (MB)', SUM(OriginalSize)/1024/1024 AS 'Orignal Size (MB)'
    FROM Saveset INNER JOIN SavesetProperty ON Saveset.SavesetIdentity = SavesetProperty.SavesetIdentity WHERE ArchivedDate >'2008-03-04 22:00'
    AND ArchivedDate < '2008-03-05 6:00'

  • PCarroll's avatar
    PCarroll
    Level 3
    16 years ago
    Further to that, is there any way of running a SQL query to report on vault usage by indiviadual users and tying that information back to the individual Business Unit or Company Field in Active Directory?

    Any help, much appreciated.

    Paul.
  • MichelZ's avatar
    MichelZ
    Level 6
    16 years ago
    Like this:

    SELECT [ArchiveName], [Company], ArchivedItems, CAST(ArchivedItemsSize AS decimal(20, 0)) AS ArchivedItemsSize, ModifiedDate
    FROM view_ListVaults
    INNER JOIN [EnterpriseVaultDirectory].[dbo].[ArchiveView]
    ON view_ListVaults.ArchivePointId = [EnterpriseVaultDirectory].[dbo].[ArchiveView].[VaultEntryId]
    INNER JOIN [EnterpriseVaultDirectory].[dbo].[Root]
    ON [EnterpriseVaultDirectory].[dbo].[ArchiveView].[RootIdentity] = [EnterpriseVaultDirectory].[dbo].[Root].[RootIdentity]
    INNER JOIN [EnterpriseVaultDirectory].[dbo].[ExchangeMailboxEntry]
    ON [EnterpriseVaultDirectory].[dbo].[Root].[VaultEntryId] = [EnterpriseVaultDirectory].[dbo].[ExchangeMailboxEntry].[DefaultVaultId]
  • jprknight-oldax's avatar
    jprknight-oldax
    Level 6
    16 years ago

    Hi all,

    I have run the below SQL query and am slightly confused. I have created a vbscript which pretty much runs osql with the below script for each day since the beginning of 2008. Typically the amount of data archived every night is around 500MB. The contractor doing our PST migrations was getting around 18GB of data migrated a night so it looks as though this query only contains archive data from users, not from PST migrations.

    Does anyone have an idea on how to include PST migration data in this query?

    What I am trying to do is prove over a period of say the last year or so the load on our servers. There is a push from elsewhere in our business to move EV from a one-to-one relationship with Exchange to a Two-to-one relationship. So One EV server will service two Exchange servers. This is not something I want as I believe we will be creeping towards the point where we will breach the Symantec suggested limit of 5000 mailboxes per EV server.

    Thanks,

    Jeremy.

    SELECT COUNT(*) AS 'Number of Items', MIN(ArchivedDate) as 'Date From', MAX (ArchivedDate) AS 'Date to', SUM(ItemSize)/1024 AS 'Saveset Size (MB)', SUM(OriginalSize)/1024/1024 AS 'Orignal Size (MB)'
    FROM Saveset INNER JOIN SavesetProperty ON Saveset.SavesetIdentity = SavesetProperty.SavesetIdentity WHERE ArchivedDate >'2008-03-04 22:00'
    AND ArchivedDate < '2008-03-05 6:00'

  • jprknight-oldax's avatar
    jprknight-oldax
    Level 6
    16 years ago
    Thanks MichelZ,

    I see this now. I have seperated the numbers into six different csv files. Once I added them up they came out to the right figures. I will try and think a little more next time. :)

    Thanks,

    Jeremy.