Currently I am using syslog-sql to store syslog data in a mysql
database. The table format is something like:
ID hostname facility priority date message
ID is auto incrementing
Now I am writting a perl app to calculate stats based on the data in the
table which will get run everyday. It is currently doing a loop, here
is an example:
Select count(facility) from syslog WHERE machine = '$srv' AND facility =
'$fac' AND date1 > (NOW() - INTERVAL 24 hour)
Now $srv is the name of the host and $fac is the facility name. This
select statement is in a loop that loops through each server and each
When this is running it puts a load on the DB, since there could be
about 20 host, each with 6 facilities, which equals about:
600 - The number of times that the select statement would be run ....
I suppose that running the query:
select host,facility from WHERE date1 > (NOW() - INTERVAL 24 hour)
and letting perl do the math on the selected results would be less of a
system load ?
Is there a better way ?
Also I am going to display the results using the following format:
hostname host A host B ...
facility A X X X
facility B X x X
facility C X X X
Is there an easy way to put that into a mysql table ??