cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query - number of archived items per archive

Hi,

Need an assistance with an SQL query that will bring the following result:

list of all archives, number of total items archived for archive, number of items archived per archive for 2014 only, and size of all items in the the archive.

I found different queries here, but I do not know SQL that good, so I was unable to join them :(

Thanks,

Sarah

1 Solution

Accepted Solutions
Accepted Solution!

This might

This might work:

 

SELECT
EME.MbxDisplayName "Mailbox Name",
PTG.DisplayName "Provisioning Group",
COUNT(S.ItemSize) "No. Items Archived",
SUM(S.ItemSize)/1024 "Archived Item Size (MB)",
SUM(SP.OriginalSize)/1024/1024 "Original Item Size (MB)",
SUM(CASE when CAST(Year(ArchivedDate) as int) = 2014 then 1 else 0 end) as 'Num in 2014'
FROM
dbo.Saveset S,
dbo.SavesetProperty SP,
dbo.ArchivePoint AP,
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG
WHERE
S.SavesetIdentity = SP.SavesetIdentity
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointID = EME.DefaultVaultId
AND EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId
GROUP BY EME.MbxDisplayName, PTG.DisplayName
ORDER BY PTG.DisplayName, EME.MbxDisplayName

Working for cloudficient.com

View solution in original post

6 Replies
Accepted Solution!

This might

This might work:

 

SELECT
EME.MbxDisplayName "Mailbox Name",
PTG.DisplayName "Provisioning Group",
COUNT(S.ItemSize) "No. Items Archived",
SUM(S.ItemSize)/1024 "Archived Item Size (MB)",
SUM(SP.OriginalSize)/1024/1024 "Original Item Size (MB)",
SUM(CASE when CAST(Year(ArchivedDate) as int) = 2014 then 1 else 0 end) as 'Num in 2014'
FROM
dbo.Saveset S,
dbo.SavesetProperty SP,
dbo.ArchivePoint AP,
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG
WHERE
S.SavesetIdentity = SP.SavesetIdentity
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointID = EME.DefaultVaultId
AND EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId
GROUP BY EME.MbxDisplayName, PTG.DisplayName
ORDER BY PTG.DisplayName, EME.MbxDisplayName

Working for cloudficient.com

View solution in original post

Sarah did this work?

Sarah did this work?

Working for cloudficient.com

Hi Rob,  Thanks as always

Hi Rob, 

Thanks as always :)

I encountered some problems when accessing the second SQL instance.

The scenario is:

Instance ilsqlev01\ev01 holds the following DB's:

EnterpriseVaultDirectory
EnterpriseVaultMonitoring
EVVSPTVMBXVS01_1
EVVSPTVMBXVS02_2
EVVSPTVMBXVS03_3
EVVSPTVMBXVS04_4
EVVSGPTVMBXVSG01_1_1

Instance ilsqlev02\ev02 holds the following DB's:

EVVSPTVMBXVS05_5
EVVSPTVMBXVS06_6
EVVSPTVMBXVS07_7

The query worked perfectly on instance ilsqlev01\ev01, but when I connected to the second instance and run the query I get an error saying : 
Msg 208, Level 16, State 1, Line 1, Invalid object name 'EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry'.

which makes sense since it cannot see the EnterpriseVaultDirectory DB.

How can I force it to read the EnterpriseVaultDirectory DB details from the 1st instance and still run it on the second instance for the rest of 3 storage groups DB's?

 

Many many thanks in advanced!

Sarah

 

Sarah, You need to use

Sarah,

You need to use "Linked Server" in a SQL query.

That needs to be configured in SQL, see https://www-secure.symantec.com/connect/forums/sql-query-required for other links.

I've done this previously, but had a DBA configure the linking for me :)

Regards. Gertjan

Yeah.. what he said :)

Yeah.. what he said :)

Working for cloudficient.com

Hi, Linked the servers,

Hi,

 

Linked the servers, added the first instance name before the EnterpriseVaultDirectory DB and it worked like a charm :)

 

Rob, thanks a lot! as always, your help is priceless.

 

Sarah