06-11-2019 12:13 AM
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
06-14-2019 09:44 AM
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
06-18-2019 01:55 AM - edited 06-18-2019 01:59 AM
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