Forum Discussion

GertjanA's avatar
GertjanA
Moderator
9 years ago

EV9 SQL query assistance

Hello SQL guru's,

I have a query to find archivename, items, compressed/uncompressed size from EV SQL.

I would like to add to this line the Mbxdisplayname and MBXAlias (from the ExchangeMailboxEntry table from the Directory database).

Can anyone please assist in getting the appropriate sql in the below query?

I've been trying, but with my limited SQL knowledge fail to get this in one query.

The query I have is:

SELECT A.ArchiveName "Archive Name",
       COUNT(S.IdTransaction) AS ArchivedItemCount,
       SUM(S.ItemSize)/1024 "Compressed Size (MB)",
       SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       EVMBXVaultStore01.dbo.ArchivePoint AP,
       EVMBXVaultStore01.dbo.Saveset S,
       EVMBXVaultStore01.dbo.SavesetProperty SP
WHERE  A.RootIdentity = R.RootIdentity
  AND  R.VaultEntryId = AP.ArchivePointId
  AND  AP.ArchivePointIdentity = S.ArchivePointIdentity
  AND  S.SavesetIdentity = SP.SavesetIdentity
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName

Thanks!

GJ

  • HI Gertjan,

    Added reqquested column in your query iteseff.


    SELECT
           e.MbxAlias,
           e.MbxDisplayName,
            A.ArchiveName "Archive Name",
           COUNT(S.IdTransaction) AS ArchivedItemCount,
           SUM(S.ItemSize)/1024 "Compressed Size (MB)",
           SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
    FROM   EnterpriseVaultDirectory.dbo.Archive A,
           EnterpriseVaultDirectory.dbo.Root R,
            ArchivePoint AP,
            Saveset S,
           SavesetProperty SP,
           EnterpriseVaultDirectory.dbo. ExchangeMailboxEntry E
    WHERE  A.RootIdentity = R.RootIdentity
      AND  R.VaultEntryId = AP.ArchivePointId
      AND  AP.ArchivePointIdentity = S.ArchivePointIdentity
      AND  S.SavesetIdentity = SP.SavesetIdentity
      AND e.DefaultVaultId = r.VaultEntryId
    GROUP BY A.ArchiveName, e.MbxAlias, e.MbxDisplayName
    ORDER BY A.ArchiveName

     

2 Replies

  • HI Gertjan,

    Added reqquested column in your query iteseff.


    SELECT
           e.MbxAlias,
           e.MbxDisplayName,
            A.ArchiveName "Archive Name",
           COUNT(S.IdTransaction) AS ArchivedItemCount,
           SUM(S.ItemSize)/1024 "Compressed Size (MB)",
           SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
    FROM   EnterpriseVaultDirectory.dbo.Archive A,
           EnterpriseVaultDirectory.dbo.Root R,
            ArchivePoint AP,
            Saveset S,
           SavesetProperty SP,
           EnterpriseVaultDirectory.dbo. ExchangeMailboxEntry E
    WHERE  A.RootIdentity = R.RootIdentity
      AND  R.VaultEntryId = AP.ArchivePointId
      AND  AP.ArchivePointIdentity = S.ArchivePointIdentity
      AND  S.SavesetIdentity = SP.SavesetIdentity
      AND e.DefaultVaultId = r.VaultEntryId
    GROUP BY A.ArchiveName, e.MbxAlias, e.MbxDisplayName
    ORDER BY A.ArchiveName