cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query - number of archived items per archive

Sarah_Seftel1
Level 6
Partner Accredited

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 ACCEPTED SOLUTION

Accepted Solutions

Rob_Wilcox1
Level 6
Partner

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 6

Rob_Wilcox1
Level 6
Partner

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

Rob_Wilcox1
Level 6
Partner

Sarah did this work?

Working for cloudficient.com

Sarah_Seftel1
Level 6
Partner Accredited

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

 

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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

Rob_Wilcox1
Level 6
Partner

Yeah.. what he said :)

Working for cloudficient.com

Sarah_Seftel1
Level 6
Partner Accredited

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