3 Replies Latest reply on Jun 23, 2015 7:51 AM by zzsdad

    Report for Exchange data from AppInsight

    stevenstadel

      We are looking for a report of the total send and receive volume of email by user by month. I have edited the built in report Users By Messages Sent This Week to give us the same data for the last 30 days but I can not figure out how to do the same for received messages because there is no column for MessagesRecievedLastThirtyDays in either a View or Table.

       

      Can anyone help?

        • Re: Report for Exchange data from AppInsight
          zzsdad

          Hi,

           

          Did you ever work this out, I have the same issue?

           

          Cheers,

           

          Neil

            • Re: Report for Exchange data from AppInsight
              stevenstadel

              I created a custom SQL and put it in a report. Change the Month number to be the number of the month you are interested in, or take out that where clause all together to get a full history. You will also need to increase the default mailbox details in SAM Settings - Data and Database Details - Exchange Server Monitor - Retain Mailbox History

               

              This is the SQL.

               

              SELECT MONTH([mbd].[AccountStatDate]) as [Month]

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

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

                    ,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 [Orion].[dbo].[APM_ExBB_MailboxAccount_Detail] as [mbd]

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

                Where MONTH([mbd].[AccountStatDate]) = '5'

                  Group By

                    [APM_ExBB_Mailbox].[UserLogonName], [APM_ExBB_Mailbox].[DisplayName], MONTH([mbd].[AccountStatDate])