User Profile Analysis for Exchange Servers (Who needs the Exchange Profile Analyzer?)

%3CLINGO-SUB%20id%3D%22lingo-sub-484939%22%20slang%3D%22en-US%22%3EUser%20Profile%20Analysis%20for%20Exchange%20Servers%20(Who%20needs%20the%20Exchange%20Profile%20Analyzer%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-484939%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20All%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Esome%20years%20ago%26nbsp%3B%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3ENeil%20Johnson%20had%20%3C%2FSPAN%3E%20written%20an%20article%20about%20Exchange%20Sizing%20without%20the%20usage%20of%20the%20Exchange%20Profile%20Analyzer.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWithin%20the%20article%20was%20a%20script%20that%20is%20used%20to%20collect%20the%20data%20needed%20for%20a%20proper%20sizing%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20messages%20sent%20per%20mailbox%20per%20day%3C%2FP%3E%0A%3CP%3E-%20messages%20received%20per%20mailbox%20per%20day%3C%2FP%3E%0A%3CP%3E-%20average%20message%20size%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%3A%20This%20information%20is%20vital%20for%20performing%20good%20quality%20Exchange%20Server%20scaling.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20Neil%20has%20changed%20the%20position%20in%20MS%20I%20will%20try%20to%20maintain%20the%20script%20and%20the%20article%20for%20the%20future.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20now%20to%20the%20technical%20stuff%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20of%20the%20nice%20things%20since%20Exchange%202007%20is%20that%20we%20can%20interrogate%20the%20message%20tracking%20logs%20via%20PowerShell.%20This%20provides%20us%20with%20a%20nice%20way%20to%20query%20what%20the%20Exchange%20Server%20is%20doing.%20Usefully%20the%20message%20tracking%20logs%20include%20sufficient%20information%20for%20us%20to%20approximate%20our%20user%20profile%20data.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20author%20of%20this%20script%20is%20Rob%20Campbell%2C%20more%20about%20the%20scripts%20development%20can%20be%20found%20here%E2%80%A6%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20title%3D%22Hey%20scripting%20-%20use%20powershell%20to%20track%20email%20messages%20in%20Exchange%20Server%22%20href%3D%22http%3A%2F%2Fblogs.technet.com%2Fb%2Fheyscriptingguy%2Farchive%2F2011%2F03%2F02%2Fuse-powershell-to-track-email-messages-in-exchange-server.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EHey%20scripting%20-%20use%20powershell%20to%20track%20email%20messages%20in%20Exchange%20Server%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20script%20is%20maintained%20now%20at%26nbsp%3B%3CA%20title%3D%22GitHub%20MessageStats%20Repository%22%20href%3D%22https%3A%2F%2Fgithub.com%2Fmsftmroth%2FMessageStats%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2Fmsftmroth%2FMessageStats%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20script%20basically%20works%20by%20parsing%20the%20messaging%20tracking%20logs%20of%20your%20Exchange%20Servers%20and%20then%20tabulates%20the%20information%20into%20a%20CSV%20file%20for%20analysis%20in%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20provide%20some%20data%20to%20parse%20I%20configured%20a%20loadgen%20test%20against%2010%20mailboxes%20with%20a%20heavy%20profile%2C%20this%20should%20approximate%20to%20around%2080%20messages%20received%20and%2020%20sent%20per%20user.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20MessageStats%20script%20has%20a%20single%20command%20line%20parameter%20which%20controls%20how%20many%20days%20back%20it%20will%20look%20in%20the%20tracking%20logs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20script%20only%20parses%20a%20single%20days%20worth%20of%20data%2C%20the%20value%20you%20provide%20define%20in%20the%20script%20which%20day%20to%20process%2C%20so%201%20will%20process%20yesterdays%20logs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110574i602FF91B21D415E3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%225661_080911_1617_UserProfile1.png%22%20title%3D%225661_080911_1617_UserProfile1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%20margin%3A%200px%3B%22%3E%3CIMG%20style%3D%22border-image-outset%3A%200%3B%20border-image-repeat%3A%20stretch%3B%20border-image-slice%3A%20100%25%3B%20border-image-source%3A%20none%3B%20border-image-width%3A%201%3B%20box-sizing%3A%20border-box%3B%20display%3A%20block%3B%20height%3A%20auto%3B%20max-width%3A%20100%25%3B%20-ms-user-select%3A%20none%3B%20vertical-align%3A%20middle%3B%20border%3A%200px%20none%20currentColor%3B%22%20border%3D%220%22%20%2F%3E%20Now%20we%20have%20our%20CSV%20file%20that%20we%20can%20open%20in%20Microsoft%20Excel%2C%20however%20the%20data%20required%20some%20work%20before%20we%20can%20get%20our%20EPA%20values.%20The%20following%20screenshot%20shows%20the%20raw%20data%20open%20in%20Excel.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110575iE3093F49581EB0F6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%227750_2011-08-09_1244_34CA617F.png%22%20title%3D%227750_2011-08-09_1244_34CA617F.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20Highlight%20cell%20A1%3C%2FP%3E%0A%3CP%3E2.%20Press%20CTRL%2BSHIFT%2BEND%3C%2FP%3E%0A%3CP%3E3.%20Click%20on%20the%20INSERT%20Menu%3C%2FP%3E%0A%3CP%3E4.%20Click%20on%20the%20TABLE%20button%3C%2FP%3E%0A%3CP%3E5.%20Click%20on%20OK%3C%2FP%3E%0A%3CP%3E6.%20Open%20the%20DESIGN%20Menu%3C%2FP%3E%0A%3CP%3E7.%20Check%20the%20%22Total%20Row%22%20checkbox%3C%2FP%3E%0A%3CP%3E8.%20Hide%20columns%20C%2CD%2CE%2CH%2CI%2CJ%2CK%2CL%2CM%2CN%2CO%2CR%2CS%2CT%2CU%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20should%20now%20have%20a%20table%20with%20the%20following%20columns%E2%80%A6%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20Date%3C%2FP%3E%0A%3CP%3E-%20User%3C%2FP%3E%0A%3CP%3E-%20Received%20Total%3C%2FP%3E%0A%3CP%3E-%20Received%20MB%20Total%3C%2FP%3E%0A%3CP%3E-%20Sent%20Unique%20Total%3C%2FP%3E%0A%3CP%3E-%20Sent%20Unique%20MB%20Total%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20741px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110581iA3C002FE4922914F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221817_2011-08-09_1254_280C42FC.png%22%20title%3D%221817_2011-08-09_1254_280C42FC.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%3A%20Due%20to%20my%20test%20lab%20being%20very%20small%20I%20have%20added%20a%20filter%20to%20remove%20any%20non-loadgen%20accounts%20from%20the%20data%20analysis.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20Total%20row%20at%20the%20bottom%20of%20your%20table%20add%20%22AVERAGE%22%20subtotals%20for%20%22Received%20Total%22%20and%20%22Sent%20Unique%20Total%22.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20741px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110582iD099FD23D3745390%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%227367_2011-08-09_1304_3F4721A0.png%22%20title%3D%227367_2011-08-09_1304_3F4721A0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3EIn%20the%20%22Received%20MB%20Total%22%20column%20total%20cell%2C%20add%20in%20an%20%22AVERAGE%22%20subtotal%2C%20then%20edit%20the%20formula%20in%20the%20cell%20and%20divide%20that%20value%20by%20the%20Total%20Row%20average%20for%20%22Received%20Total%22%2C%20then%20multiply%20the%20result%20by%201024%20%E2%80%93%20this%20will%20report%20the%20average%20message%20size%20in%20KB.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20747px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110583iAB03CDA6106ADC90%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222018_2011-08-09_1303_296D1376.png%22%20title%3D%222018_2011-08-09_1303_296D1376.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20cursor%3A%20text%3B%20font-family%3A%20inherit%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3EIn%20the%20%22Sent%20Unique%20MB%20Total%22%20column%20total%20cell%2C%20add%20in%20an%20%22AVERAGE%22%20subtotal%2C%20then%20edit%20the%20formula%20in%20the%20cell%20and%20divide%20that%20value%20by%20the%20Total%20Row%20average%20for%20%22Sent%20Unique%20Total%22%2C%20then%20multiply%20the%20result%20by%201024%20%E2%80%93%20this%20will%20report%20the%20average%20message%20size%20in%20KB.%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20765px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110584i075025F6E4F1FC0C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221854_2011-08-09_1308_273FEEE0.png%22%20title%3D%221854_2011-08-09_1308_273FEEE0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20now%20have%20all%20of%20the%20information%20that%20we%20require%E2%80%A6%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20745px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110585i6A6D4C8B1A19D4BD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%220211_2011-08-09_1312_2BC5D3CD.png%22%20title%3D%220211_2011-08-09_1312_2BC5D3CD.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20%3CSTRONG%3EMessages%20Received%20per%20Mailbox%20Per%20Day%3C%2FSTRONG%3E%20%3D%20Received%20Total%20%3D%20%3CSTRONG%3E68%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E-%20%3CSTRONG%3EMessages%20Sent%20per%20Mailbox%20Per%20Day%3C%2FSTRONG%3E%20%3D%20Sent%20Unique%20Total%20%3D%20%3CSTRONG%3E17%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E-%20%3CSTRONG%3EAverage%20Message%20Size%3C%2FSTRONG%3E%20%3D%20Average%20of%20Received%20MB%20Total%20%26amp%3B%20Sent%20Unique%20MB%20Total%20(27.37%2B28.5)%2F2%20%3D%20%3CSTRONG%3E27.94KB%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20using%20this%20technique%20we%20have%20managed%20to%20approximate%20our%20user%20profile%20to%20a%20fair%20degree%20of%20accuracy%20without%20needing%20to%20logon%20to%20any%20mailboxes!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20suspect%20that%20this%20method%20is%20accurate%20to%20around%20%2B%2F-%2010%25%20which%20is%20totally%20acceptable%20in%20this%20context.%20Obviously%20there%20is%20a%20caveat%20here%20that%20I%20have%20only%20performed%20some%20rudimentary%20testing%20in%20a%20fairly%20small%20lab%20environment%2C%20so%20if%20you%20do%20run%20this%20in%20production%20and%20find%20that%20it%20generates%20weird%20results%2C%20or%20that%20it%20validates%20your%20already%20proven%20EPA%20data%2C%20then%20feel%20free%20to%20drop%20me%20a%20note%20to%20let%20me%20know.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20to%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9998%22%20target%3D%22_blank%22%3E%40Frank%20Plawetzki%3C%2FA%3E%26nbsp%3B%20for%20checking%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EShortcut%20for%20this%20post%3A%26nbsp%3B%3CA%20title%3D%22http%3A%2F%2Faka.ms%2FNoEPA%22%20href%3D%22http%3A%2F%2Faka.ms%2FNoEPA%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Faka.ms%2FNoEPA%3C%2FA%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-484939%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExchange%20Server%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Microsoft

Hey All,

 

some years ago Neil Johnson had written an article about Exchange Sizing without the usage of the Exchange Profile Analyzer.

 

Within the article was a script that is used to collect the data needed for a proper sizing:

 

- messages sent per mailbox per day

- messages received per mailbox per day

- average message size

 

Note: This information is vital for performing good quality Exchange Server scaling.

 

After Neil has changed the position in MS I will try to maintain the script and the article for the future.

 

And now to the technical stuff:

 

One of the nice things since Exchange 2007 is that we can interrogate the message tracking logs via PowerShell. This provides us with a nice way to query what the Exchange Server is doing. Usefully the message tracking logs include sufficient information for us to approximate our user profile data.

 

The author of this script is Rob Campbell, more about the scripts development can be found here… 

 

Hey scripting - use powershell to track email messages in Exchange Server

 

The script is maintained now at https://github.com/msftmroth/MessageStats

 

The script basically works by parsing the messaging tracking logs of your Exchange Servers and then tabulates the information into a CSV file for analysis in Excel.

 

To provide some data to parse I configured a loadgen test against 10 mailboxes with a heavy profile, this should approximate to around 80 messages received and 20 sent per user.

 

The MessageStats script has a single command line parameter which controls how many days back it will look in the tracking logs.

 

The script only parses a single days worth of data, the value you provide define in the script which day to process, so 1 will process yesterdays logs.

 

5661_080911_1617_UserProfile1.png

 

Now we have our CSV file that we can open in Microsoft Excel, however the data required some work before we can get our EPA values. The following screenshot shows the raw data open in Excel. 

 

7750_2011-08-09_1244_34CA617F.png

 

1. Highlight cell A1

2. Press CTRL+SHIFT+END

3. Click on the INSERT Menu

4. Click on the TABLE button

5. Click on OK

6. Open the DESIGN Menu

7. Check the "Total Row" checkbox

8. Hide columns C,D,E,H,I,J,K,L,M,N,O,R,S,T,U

 

You should now have a table with the following columns…

 

- Date

- User

- Received Total

- Received MB Total

- Sent Unique Total

- Sent Unique MB Total

 

1817_2011-08-09_1254_280C42FC.png

 

Note: Due to my test lab being very small I have added a filter to remove any non-loadgen accounts from the data analysis.

 

In the Total row at the bottom of your table add "AVERAGE" subtotals for "Received Total" and "Sent Unique Total".

 

 

7367_2011-08-09_1304_3F4721A0.png

 

In the "Received MB Total" column total cell, add in an "AVERAGE" subtotal, then edit the formula in the cell and divide that value by the Total Row average for "Received Total", then multiply the result by 1024 – this will report the average message size in KB.

 

 

2018_2011-08-09_1303_296D1376.png

 

In the "Sent Unique MB Total" column total cell, add in an "AVERAGE" subtotal, then edit the formula in the cell and divide that value by the Total Row average for "Sent Unique Total", then multiply the result by 1024 – this will report the average message size in KB.

 

1854_2011-08-09_1308_273FEEE0.png

 

We now have all of the information that we require…

 

0211_2011-08-09_1312_2BC5D3CD.png

 

- Messages Received per Mailbox Per Day = Received Total = 68

- Messages Sent per Mailbox Per Day = Sent Unique Total = 17

- Average Message Size = Average of Received MB Total & Sent Unique MB Total (27.37+28.5)/2 = 27.94KB

 

So, using this technique we have managed to approximate our user profile to a fair degree of accuracy without needing to logon to any mailboxes!

 

I suspect that this method is accurate to around +/- 10% which is totally acceptable in this context. Obviously there is a caveat here that I have only performed some rudimentary testing in a fairly small lab environment, so if you do run this in production and find that it generates weird results, or that it validates your already proven EPA data, then feel free to drop me a note to let me know.

 

Thanks to @Frank Plawetzki  for checking

 

Shortcut for this post: http://aka.ms/NoEPA

 

0 Replies