Subversion Repositories computer_asset_manager_v1

/*

   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);