Database Formats

Monday, March 19th, 2007

Database Formats

These sample here implement the MonitorWare Common Database Format in widely used database systems. Attention Sybase users: the “Message” name is reserved in your database system and cannot be used as a field name. It needs to be changed, otherwise the table create will fail. Be sure to also change it in to client database field name configuration.

  • JET (MS Access) Sample
  • Microsoft SQL Server Sample
  • MySQL

JET (MS Access) Sample -A sample JET (Microsoft Access) database file is included in the MonitorWare Agent, EventReporter and WinSyslog install set. It conforms to the MonitorWare Common Database format. It is in Microsoft Access 97 format to enhance compatibility. It can be converted to any more current format without any problems. In fact, we recommend using the most current format supported by your system because it offers the best performance. To convert it, please use Microsoft Access.

Microsoft SQL Server Sample - If you would like to create the default database on Microsoft SQL server, please use the following script:

CREATE TABLE.SystemEvents
(
ID int IDENTITY (1, 1) NOT NULL,
CustomerID bigint,
ReceivedAt datetime NULL,
DeviceReportedTime datetime NULL,
Facility smallint NULL,
Priority smallint NULL,
FromHost nvarchar (60) NULL,
Message text,
NTSeverity int NULL,
Importance int NULL,
EventSource nvarchar (60),
EventUser nvarchar (60) NULL,
EventCategory int NULL,
EventID int NULL,
EventBinaryData text NULL,
MaxAvailable int NULL,
CurrUsage int NULL,
MinUsage int NULL,
MaxUsage int NULL,
InfoUnitID int NULL ,
SysLogTag varchar(60),
EventLogType varchar(60),
GenericFileName VarChar(60),
SystemID int NULL,
Checksum int NULL
)

CREATE TABLE.SystemEventsProperties
(
ID int IDENTITY (1, 1) NOT NULL ,
SystemEventID int NULL ,
ParamName varchar (255) NULL ,
ParamValue varchar(255) NULL
)

MySQL Sample - If you would like to create the default database on MySQL, please use the following script:

CREATE TABLE SystemEvents
(
ID int unsigned not null auto_increment primary key,
CustomerID bigint,
ReceivedAt datetime NULL,
DeviceReportedTime datetime NULL,
Facility smallint NULL,
Priority smallint NULL,
FromHost varchar(60) NULL,
Message text,
NTSeverity int NULL,
Importance int NULL,
EventSource varchar(60),
EventUser varchar(60) NULL,
EventCategory int NULL,
EventID int NULL,
EventBinaryData text NULL,
MaxAvailable int NULL,
CurrUsage int NULL,
MinUsage int NULL,
MaxUsage int NULL,
InfoUnitID int NULL ,
SysLogTag varchar(60),
EventLogType varchar(60),
GenericFileName VarChar(60),
SystemID int NULL,
Checksum int NULL
)

CREATE TABLE SystemEventsProperties
(
ID int unsigned not null auto_increment primary key,
SystemEventID int NULL ,
ParamName varchar(255) NULL ,
ParamValue varchar(255) NULL
)

This script should also be easily adaptable to other database systems like Oracle.

When porting the script to other database systems, please note that “nvarchar” is essentially “varchar”. The difference is that data is stored in Unicode which allows storage of non-ANSI characters. Typically, it can be replaced with “varchar” or an equivalent data type without any problems.