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
Highlighted

Re: Input around SQL Queries

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

For the best O365 Reporting Solution click here Radar-Reporting

Re: Input around SQL Queries

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