Log Parser and Microsoft Exchange Server, the Perfect Blend!

Log Parser and Microsoft Exchange Server, the Perfect Blend! Ilse Van Criekinge Exchange Trainer & Consultant (Azlan Training) ilse@vancriekinge.com www.Pro-Exchange.be

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

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

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

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, ... • But…easy to develop basic reports yourself!

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

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

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

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

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

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

Source 1:Exchange 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

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

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/?kbid=821905

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 Encryption service-Version Linked-MSGID Message-Subject Sender-Address 2006-2-710:28:41 GMT - - - NTS00 - ivcrieki@yahoo.com 1027 3ADF255035AF154496E38B1C234B9C5D442F@nts00.matisse.edu 0 0 511 1 2006-2-7 10:28:41 GMT 0 - c=US;a= ;p=First Organizati;l=NTS00- 060207102841Z-5 Will Public Folders disappear? EX:/O=FIRST ORGANIZATION/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=ILSE - 2006-2-7 10:28:41 GMT - - - NTS00 - ivcrieki@yahoo.com 1019 3ADF255035AF154496E38B1C234B9C5D442F@nts00.matisse.edu 0 0 511 1 2006-2-7 10:28:41 GMT 0 - - Will Public Folders disappear? - - 2006-2-7 10:28:41 GMT - - - NTS00 - ivcrieki@yahoo.com 1025 3ADF255035AF154496E38B1C234B9C5D442F@nts00.matisse.edu 0 0 511 1 2006-2-7 10:28:41 GMT 0 - - Will Public Folders disappear? - - 2006-2-7 10:28:41 GMT - - - NTS00 - ivcrieki@yahoo.com 1024 3ADF255035AF154496E38B1C234B9C5D442F@nts00.matisse.edu 0 0 511 1 2006-2-7 10:28:41 GMT 0 - - Will Public Folders disappear? - - 2006-2-7 10:28:41 GMT - - - NTS00 - ivcrieki@yahoo.com 1033 3ADF255035AF154496E38B1C234B9C5D442F@nts00.matisse.edu 0 0 511 1 2006-2-7 10:28:41 GMT 0 - - Will Public Folders disappear? Ilse.VanCriekinge@matisse.edu - 2006-2-7 10:28:41 GMT - - - NTS00 - ivcrieki@yahoo.com 1034 3ADF255035AF154496E38B1C234B9C5D442F@nts00.matisse.edu 0 0 511 1 2006-2-7 10:28:41 GMT 0 - - Will Public Folders disappear? Ilse.VanCriekinge@matisse.edu -

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

Introducing Log Parser“The 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.”

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

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

Example: Retrieving some fields from the Event Log c:\LogParser –i:EVT –o:NAT “SELECT TimeGenerated, SourceName FROM System” Or “SELECT TimeGenerated, SourceName INTO mytest.txt FROM System” TimeGenerated SourceName ------------------- ----------------------- 2005-11-10 12:26:07 Windows Update Agent 2005-11-10 12:26:14 Windows Update Agent 2005-11-10 15:00:23 Service Control Manager 2005-11-10 15:00:23 Service Control Manager 2005-11-10 15:00:44 Windows Update Agent 2005-11-10 15:01:18 Windows Update Agent 2005-11-10 15:01:30 NtServicePack 2005-11-10 15:01:36 Windows Update Agent 2005-11-10 15:01:50 Windows Update Agent 2005-11-10 15:02:12 Windows Update Agent Press a key...

Back to Message Tracking Logs Command: LogParser.exe file:f:\info\msgtracklog.sql?infile=f:\info\logs\20060207.log+outfile= f:\info\logs\20060207.bcp -i:W3C -o:TSV • 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

Log Parser Query Syntax msgtracklog.sql: SELECT TO_Timestamp(REPLACE_STR(STRCAT(STRCAT(date,' '), time),' GMT',''),'yyyy-M-d h:m:s') 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 h:m:s') as [Origination Time], Encryption, [service-Version], [Linked-MSGID], [Message-Subject], [Sender-Address] INTO '%outfile%' FROM '%infile%' WHERE [Event-ID] IN (1027;1028)

Log Parser Output DateTime 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 Encryption service-Version Linked-MSGID Message-Subject Sender-Address 2006-02-07 10:28:41 NTS00 ivcrieki@yahoo.com 1027 3ADF255035AF154496E38B1C234B9C5D442F@nts00.matisse.edu 0 0 511 1 2006-02-07 10:28:41 0 c=US;a= ;p=First Organizati;l=NTS00-060207102841Z-5 Will Public Folders disappear? EX:/O=FIRST ORGANIZATION/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=ILSE -

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

Create Table to Hold Data USE Analyzing_Exchange CREATE TABLE MsgTrackingLogs ( [DateTime] datetimeNULL , [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] intNULL , [MSGID] nvarchar (1024) NULL , [Priority] int NULL , [Recipient-Report-Status] int NULL , [Total-bytes] bigintNULL , [Number-Recipients] intNULL , [Origination Time] datetimeNULL , [Encryption] int NULL , [Service-version] varchar (255) NULL , [Linked-MSGID] varchar (255) NULL , [Message-Subject] nvarchar (255) NULL , [Sender-Address] varchar (255) NULL , )

Import Data Into Database Bcp Analyzing_Exchange.dbo.msgtrackinglogs in f:\info\logs\20060207.bcp –c –t”\t” –T –F 2

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

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

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)

CSVDE Example Extract a specified list of all Person objects in the Matisse domain: CSVDE –f f:\info\ad\directory.csv -s NTSMATISSE -d "dc=matisse,dc=edu" -r "(&(objectCategory=Person)(homeMDB=*))" -l DN,legacyExchangeDN,mail,homeMDB,mDBUseDefaults,mDBOverQuotaLimit,mDBStorageQuota,mDBOverHardQuotaLimit,department -u

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]

Import Data Into Database LogParser "SELECT DN,legacyExchangeDN,mail,homeMDB,mDBUseDefaults,mDBOverQuotaLimit,mDBStorageQuota,mDBOverHardQuotaLimit,department into dbo.Active_Directory_Info FROM f:\info\ad\directory.csv" -i:csv -o:SQL -server:servername -database:Analyzing_Exchange -driver:"SQL Server"

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

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)

VBScript to Access WMI Data strWinMgmts = "winmgmts:{impersonationLevel=impersonate}!//ServerName/root/MicrosoftExchangeV2" Set objWMIExchange = GetObject(strWinMgmts) Set listExchange_Mailboxes = objWMIExchange.InstancesOf("Exchange_Mailbox") For each objExchange_Mailbox in listExchange_Mailboxes Wscript.echo objExchange_Mailbox.MailboxDisplayName & vbTab _ & objExchange_Mailbox.LegacyDN & vbTab _ & objExchange_Mailbox.ServerName & vbTab _ & objExchange_Mailbox.Size & vbTab _ & objExchange_Mailbox.TotalItems & vbTab _ & objExchange_Mailbox.DeletedMessageSizeExtended & vbTab _ & objExchange_Mailbox.LastLogonTime & vbTab _ & objExchange_Mailbox.LastLogOffTime & vbTab _ & objExchange_Mailbox.LastLoggedOnUserAccount Next Execute as:cscript //nologo mailboxes.vbs > Mailboxes.txt

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]

Import Data Into Database Microsoft SQL Server Management Studio “BULK INSERT [MailboxSizeData] FROM ‘f:\info\wmi\Mailboxes.txt'“

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

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]

Join

Log Parser and Microsoft Exchange Server, the Perfect Blend!