Exchange server is logging through ODBC to a SQL table.
The layout of the table is:
CREATE TABLE [dbo].[inetlog] ( [ClientHost] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , [username] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , [LogTime] [datetime] NULL , [service] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , [machine] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , [serverip] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [processingtime] [int] NULL , [bytesrecvd] [int] NULL , [bytessent] [int] NULL , [servicestatus] [int] NULL , [win32status] [int] NULL , [operation] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , [target] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , [parameters] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ) ON [PRIMARY] GO
This structure is defined by MS. I have provided sample data in the attached zip file. The required output should be:
Time Sender Recipient Result Incoming
Result column should be OK or an error msg. Incoming is a bit field which indicates weither it is in incoming or outgoing mail.
The Sender and Recipient should be the mail address only, not FROM:. Since this will require use of the substring function it probably would be useful to do this on insert with an instead of insert query! Keep in mind that performance should be good and that this table will contain >500.000 records.
When you believe performance will be an issue you may also suggest an alternative based on a instead of insert trigger which would already group rows.
The database backend is MSDE so there is a limit of 2GB per database. This means no excessive use of indexes in order to stay under the limit.
Knowledge of the exchange log and especially parameter column is required in order to fulfill this project. There can be NO errors in the resulting output!
## Deliverables
1) Complete and fully-functional working queries in executable form as well as complete source code of all work done.
2) Deliverables must be in ready-to-run condition, as follows? (depending on the nature? of the deliverables):
a)? For web sites or? other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment.
b) For all others including desktop software or software the buyer intends to distribute: A software? installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request.
3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).
## Platform
SQL Server 2000. (MSDE 2000).