Mailbox Emails Report

max348

I am trying to create a report for a specific mailbox that will show number of emails sent and received internally and externally. Also is it possible to see message size for each email, email of sender, message subject, etc ??

I was trying out the below script i found on one of the posts but it show every thing as '0' for message sent internally and externally. It show data only for "mb.MessagesSentLastSevenDays" field.  Would appreciate if anyone could help.

SELECT TOP 1000

    a.Name,

    a.Status as [ApplicationStatus],

    a.DetailsUrl as [ApplicationDetailsUrl],

    mb.DisplayName,

  mb.MessagesSentInternal as [Sent Internal],

  mb.MessagesSentInternalSize  as [Sent Internal Size],

    mb.MessagesSentExternal as [Sent External],

     mb.MessagesSentExternalSize as [Sent External Size],

      mb.MessagesReceivedInternal as [Received Internal],

      mb.MessagesReceivedInternalSize as [Received Internal Size]

      ,mb.MessagesReceivedExternal as [Received External]

      ,mb.MessagesReceivedExternalSize as [Received External Size],

    mb.Status as [MailboxStatus],

    mb.DetailsUrl as [MailboxDetailsUrl],

    '<a href="mailto:' + mb.PrimarySmtpAddress + '">' + mb.PrimarySmtpAddress + '</a>' as [PrimarySmtpAddress],

    mb.Type,

    mb.MessagesSentLastSevenDays,

    mb.Database.Identity,

    mb.Database.Status as  [DatabaseStatus],

    mb.Database.DetailsUrl as [DatabaseDetailsUrl]

FROM Orion.APM.Exchange.Mailbox mb

JOIN Orion.APM.Exchange.DatabaseCopy dc ON dc.DatabaseID = mb.DatabaseID AND dc.IsActive = 1

JOIN Orion.APM.Exchange.Application a ON a.ApplicationID = dc.ApplicationID

ORDER BY MessagesSentLastSevenDays DESC

max348

OK managed to get it working with another script i found on a post with little modification. The original Script was for a Month report (Report for Exchange data from AppInsight ) . I want to get a report for each day for Last Seven Days. Can anyone help me out here??

Also is it possible to show the message subject, Sender name, Recipient Name etc. on the same report??

SELECT top 1000

      [APM_ExBB_Mailbox].[DisplayName] as [Display Name]

      ,[APM_ExBB_Mailbox].[UserLogonName] as [User Logon Name] ,

          [mbd].[AccountStatDate] as [Date]

      ,Sum([mbd].[MessagesSentInternal]) as [Sent Internal]

      ,Sum([mbd].[MessagesSentInternalSize]) as [Sent Internal Size]

      ,Sum([mbd].[MessagesSentExternal]) as [Sent External]

      ,Sum([mbd].[MessagesSentExternalSize]) as [Sent External Size]

      ,Sum([mbd].[MessagesReceivedInternal]) as [Received Internal]

      ,Sum([mbd].[MessagesReceivedInternalSize]) as [Received Internal Size]

      ,Sum([mbd].[MessagesReceivedExternal]) as [Received External]

      ,Sum([mbd].[MessagesReceivedExternalSize]) as [Received External Size]

   ,Sum(([mbd].[MessagesReceivedExternalSize])+([mbd].[MessagesReceivedInternalSize])) as [Total Received Size]

   ,Sum(([mbd].[MessagesSentExternalSize])+([mbd].[MessagesSentInternalSize])) as [Total Sent Size]

   ,Sum(([mbd].[MessagesSentInternal])+([mbd].[MessagesSentExternal])) as [Total Sent]

   ,Sum(([mbd].[MessagesReceivedInternal])+([mbd].[MessagesReceivedExternal])) as [Total Received]

FROM [dbo].[APM_ExBB_MailboxAccount_Detail] as [mbd]

  inner join [APM_ExBB_Mailbox] on [APM_ExBB_Mailbox].[ID]=[mbd].[MailboxID]

Where USERLOGONNAME = 'Username'

Group By

[APM_ExBB_Mailbox].[UserLogonName], [APM_ExBB_Mailbox].[DisplayName],[mbd].[AccountStatDate]