Forum Discussion

BruceCrankshaw1's avatar
BruceCrankshaw1
Level 3
6 years ago

Input around SQL Queries

Hi All

BCS support offers some excellent analysis on a customers environment. I was wondering what would be the SQL queries or most effective tables in SQL  to get the information below. Some of these I already know but is there a report that covers these or should I use SQL queries that are specific to each query?

 

Not Enabled For Archiving

 

Re-linked Mailbox

 

Hidden Mailbox ,  Disabled for Archiving

 

Deleted Mailbox ,  Enabled For Archiving

 

Hidden Mailbox ,  Re-linked Mailbox

 

Hidden Mailbox ,  Not Enabled For Archiving

 

Enabled For Archiving

 

Deleted Mailbox ,  Disabled for Archiving

 

Disabled for Archiving

 

Hidden Mailbox ,  Enabled For Archiving

 

Deleted Mailbox ,  Re-linked Mailbox

 

Thanks

2 Replies

Sort By
  • TonySterling's avatar
    TonySterling
    Moderator
    6 years ago

    This is one that I use: 

    SELECT ex.MbxDisplayName, ex.ADMbxFlags, ex.MbxExchangeState, ex.MbxArchivingState, a.archiveditems as Count, "Archived Size" =sum(Convert(bigint,s.itemSize))/1024

    FROM Saveset s Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity

    Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID

    Join SavesetProperty SP on SP.SavesetIdentity = s.SavesetIdentity

    GROUP BY ex.MbxDisplayName, ex.ADMbxFlags, ex.MbxExchangeState, ex.MbxArchivingState, a.archiveditems,a.archiveditemssize

     

     

    admbxflags 

    0 = normal

    1 = mailbox is hidden

    2 = AD account is disabled

    3 = mailbox is hidden and AD account is disabled

     

    MBXExchangeState

    0 = Normal 

    1 = Hidden 

    2 = Deleted

     

    ArchivingState

    0 = Not Enabled 

    1 = Enabled 

    2 = Disabled 

    3 = Re-Link

    • CConsult's avatar
      CConsult
      Moderator
      6 years ago

      I have added the different states within the output:

      ------------------

      SELECT 
      ex.MbxDisplayName,
      CASE ex.ADMbxFlags
      WHEN 0 THEN 'normal'
      WHEN 1 THEN 'mailbox hidden'
      WHEN 2 THEN 'AD account disabled'
      WHEN 3 THEN 'mailbox hidden and AD account disabled'
      ELSE cast(ex.ADMbxFlags as varchar(255))
      END AS ADFlag,

      CASE ex.MbxExchangeState
      WHEN 0 THEN 'normal'
      WHEN 1 THEN 'hidden'
      WHEN 2 THEN 'deleted'
      ELSE cast(ex.MbxExchangeState as varchar(255))
      END AS EXCHMbxFlag,

      CASE ex.MbxArchivingState
      WHEN 0 THEN 'not enabled'
      WHEN 1 THEN 'enabled'
      WHEN 2 THEN 'disabled'
      WHEN 3 THEN 're-link'
      ELSE cast(ex.MbxArchivingState as varchar(255))
      END AS ARCHMbxFlag,

      a.archiveditems as Count, "Archived Size" =sum(Convert(bigint,s.itemSize))/1024

      FROM EVVSMailbox_1.dbo.Saveset s Join EVVSMailbox_1.dbo.Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity

      Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID

      Join EVVSMailbox_1.dbo.SavesetProperty SP on SP.SavesetIdentity = s.SavesetIdentity

      GROUP BY ex.MbxDisplayName,
      ex.ADMbxFlags, ex.MbxExchangeState, ex.MbxArchivingState, a.archiveditems,a.archiveditemssize

      -------------

      you will need to change " EVVSMailbox_1" to your database name within the query