Log Parser and Microsoft Exchange Server, the Perfect Blend

1
Log Parser and Microsoft Exchange Server, the
Perfect Blend!

• Ilse Van Criekinge
• Exchange Trainer Consultant
• (Azlan Training)
• ilse_at_vancriekinge.com
• www.Pro-Exchange.be

2
Objectives

• Who needs reports?
• What kind of data is available?
• How to gain access to the data?
• How to present the acquired data?

3
Agenda

• Introduction
• Process flow
• Sources of data
• Pulling it all together Joins
• Report creation
• Automation

4
Introduction

• Reports are critical to a healthy Exchange org
• understand, monitor, and track who-what-when-wher
  e-how
• usage data
• mail usage by user
• message traffic patterns
• delivery times
• historical usage trends
• message content reporting
• ...

5

• Many reporting packages are available at a price
• OmniAnalyser, StealthAUDIT for Exchange, eIQ
  Mailanalyzer, Quest MessageStats, Admin Report
  Kit for Exchange, IMFStats, bv-Control for
  Exchange, PROMODAG Reports for Exchange,
  MailMeter Insight, Mail Access Monitor for MS
  Exchange Server,MailDetective, Sirana AppAnayzer
  for Exchange, e-nspect real time reporting, Quest
  Reporter, DYS CONTROL!, Exchange Monitor,
  bt-LogAnalyzer, ...
• Buteasy to develop basic reports yourself!

6
Requirements

• Microsoft Exchange Server
• Active Directory
• Data extraction tools
• Microsoft Log Parser 2.2
• A dash of scripting

7
To create publish reports

• Microsoft SQL Server 2000 (or better)
• ? to store the data
• Visual Studio .NET 2003 (or better)
• ? to create reports
• SQL Reporting Services
• ? to publish reports

8
Why Reporting Services?

• Powerful web based reporting tool
• Easy to create rich, interactive, graphical
  reports
• End-users can subscribe to receive reports via
  email, file share, etc
• End-users can export reports to various formats
  (XLS, XML, CSV, HTML, TIFF, PDF, etc)
• Easy to develop
• Rendering and processing can be seperated

9
Agenda

• Introduction
• Process flow
• Sources of data
• Pulling it all together Joins
• Report creation
• Automation

10
Process Flow

• Extract data from source
• Load data into SQL Server
• Create report in Visual Studio
• Publish report

11
Process Flow
Exchange Message Tracking Logs
Active Directory User Mailbox Info
Exchange Mailbox Info
Data Sources
Data Access Method
Log Parser
CSVDE
WMI
Data Storage
Microsoft SQL Server Database
Data Output
Microsoft SQL Server Reporting Services
12
Agenda

• Introduction
• Process flow
• Sources of data
• Pulling it all together Joins
• Report creation
• Automation

13
Sources of Data

• Exchange message tracking logs
• Active Directory
• HomeMDB, quota settings, ...
• User Information
• WMI providers for Exchange

14
Source 1Exchange Message Tracking Logs

• Available in Exchange 5.5/2000/2003
• Has to be enabled
• Exchange 5.5 Information Store/MTA/Internet Mail
  Service
• Exchange 2000/2003 Server setting
• Options
• Remove log files older than (days)
• Exchange 2000/2003 Enable subject logging and
  display
• Exchange 2003 Location to store log files

15
Message Tracking Log Format

• Note Tracking logs in Microsoft Exchange 2000
  Server have a significantly different format then
  Microsoft Exchange Server 5.5 tracking logs.
• Generally follows the W3C format for log files
• First few lines contain directives, tab delimited
• One log generated/server/day
• Logs roll at midnight GMT
• All times in the log are GMT

16
Message Tracking Log Fields

• Message tracking event IDs in Exchange Server
  2003
• 1027 Message submission by store
• 1028 Message delivery
• Overview http//support.microsoft.com/?kbid82190
  5

17
Message Tracking Log Sample

• Message Tracking Log File
• Exchange System Attendant Version
  6.5.7638.1
• Date Time client-ip Client-hostname Partner-Name
  Server-hostname server-IP Recipient-Address
• Event-ID MSGID Priority Recipient-Report-Status
  total-bytes Number-Recipients Origination-Time Enc
  ryption service-Version Linked-MSGID Message-Subje
  ct Sender-Address
• 2006-2-7 102841 GMT - - - NTS00 - ivcrieki_at_yahoo
  .com 1027 3ADF255035AF154496E38B1C234B9C5D442F_at_nts
  00.matisse.edu 0 0 511 1 2006-2-7 102841
  GMT 0 - cUSa pFirst OrganizatilNTS00- 06020
  7102841Z-5 Will Public Folders disappear? EX/OFI
  RST ORGANIZATION/OUFIRST ADMINISTRATIVE
  GROUP/CNRECIPIENTS/CNILSE -
• 2006-2-7 102841 GMT - - - NTS00 - ivcrieki_at_yahoo
  .com 1019 3ADF255035AF154496E38B1C234B9C5D442F_at_nts
  00.matisse.edu 0 0 511 1 2006-2-7 102841
  GMT 0 - - Will Public Folders disappear? - -
• 2006-2-7 102841 GMT - - - NTS00 - ivcrieki_at_yahoo
  .com 1025 3ADF255035AF154496E38B1C234B9C5D442F_at_nts
  00.matisse.edu 0 0 511 1 2006-2-7 102841
  GMT 0 - - Will Public Folders disappear? - -
• 2006-2-7 102841 GMT - - - NTS00 - ivcrieki_at_yahoo
  .com 1024 3ADF255035AF154496E38B1C234B9C5D442F_at_nts
  00.matisse.edu 0 0 511 1 2006-2-7 102841
  GMT 0 - - Will Public Folders disappear? - -
• 2006-2-7 102841 GMT - - - NTS00 - ivcrieki_at_yahoo
  .com 1033 3ADF255035AF154496E38B1C234B9C5D442F_at_nts
  00.matisse.edu 0 0 511 1 2006-2-7 102841
  GMT 0 - - Will Public Folders disappear? Ilse.VanC
  riekinge_at_matisse.edu -
• 2006-2-7 102841 GMT - - - NTS00 - ivcrieki_at_yahoo
  .com 1034 3ADF255035AF154496E38B1C234B9C5D442F_at_nts
  00.matisse.edu 0 0 511 1 2006-2-7 102841
  GMT 0 - - Will Public Folders disappear? Ilse.VanC
  riekinge_at_matisse.edu -

18
(No Transcript)
19
Log Parser

• Log Parser 2.2 can be used to reformat the
  tracking logs into a format digestible by SQL
  Server
• Log Parser is available for download from the
  Microsoft Download Center
• http//www.microsoft.com/download

20
Introducing Log ParserThe world is your
database with Log Parser

• Log Parser allows users to treat log files and
  other information as SQL tables, the rows of
  which can be queried, processed, and formatted in
  different ways
• Born around 2000, as a utility to test the
  logging mechanisms of IIS
• Latest release version 2.2
• Designed and engineered with the vision of
  helping users achieve their data-processing goals
  in a simple, fast, and powerful way.

21
Building Queries
Log Parser Query
Data (Input Formats)
Output Records (Output Formats)
22
Building Blocks Log Parser

• Input Format
• Log Parser Query (dialect of SQL)
• Output Format

23
Example Retrieving some fields from the Event
Log

• c\LogParser iEVT oNAT SELECT
• TimeGenerated, SourceName FROM System
• Or SELECT TimeGenerated, SourceName INTO
• mytest.txt FROM System

TimeGenerated SourceName ------------------
- ----------------------- 2005-11-10 122607
Windows Update Agent 2005-11-10 122614
Windows Update Agent 2005-11-10 150023
Service Control Manager 2005-11-10 150023
Service Control Manager 2005-11-10 150044
Windows Update Agent 2005-11-10 150118
Windows Update Agent 2005-11-10 150130
NtServicePack 2005-11-10 150136 Windows
Update Agent 2005-11-10 150150 Windows Update
Agent 2005-11-10 150212 Windows Update
Agent Press a key...
24
Back to Message Tracking Logs

• Command
• LogParser.exe
• filef\info\msgtracklog.sql?infilef\info\logs\2
  0060207.logoutfile f\info\logs\20060207.bcp
• -iW3C -oTSV
• W3C input format parses log files in the W3C
  Extended Log File Format
• TSV output format creates text file formatted
  according to the Tab-Seperated-Values convention

25
Log Parser Query Syntax

• msgtracklog.sql
• SELECT
• TO_Timestamp(REPLACE_STR(STRCAT(STRCAT(date,' '),
  time),' GMT',''),'yyyy-M-d hms') as DateTime,
• client-ip, Client-hostname, Partner-name,
  Server-hostname, server-IP,
  Recipient-Address,
• Event-ID, MSGID, Priority,
  Recipient-Report-Status, total-bytes,
  Number-Recipients,
• TO_Timestamp(REPLACE_STR(Origination-time, '
  GMT',''),'yyyy-M-d hms') as Origination Time,
• Encryption, service-Version, Linked-MSGID,
  Message-Subject, Sender-Address
• INTO 'outfile'
• FROM 'infile'
• WHERE Event-ID IN (10271028)

26
Log Parser Output

• DateTime client-ip Client-hostname Partner-Name Se
  rver-hostname server-IP Recipient-Address Event-ID
  
• MSGID Priority Recipient-Report-Status total-byt
  es Number-Recipients Origination Time
• Encryption service-Version Linked-MSGID Message-S
  ubject
• Sender-Address
• 2006-02-07 102841 NTS00 ivcrieki_at_yahoo.com 1
  027 3ADF255035AF154496E38B1C234B9C5D442F_at_nts00.mat
  isse.edu 0 0 511 1 2006-02-07 102841 0 cUSa
  pFirst OrganizatilNTS00-060207102841Z-5 Will
  Public Folders disappear? EX/OFIRST
  ORGANIZATION/OUFIRST ADMINISTRATIVE
  GROUP/CNRECIPIENTS/CNILSE -

27
(No Transcript)
28
Populate SQL with formed DATA

• Create a table to hold the data
• Import information into the database
• Several tools available, like
• SQL 2000 SQL Query Analyzer, osql utility, bcp
  utility
• SQL 2005 SQL Management Studio, sqlcmd, bcp or
  osql utility

29
Create Table to Hold Data

• USE Analyzing_Exchange
• CREATE TABLE MsgTrackingLogs (
• DateTime datetime NULL ,
• Client-IP varchar (255) NULL ,
• Client-Hostname nvarchar (255) NULL ,
• Partner-name nvarchar (255) NULL ,
• Server-hostname nvarchar (255) NULL ,
• Server-IP varchar (255) NULL ,
• Recipient-Address varchar (512) NULL ,
• Event-ID int NULL ,
• MSGID nvarchar (1024) NULL ,
• Priority int NULL ,
• Recipient-Report-Status int NULL ,
• Total-bytes bigint NULL ,
• Number-Recipients int NULL ,
• Origination Time datetime NULL ,
• Encryption int NULL ,
• Service-version varchar (255) NULL ,
• Linked-MSGID varchar (255) NULL ,

30
(No Transcript)
31
Import Data Into Database

• Bcp Analyzing_Exchange.dbo.msgtrackinglogs
• in f\info\logs\20060207.bcp
• c
• t\t
• T
• F 2

32
(No Transcript)
33
(No Transcript)
34
Source 2 Active Directory

• Each mailbox is an object in AD
• Some relevant properties
• legacyExchangeDN
• homeMDB
• mDBUseDefaults (use default quota)
• mDBStorageQuota (issue warning)
• mDBOverQuotaLimit (prohibit send)
• mDBOverHardQuotaLimit (prohibit send/receive)
• Can also include fields like city, department,
  etc..
• Use CSVDE to export data to CSV file
• Use account with Exchange view only admin rights

35
Introducing CSVDE

• csvde.exe installed on Windows 200X Server by
  default
• Can be run from Windows 2000 Pro or XP
  Professional
• Can be used to import and export data from
  Active Directory by using files that store data
  in the comma-separated value (CSV) file format
  standard
• Also supports batch operations that are based on
  CSV

36
CSVDE Syntax

• CSVDE
• -f file to export to
• -s servername
• -d LDAP search root
• -r LDAP search filter (default objectClass)
• -l list of attributes to export
• -u Unicode format (important for DBCS)

37
CSVDE Example

• Extract a specified list of all Person objects in
  the Matisse domain
• CSVDE f f\info\ad\directory.csv
• -s NTSMATISSE
• -d "dcmatisse,dcedu"
• -r "((objectCategoryPerson)(homeMDB))"
• -l DN,legacyExchangeDN,mail,homeMDB,mDBUseDefaults
  ,mDBOverQuotaLimit,mDBStorageQuota,mDBOverHardQuot
  aLimit,department
• -u

38
Create Table to Hold Data

• CREATE TABLE Active_Directory_Info (
• DN varchar (1000) NULL ,
• legacyExchangeDN varchar (512) NULL,
• mail varchar (512) NULL ,
• homeMDB varchar (1000) NULL ,
• mDBUseDefaults varchar (10) NULL ,
• mDBOverQuotaLimit int NULL ,
• mDBStorageQuota int NULL ,
• mDBOverHardQuotaLimit int NULL ,
• Department varchar (256) NULL ,
• ) ON PRIMARY

39
Import Data Into Database

• LogParser
• "SELECT DN,legacyExchangeDN,mail,homeMDB,mDBUseDef
  aults,mDBOverQuotaLimit,mDBStorageQuota,mDBOverHar
  dQuotaLimit,department
• into dbo.Active_Directory_Info
• FROM f\info\ad\directory.csv"
• -icsv
• -oSQL -serverservername -databaseAnalyzing_Exch
  ange -driver"SQL Server"

40
(No Transcript)
41
Source 3 WMI

• Windows Management Instrumentation
• Management technology allowing scripts to monitor
  and control managed resources throughout the
  network
• Resources include hard drives, file systems,
  operating system settings, processes, services,
  shares, registry settings, networking components,
  event logs, users, and groups
• Built into clients with Windows 2000 or above,
  and can be installed on any other 32-bit Windows
  client
• WMI is easy to consume via script

42
Exchange_Mailbox WMI Class

• New class for Exchange 2003
• Returns properties of a mailbox
• Interesting fields
• MailboxDisplayName
• LegacyDN (legacyExchangeDN)
• ServerName (Exchange server name)
• Size (size of mailbox in kb)
• TotalItems (total messages in the mailbox)
• DeletedMessageSizeExtended (Size in bytes of
  deleted messages being retained per deleted items
  retention policy)

43
VBScript to Access WMI Data

• strWinMgmts "winmgmtsimpersonationLevelimpers
  onate!//ServerName/root/MicrosoftExchangeV2"
• Set objWMIExchange GetObject(strWinMgmts)
• Set listExchange_Mailboxes objWMIExchange.Instan
  cesOf("Exchange_Mailbox")
• For each objExchange_Mailbox in
  listExchange_Mailboxes
• Wscript.echo objExchange_Mailbox.MailboxDisplay
  Name vbTab _
• objExchange_Mailbox.LegacyDN vbTab _
• objExchange_Mailbox.ServerName vbTab _
• objExchange_Mailbox.Size vbTab _
• objExchange_Mailbox.TotalItems vbTab _
• objExchange_Mailbox.DeletedMessageSizeExte
  nded vbTab _
• objExchange_Mailbox.LastLogonTime vbTab _
• objExchange_Mailbox.LastLogOffTime vbTab _
• objExchange_Mailbox.LastLoggedOnUserAccount
• Next
• Execute as cscript //nologo mailboxes.vbs gt
  Mailboxes.txt

44
Create Table to Hold Data

• CREATE TABLE MailboxSizeData (
• displayName varchar (128) NULL ,
• legacyExchangeDN varchar (512) NULL ,
• ServerName varchar (50) NULL ,
• Size int NULL ,
• TotalItems int NULL ,
• DeletedMessageSizeExtended int NULL ,
• LastLogonTime varchar (50) NULL ,
• LastLogoffTime varchar (50) NULL ,
• LastLoggedOnUserAccount varchar (50) NULL
• ) ON PRIMARY

45
Import Data Into Database

• Microsoft SQL Server Management Studio
• BULK INSERT MailboxSizeData FROM
  f\info\wmi\Mailboxes.txt'

46
(No Transcript)
47
Agenda

• Introduction
• Process flow
• Sources of data
• Pulling it all together Joins
• Report creation
• Automation

48
Pulling it all together

• SQL joins let us relate data in one table with
  data in another table
• Powerful feature for rich reports
• Use common columns to relate data

49
Table Joins

• MsgTrackingLogs, MailboxSizeData and
  Active_Directory_Info can all be joined
• Active_Directory_Info and MailboxSizeData join on
  legacyExchangeDN
• Then join Active_Directory_Info to
  MsgTrackingLogs on Recipient-Address

50
Join
51
Agenda

• Introduction
• Process flow
• Sources of data
• Pulling it all together Joins
• Report creation
• Automation

52
Report Creation Getting Started

• Install Reporting Services
• Install Reporting Services Client Tools
• Create new Business Intelligence project
• Select template Report Server Project Wizard

53
Six Easy Steps

• Define data source
• Design query
• Choose type of report
• Specify basic layout of report
• Format the report
• Deploy the report

54
(No Transcript)
55
Deploy

• Check project properties
• Check TargetServerUrRL
• http//myserver.mydomain.com/ReportServer
• Deploy!
• When complete, browse with IE
• http//servername/Reports

56
(No Transcript)
57
Agenda

• Introduction
• Process flow
• Sources of data
• Pulling it all together Joins
• Report creation
• Automation

58
Automation

• Automate data gathering and import with
• SQL 2000 Data Transformation Services (DTS)
• SQL 2005 SQL System Integration Services
• (SSIS)

59
Summary

• Reports are vital to the health of your messaging
  infrastructure
• Basic reports are fairly easy to develop
• Three key data sources AD, Tracking logs, WMI
• This session gave you a very limited view of all
  the power you have when you use the available
  tools to create custom reports!

60
Thank you for your attention!

• Ilse Van Criekinge
• Exchange Trainer Consultant
• (Azlan Training)
• ilse_at_vancriekinge.com
• www.Pro-Exchange.be

61
(No Transcript)
62
Resources

• LogParser 2.2
• Microsoft Log Parser Toolkit, Gabriele
  GiuseppiniMark Burnett, Syngress
• http//www.logparser.com/
• http//www.microsoft.com/downloads/details.aspx?Fa
  milyID890cd06b-abf8-4c25-91b2-f8d975cf8c07displa
  ylangen
• http//www.microsoft.com/technet/scriptcenter/tool
  s/logparser/default.mspx
• Message tracking log field descriptions
• http//support.microsoft.com/default.aspx?scidkb
  en-us246965
• Message tracking log event id definitions
• http//support.microsoft.com/default.aspx?scidkb
  en-us821905
• SQL Server 2000/2005
• http//www.microsoft.com/sql
• Visual Studio 2005
• http//msdn.microsoft.com/vstudio/
• WMI Exchange_Mailbox class
• http//msdn.microsoft.com/library/default.asp?url
  /library/en-us/e2k3/e2k3/_wmiref_cl_Exchange_Mailb
  ox.asp

63
The Connected Generation

• 7 8 March 2006
• ICC Gent