0

I'm sending syslog-ng to Percona. I have different logging sources filtered into different MySQL tables. I'm trying to determine the number of logs per second, minute and hour.

This is how the table was created:

CREATE TABLE syslog.switchlogs (
  host varchar(40) DEFAULT NULL,
  facility varchar(10) DEFAULT NULL,
  level varchar(10) DEFAULT NULL,
  tag varchar(10) DEFAULT NULL,
  program varchar(50) DEFAULT NULL,
  msg text,
  seq bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (seq),
  KEY host (host),
  KEY timestamp (timestamp),
  KEY host_timestamp (host,timestamp)
) ENGINE=InnoDB;

Most of the tables get the results I'm expecting:

Last hour:

SELECT count(seq) as thecount FROM syslog.switchlogs WHERE timestamp>=DATE_SUB(NOW(),INTERVAL 1 HOUR);

Last minute:

SELECT count(seq) as thecount FROM syslog.switchlogs WHERE timestamp>=DATE_SUB(NOW(),INTERVAL 1 MINUTE);

Last second:

SELECT count(seq) as thecount FROM syslog.switchlogs WHERE timestamp>=DATE_SUB(NOW(),INTERVAL 1 SECOND);

I get results like this:

Hour: 804
Minute: 16
Second: 1

One of my tables has VMware logs and I get odd results from counts...

Hour: 30,180
Minute: 24,278
Second: 24,160

That's obviously wrong. If I look at the last 50 logs in the table there's only 10 of them in the last second and 39 in the last minute. Why is the SQL above not working as expected?

Jade Rampulla
  • 1
  • 1

0 Answers0