MYSQL Logging with Stored Procedure

[chickenwing]

At the first i will post all the tables that i have created:

DROP TABLE IF EXISTS `hostmonitor`.`agent`;
CREATE TABLE `hostmonitor`.`agent` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Agent` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `hostmonitor`.`aktuell`;
CREATE TABLE `hostmonitor`.`aktuell` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ServiceN` int(10) unsigned NOT NULL,
`ServiceC` int(10) unsigned NOT NULL,
`MasterT` int(10) unsigned NOT NULL,
`TestM` int(10) unsigned NOT NULL,
`Agent1` int(10) unsigned NOT NULL,
`TestN` int(10) unsigned NOT NULL,
`Reply` varchar(45) DEFAULT NULL,
`Status1` int(10) unsigned NOT NULL,
`TestID` int(10) unsigned NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=169149 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `hostmonitor`.`marseinheiten`;
CREATE TABLE `hostmonitor`.`marseinheiten` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`unit` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `hostmonitor`.`mastertest`;
CREATE TABLE `hostmonitor`.`mastertest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`mastertest` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `hostmonitor`.`servicecomp`;
CREATE TABLE `hostmonitor`.`servicecomp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`servicecomp` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=40 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `hostmonitor`.`servicename`;
CREATE TABLE `hostmonitor`.`servicename` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`servicename` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `hostmonitor`.`status`;
CREATE TABLE `hostmonitor`.`status` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`status` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `hostmonitor`.`testmethods`;
CREATE TABLE `hostmonitor`.`testmethods` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`testmethod` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `hostmonitor`.`testname`;
CREATE TABLE `hostmonitor`.`testname` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`TestName` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=274 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

And now the Stored Procedure that will write the Data into the tables.

DELIMITER $$

DROP PROCEDURE IF EXISTS `hostmonitor`.`hostmon_insert`$$
CREATE DEFINER=`root`@`%` PROCEDURE `hostmonitor`.`hostmon_insert`(ServiceN varchar(255) , ServiceC varchar(255) ,MasterT varchar(255),TestM varchar(255),Agent1 varchar(255),
TestN varchar(255),Reply1 varchar(255),Status1 varchar(255),TestID1 int )
BEGIN

Declare ID_testname integer;
Declare ID_servicename integer;
Declare ID_servicecomp integer;
Declare ID_mastertest integer;
Declare ID_testmethod integer;
Declare ID_agent integer;
Declare ID_status integer;

if Not Exists(select id into ID_testname from hostmonitor.testname where testname = TestN) then
insert into hostmonitor.testname
(testname) Values(TestN);
END IF;

if Not Exists(select id into ID_servicename from hostmonitor.servicename where servicename = ServiceN) then
insert into hostmonitor.servicename
(servicename) Values(ServiceN);
END IF;

if Not Exists(select id into ID_servicecomp from hostmonitor.servicecomp where servicecomp = ServiceC) then
insert into hostmonitor.servicecomp
(servicecomp) Values(ServiceC);
END IF;

if Not Exists(select id into ID_mastertest from hostmonitor.mastertest where mastertest = MasterT) then
insert into hostmonitor.mastertest
(mastertest) Values(MasterT);
END IF;

if Not Exists(select id into ID_testmethod from hostmonitor.testmethods where testmethod = TestM) then
insert into hostmonitor.testmethods
(testmethod) Values(TestM);
END IF;

if Not Exists(select id into ID_agent from hostmonitor.agent where Agent = Agent1) then
insert into hostmonitor.agent
(Agent) Values(Agent1);
END IF;

if Not Exists(select id into ID_status from hostmonitor.status where status = Status1) then
insert into hostmonitor.status
(status) Values(Status1);
END IF;


select id into ID_servicename from hostmonitor.servicename where servicename = ServiceN;
select id into ID_testname from hostmonitor.testname where testname = TestN;
select id into ID_servicecomp from hostmonitor.servicecomp where servicecomp = ServiceC;
select id into ID_mastertest from hostmonitor.mastertest where mastertest = MasterT;
select id into ID_testmethod from hostmonitor.testmethods where testmethod = TestM;
select id into ID_agent from hostmonitor.agent where Agent = Agent1;
select id into ID_status from hostmonitor.status where status = Status1;


insert into hostmonitor.aktuell

(ServiceN,ServiceC,MasterT,TestM,Agent1,TestN,Reply,Status1,TestID,timestamp)

Values

(ID_servicename,ID_servicecomp,ID_mastertest,ID_testmethod,ID_agent,ID_testname,Reply1,ID_status,TestID1,Current_timestamp() );


END $$

DELIMITER ;

And now the call for the Hostmonitor

Call hostmonitor.hostmon_insert('%ServiceName%','%ServiceComp%','%MasterTest%','%TestMethod%','%Agent%','%TestName%','%Reply%','%Status%','%TestID%')

I log every test into the Database at the moment. At the moment this ist the first thing i can post here. I hope in a few days i can Post a view for LogAnalyzer. When some one has an idea feel free to post it here. Thanks

[chickenwing]

so now here comes the view

DROP VIEW IF EXISTS `hostmonitor`.`hostmon_complete`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `hostmonitor`.`hostmon_complete` AS select `agent`.`Agent` AS `Agent`,`mastertest`.`mastertest` AS `mastertest`,`servicecomp`.`servicecomp` AS `servicecomp`,`servicename`.`servicename` AS `servicename`,`status`.`status` AS `status`,`testname`.`TestName` AS `TestName`,`testmethods`.`testmethod` AS `testmethod`,`aktuell`.`timestamp` AS `timestamp`,`aktuell`.`TestID` AS `TestID`,`aktuell`.`Reply` AS `Reply` from (((((((`agent` join `aktuell` on((`aktuell`.`Agent1` = `agent`.`id`))) join `mastertest` on((`mastertest`.`id` = `aktuell`.`MasterT`))) join `servicecomp` on((`servicecomp`.`id` = `aktuell`.`ServiceC`))) join `servicename` on((`servicename`.`id` = `aktuell`.`ServiceN`))) join `status` on((`status`.`id` = `aktuell`.`Status1`))) join `testname` on((`testname`.`id` = `aktuell`.`TestN`))) join `testmethods` on((`testmethods`.`id` = `aktuell`.`TestM`)));

[chickenwing]

For information i am using this at the moment for logging every test and also for midnight logging with the backup log option.