Re: [iMS] Log parsing?

[Brett Payne-Rhodes]

Hi Brett,

I have a couple of CF scripts that may help you get started.

The first one, collectEmailStats.cfm does the actual parsing of the log files 
and that might be most useful to you. It writes mail stats information for each 
email domain to a (mySQL) database.

The other, generateVolumeReports.cfm reads the data and creates a simple text 
file report for each domain for the current month.

I have these set up to run every night.

The mailstats table looks like:

CREATE TABLE  `iMS`.`mailstats` (
 `DomainId` int(11) NOT NULL default '0',
 `Domain` varchar(50) NOT NULL default '',
 `TranDate` datetime NOT NULL default '0000-00-00 00:00:00',
 `PostCount` smallint(6) NOT NULL default '0',
 `PostVolume` int(11) NOT NULL default '0',
 `SMTPCount` smallint(6) NOT NULL default '0',
 `SMTPVolume` int(11) NOT NULL default '0',
 `InvoiceNumber` int(11) default NULL,
 PRIMARY KEY  (`DomainId`,`Domain`,`TranDate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Sorry for the delay in getting back to you, we had out Anzac Day holiday here 
in Australia yesterday.


Cheers,

Brett
B)


Brett Peterson wrote:

Ben,
I've used TextPad to view the logs.  The search functionality lets you get a 
count of what it found (mark all), so I have several phrases I look for on a 
regular basis.  It remembers past search phrases, so each time I go in I cycle 
through the top search terms and get my counts.  (eg, mbx sent successfully)

Brett Peterson (must be the third Brett on this list)
<hr size=1 color="#4455AA" noshade width=250 align=left><font size=1>I think they 
should televise the Congressional Medal of Honor awards instead of the Oscars.</font id=signature>

==^=======================================================
This list server is Powered by iMS  "The Swiss Army Knife of Mail Servers"
--------------------------------------------------------------------------------------
This list is provided as a free service.  Although we will try to address issues
in a timely manner, support via this list is not guaranteed.  If you require 
expedited
support then a support contract is required.  Support may be purchased from
http://www.coolfusion.com/commerce.  Details regarding support options may be 
reviewed
at: http://www.coolfusion.com/SupportOptions.cfm
--------------------------------------------------------------------------------------
To leave this list please complete the form at 
http://www.coolfusion.com/Support/
Need an iMS Developer license?  Sign up for a free license here:
http://www.coolfusion.com/Developers/
List archives: http://www.coolfusion.com/cfbb/
Note: You are subscribed as archive_jab_org / [EMAIL PROTECTED]
==^=======================================================

SELECT * FROM domainAliases #left(dayOfWeekAsString(DayOfWeek(tranDate)), 3)# #dateFormat(tranDate, "DD MMM")#
#Domains["#domainList.domain#"].name# #Domains["#domainList.domain#"].postCount# #Domains["#domainList.domain#"].postVolume#
Delete from MailStats Where domainId=#domainList.domainId# And domain='#domainList.domain#' And TranDate=#CreateODBCDateTime(tranDate)# Insert into MailStats (domainId, domain, tranDate, postCount, postVolume) Values (#domainList.domainId#, '#domainList.domain#', #CreateODBCDateTime(tranDate)#, #Domains["#domainList.domain#"].postCount#, #Domains["#domainList.domain#"].postVolume#) #left(dayOfWeekAsString(DayOfWeek(tranDate)), 3)# #dateFormat(tranDate, "DD MMM")#
#Domains["#domainList.domain#"].name# #Domains["#domainList.domain#"].smtpCount# #Domains["#domainList.domain#"].smtpVolume#
Select domainId from MailStats Where domainId=#domainList.domainId# And domain='#domainList.domain#' And TranDate=#CreateODBCDateTime(tranDate)# Update MailStats Set smtpCount=#Domains["#domainList.domain#"].smtpCount#, smtpVolume=#Domains["#domainList.domain#"].smtpVolume# Where domainId=#domainList.domainId# And domain='#domainList.domain#' And TranDate=#CreateODBCDateTime(tranDate)# Insert into MailStats (domainId, domain, tranDate, smtpCount, smtpVolume) Values (#domainList.domainId#, '#domainList.domain#', #CreateODBCDateTime(tranDate)#, #Domains["#domainList.domain#"].smtpCount#, #Domains["#domainList.domain#"].smtpVolume#) insert into test (f1) values ('email statistics directory: #statsDir# or archive directory: #archiveDir# not found!!!');

This list server is Powered by iMS "The Swiss Army Knife of Mail Servers" This list is provided as a free service. Although we will try to address issues in a timely manner, support via this list is not guaranteed. If you require expedited support then a support contract is required. Support may be purchased from http://www.coolfusion.com/commerce. Details regarding support options may be reviewed at: http://www.coolfusion.com/SupportOptions.cfm. To leave this list please complete the form at http://www.coolfusion.com/Support/ Need an iMS Developer license? Sign up for a free license here: http://www.coolfusion.com/Developers/ List archives: http://www.coolfusion.com/cfbb/ Note: You are subscribed as archive_jab_org / [EMAIL PROTECTED]

Start Date: #dateFormat(startDate, "DD MMM YYYY")#
End Date: #dateFormat(endDate, "DD MMM YYYY")#

Select domains.*, domainAliases.domain as domainAlias From domains, domainAliases Where domains.domainId = domainAliases.domainId Select sum(postCount) as postCountTotal, sum(postVolume) as postVolumeTotal, sum(smtpCount) as smtpCountTotal, sum(smtpVolume) as smtpVolumeTotal from MailStats Where domainId=#domainList.domainId# And TranDate>=#CreateODBCDateTime(startDate)# And TranDate<=#CreateODBCDateTime(endDate)# #txtOut#
#txtOut#
#txtOut#
#txtOut#
#txtOut#
#txtOut#
#txtOut#
#txtOut#

#txtOut#
#txtOut#
#txtOut#
#txtOut#
Select * from MailStats Where domainId=#domainList.domainId# And TranDate>=#CreateODBCDateTime(startDate)# And TranDate<=#CreateODBCDateTime(endDate)# Order by TranDate #txtOut#
#txtOut#
#txtOut#
#txtOut#