Rev 1 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
/*
SQL information for the sysinfo module of CAMP
Same conventions as in the root SQL, ie
tablename_id - Primary key for table
added_date - Date the record was added
removed_date - Date record was removed/superceded
*/
drop table if exists unknown_entry;
create table unknown_entry (
unknown_entry_id int(10) unsigned not null auto_increment,
client_name varchar(64) not null comment 'Name of the client from the report',
device_name varchar(64) not null comment 'Name of a device from the report',
report_date datetime not null comment 'The date of the report that found this entry',
processed_date datetime default null comment 'When set the entry has been taken care of by a user',
processed_by int unsigned comment 'The user who processed this entry' references login(login_id),
primary key (unknown_entry_id)
) comment='Used to store information on equipment and clients if it is not in the database';
DROP TABLE IF EXISTS device_alias;
CREATE TABLE device_alias (
device_alias_id int(10) unsigned NOT NULL auto_increment,
device_id int(10) unsigned NOT NULL REFERENCES device(device_id),
alias varchar(64) comment 'Name found on report',
added_date datetime not null,
removed_date datetime default null,
PRIMARY KEY (device_alias_id)
) comment='If the same device has a name change this will track that';
alter table device_alias add index (device_id);
alter table device_alias add index (added_date,removed_date);
DROP TABLE IF EXISTS client_alias;
CREATE TABLE client_alias (
client_alias_id int(10) unsigned NOT NULL auto_increment,
client_id int(10) unsigned NOT NULL REFERENCES client(client_id),
alias varchar(64) comment 'Name found on report',
added_date datetime not null,
removed_date datetime default null,
PRIMARY KEY (client_alias_id)
) comment='If the same client has a name change this will track that';
DROP TABLE IF EXISTS computer_uptime;
CREATE TABLE computer_uptime (
computer_uptime_id int(10) unsigned NOT NULL auto_increment,
device_id int(10) unsigned NOT NULL REFERENCES device(device_id),
last_reboot datetime default NULL COMMENT 'date and time of last reboot',
added_date datetime default NULL COMMENT 'date record was added',
removed_date datetime default NULL COMMENT 'date record was deleted/supserceded',
PRIMARY KEY (computer_uptime_id)
) COMMENT='Tracks the computer uptime by storing the last reboot';
alter table computer_uptime add index (device_id);
alter table computer_uptime add index (added_date,removed_date);
DROP TABLE IF EXISTS device_operating_system;
CREATE TABLE device_operating_system (
device_id int(10) unsigned NOT NULL REFERENCES device(device_id),
operating_system_id int(10) unsigned NOT NULL COMMENT 'a link to the operating_system table',
added_date datetime default NULL COMMENT 'date record was added',
removed_date datetime default null COMMENT 'date record was deleted/supserceded',
PRIMARY KEY (device_id,operating_system_id,removed_date)
) COMMENT='links devices and their operating systems together';
DROP TABLE IF EXISTS disk_info;
CREATE TABLE disk_info (
disk_info_id int(10) unsigned NOT NULL auto_increment,
device_id int(10) unsigned NOT NULL REFERENCES device(device_id),
disk_device varchar(64) default NULL COMMENT 'actual device name generally partition name',
filesystem varchar(32) default NULL COMMENT 'the file system ext3 ntfs with which this if formatted',
mount_point varchar(64) default NULL COMMENT 'place on file system where this is mounted or drive letter for Windows',
capacity decimal(12,0) default NULL COMMENT 'size in kilobytes of partition',
added_date datetime default NULL COMMENT 'date record was added',
removed_date datetime default NULL COMMENT 'date record was deleted/supserceded',
PRIMARY KEY (disk_info_id),
key (device_id),
key (added_date,removed_date)
) COMMENT='stores static information about a hard disk ie capacity';
DROP TABLE IF EXISTS disk_space;
CREATE TABLE disk_space (
disk_space_id int(10) unsigned NOT NULL auto_increment,
disk_info_id int(10) unsigned NOT NULL references disk_info(disk_info_id),
space_used decimal(12,0) default NULL COMMENT 'space used in kilobytes',
added_date datetime default NULL COMMENT 'date record was added',
removed_date datetime default NULL COMMENT 'date record was deleted/supserceded',
PRIMARY KEY (disk_space_id),
KEY (disk_info_id,added_date)
) COMMENT='stores dynamic information about a partition ie amount of u';
DROP TABLE IF EXISTS installed_packages;
CREATE TABLE installed_packages (
installed_packages_id int(10) unsigned NOT NULL auto_increment,
device_id int(10) unsigned NOT NULL REFERENCES device(device_id),
software_id int(10) unsigned NOT NULL COMMENT 'link into software table',
software_version_id int(10) unsigned NOT NULL COMMENT 'link into software_version table',
added_date datetime default NULL COMMENT 'date record was added',
removed_date datetime default NULL COMMENT 'date record was deleted/supserceded',
PRIMARY KEY (installed_packages_id)
) COMMENT='tracks software packages installed on a computer';
DROP TABLE IF EXISTS network;
CREATE TABLE network (
network_id int(10) unsigned NOT NULL auto_increment,
device_id int(10) unsigned NOT NULL REFERENCES device(device_id),
interface varchar(32) default NULL comment 'the interface. eth0 whatever used. Note that some interface names can be long',
address char(15) default NULL comment 'The IP4 Address associated here',
netmask char(15) default NULL comment 'The subnet netmask',
ip6 varchar(32) default NULL comment 'If provided the IP6 address',
ip6net int(10) unsigned default NULL comment 'If provided the IP6 subnet mask',
mtu int(10) unsigned default NULL comment 'MTU value',
mac varchar(20) default NULL comment 'MAC hardware address',
added_date datetime default NULL,
removed_date datetime default NULL,
PRIMARY KEY (network_id),
KEY device_id (device_id,added_date)
) comment='Tracks multiple physical and virtual network interfaces on a machine';
DROP TABLE IF EXISTS operating_system;
CREATE TABLE operating_system (
operating_system_id int(10) unsigned NOT NULL auto_increment,
name varchar(255) default NULL COMMENT 'Name of the OS',
version text COMMENT 'Version. Note this is a very large field in some cases',
kernel varchar(255) default NULL COMMENT 'kernel information',
distro varchar(255) default NULL COMMENT 'For Linux the distribution name',
distro_description varchar(255) default NULL COMMENT 'For Linux the distribution description',
distro_release varchar(255) default NULL COMMENT 'For Linux the distribution release',
distro_codename varchar(255) default NULL COMMENT 'For Linux the distribution code name',
added_date datetime default NULL COMMENT 'date record was added',
removed_date datetime default NULL COMMENT 'date record was closed',
PRIMARY KEY (operating_system_id)
) COMMENT='used to track the operating system version on computers';
DROP TABLE IF EXISTS software;
CREATE TABLE software (
software_id int(10) unsigned NOT NULL auto_increment,
package_name varchar(64) default NULL COMMENT 'actual name of software package',
description varchar(64) default NULL COMMENT 'any additional description',
added_date datetime default NULL COMMENT 'date record was added',
removed_date datetime default NULL COMMENT 'date record was deleted/supserceded',
PRIMARY KEY (software_id)
) COMMENT='master table containing information about various software p';
DROP TABLE IF EXISTS software_version;
CREATE TABLE software_version (
software_version_id int(10) unsigned NOT NULL auto_increment,
version varchar(64) default NULL COMMENT 'version number',
added_date datetime default NULL COMMENT 'date record was added',
removed_date datetime default NULL COMMENT 'date record was deleted/supserceded',
PRIMARY KEY (software_version_id)
) COMMENT='Master table to simply track version numbers';
DROP TABLE IF EXISTS sysinfo_report;
CREATE TABLE sysinfo_report (
sysinfo_report_id int(10) unsigned NOT NULL auto_increment,
device_id int(10) unsigned NOT NULL REFERENCES device(device_id),
version varchar(5) not null default '1.0' comment 'The version of the client',
report_date datetime default NULL,
added_date datetime default NULL,
PRIMARY KEY (sysinfo_report_id),
KEY device_id (device_id,report_date)
) comment='Tracks when reports come in for each device';
/* Now, populate the tables */
insert into _system ( group_name,key_name,theValue,added_date) values ( 'Modules', 'Sysinfo', 'modules/sysinfo/', now() );
insert into menu (parent_id,caption,url) values (null,'SysInfo','/modules/sysinfo/index.html');
insert into menu (parent_id,caption,url) select menu_id,'Manual Upload','/modules/sysinfo/index.html' from menu where caption = 'SysInfo';
insert into menu (parent_id,caption,url) select menu_id,'Unknowns','/modules/sysinfo/process_unknowns.html' from menu where caption = 'SysInfo';
insert into menu (parent_id,caption,url) select menu_id,'Download','/modules/sysinfo/get_client.html' from menu where caption = 'SysInfo';
/* define reports */
/* Installed Packages list report */
insert into report ( name, query, parameters ) values ('Installed Packages','select client_site.name Client,
device.name Device,
software.package_name Package,
software_version.version Version
from client_site join device using (site_id)
join installed_packages using (device_id)
join software using (software_id)
join software_version using (software_version_id)
where device.device_id = <device_id>
order by package_name,version','device_id++System++select * from client_systems order by device++select name from device where device_id=<value>');
/* Count of packages currently installed on all systems */
insert into report ( name, query, parameters ) values ('Software Install Counts','select software.software_id ID,
software.package_name Package,
count(*) Installations
from installed_packages join software using (software_id)
where installed_packages.removed_date is null
group by software.software_id,package_name
order by software.package_name','');
/* Find what was on a system during a particular date */
insert into report ( name, query, parameters ) values ('Software Revisions for System','select date(installed_packages.added_date) \'Added\',
date(installed_packages.removed_date) \'Removed\',
device.name \'Device\',
package_name \'Package\',
version \'Version\'
from installed_packages join software using (software_id)
join software_version using (software_version_id)
join device using (device_id)
join site using (site_id)
join client using (client_id)
where device.device_id = <device_id>
and (installed_packages.removed_date is null or
installed_packages.removed_date > <end_date>)
and installed_packages.added_date < <end_date>
and installed_packages.added_date >= <start_date>
order by device.name,package_name,installed_packages.added_date
','device_id++System++select * from client_systems order by device++select name from device where device_id=<value>
start_date++Start Date++++
end_date++End Date++++');
/* Current Disk Usage */
insert into report ( name, query, parameters ) values ('Current Disk Usage','select device.name,
date(disk_space.added_date) \'Date Added\',
lpad(format(space_used/capacity*100, 0), 3, \' \') \"used %\",
lpad(format(space_used/1048576,2), 10, \' \') used,
lpad(format(capacity/1048576,2), 10, \' \') Part_Size,
mount_point,
disk_device,
filesystem
from device join disk_info on disk_info.device_id = device.device_id
join disk_space on disk_info.disk_info_id=disk_space.disk_info_id
where disk_info.removed_date is null
and device.removed_date is null
and disk_space.added_date = (select max(added_date) from disk_space b where b.disk_info_id = disk_space.disk_info_id)
order by device.name,disk_device
','');
/* Following reports are added to the Device Display in the main module. ORDER IS IMPORTANT as the first reports will be the first */
/* ones displayed. */
/* Sysinfo Reports */
insert into report( name, query, parameters, screen_report ) values ('Sysinfo Reports','select min(concat(date(report_date),\' (v\', version, \')\' )) "First Sysinfo Report", max(concat(date(report_date),\' (v\', version, \')\' )) "Latest Sysinfo Report" from sysinfo_report where device_id = <device_id>',
'device_id++Device++select device_id,name from device where removed_date is null++select name from device where device_id = <value>', 1);
/* Network Report */
insert into report( name, query, parameters, screen_report ) values ('Device Network','select interface "Port",address "IP Address",netmask "Netmask",mac "Mac" from network where removed_date is null and device_id = <device_id>',
'device_id++Device++select device_id,name from device where removed_date is null++select name from device where device_id = <value>', 1);
/* Current Disk Usage */
insert into report ( name, query, parameters,screen_report ) values ('Device Disk Usage','select date(disk_space.added_date) \'Date Added\',
lpad(format(space_used/capacity*100, 0), 3, \' \') "used %",
lpad(format(space_used/1048576,2), 10, \' \') used,
lpad(format(capacity/1048576,2), 10, \' \') Part_Size,
mount_point,
disk_device,
filesystem
from disk_info join disk_space using (disk_info_id)
where disk_info.device_id = <device_id>
and disk_info.removed_date is null
and disk_space.added_date = (select max(report_date) from sysinfo_report where device_id = <device_id>)
order by disk_device',
'device_id++Device++select device_id,name from device where removed_date is null++select name from device where device_id = <value>', 1
);
insert into report( name, query, parameters, screen_report ) values ('Device PCI','select device_attrib.value Slot,
device.name Card
from device join device_attrib using (device_id)
join attrib using (attrib_id)
where part_of = <device_id>
and attrib.name = \'Slot\'
and device.removed_date is null
order by device_attrib.value',
'device_id++Device++select device_id,name from device where removed_date is null++select name from device where device_id = <value>', 1);
insert into _system( group_name,key_name,theValue,added_date ) values ( 'device view', 'SysInfo', 'callable.php:sysinfoView', now() );