KS-Soft. Network Management Solutions
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister    ProfileProfile    Log inLog in 

MYSQL Logging with Stored Procedure

 
Post new topic   Reply to topic    KS-Soft Forum Index -> Library
View previous topic :: View next topic  
Author Message
chickenwing



Joined: 17 Jun 2009
Posts: 3

PostPosted: Wed Jun 17, 2009 11:43 pm    Post subject: MYSQL Logging with Stored Procedure Reply with quote

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
Back to top
View user's profile Send private message
chickenwing



Joined: 17 Jun 2009
Posts: 3

PostPosted: Thu Jun 18, 2009 3:13 am    Post subject: Reply with quote

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`)));
Back to top
View user's profile Send private message
chickenwing



Joined: 17 Jun 2009
Posts: 3

PostPosted: Thu Jun 18, 2009 3:15 am    Post subject: Reply with quote

For information i am using this at the moment for logging every test and also for midnight logging with the backup log option.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    KS-Soft Forum Index -> Library All times are GMT - 6 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group

KS-Soft Forum Index