1 / 63

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?

thane
Download Presentation

Log Parser and Microsoft Exchange Server, the Perfect Blend!

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Log Parser and Microsoft Exchange Server, the Perfect Blend! Ilse Van Criekinge Exchange Trainer & Consultant (Azlan Training) ilse@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-where-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, ... • But…easy 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 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

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

  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 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 -

  18. 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

  19. 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.”

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

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

  22. 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...

  23. 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

  24. 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)

  25. 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 -

  26. 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

  27. 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 , )

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

  29. 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

  30. 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

  31. 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)

  32. 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

  33. 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]

  34. 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"

  35. 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

  36. 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)

  37. 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

  38. 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]

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

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

  41. 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

  42. 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]

  43. Join

More Related