SQL Queries on vaults

[Project_Man]

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.

[Joseph_Rodgers · Accepted solution]

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]

Excellent, much appreciated.

[Project_Man]

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]

It's the Size after compression, I think.

The OriginalSize in bytes is in the SavesetProperty table.

/Michel

[MichelZ]

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]

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]

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]

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'

[MichelZ]

Jeremy

This covers PST Migrations, too.

Cheers

[jprknight-oldax]

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.

[Liam_Finn1]

If you have many databases to query you can use the USE command in SQL to specify each of the databases

 USE database1

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'
UNION

USE database 2
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'

This will query all the databsese and then UNION the results into one output

[jprknight-oldax]

Thanks Scanner001!

Excellent.