12-01-2014 03:45 AM
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
Solved! Go to Solution.
12-01-2014 04:22 AM
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
12-01-2014 04:22 AM
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
12-03-2014 07:36 AM
Sarah did this work?
12-03-2014 12:33 PM
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
12-03-2014 10:57 PM
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 :)
12-03-2014 11:22 PM
Yeah.. what he said :)
12-04-2014 03:53 AM
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