Difference between revisions of "Virtual Infrastructure DB Project"

Jump to navigation Jump to search

Virtual Infrastructure DB Project (view source)

Revision as of 10:00, 14 March 2011

5,144 bytes removed ,  10:00, 14 March 2011
Reorganised page, moved component scripts to Releases table
(→‎Intra-Day Status: Move script to subpage)
(Reorganised page, moved component scripts to Releases table)
Line 7: Line 7:
# PHP front end (delivered by Apache running on same Ubuntu VM)
# PHP front end (delivered by Apache running on same Ubuntu VM)


== Database ==
== Releases ==
<source lang="mysql">
{|cellpadding="4" cellspacing="0" border="1"
CREATE DATABASE vi;
|- style="background-color:#bbddff;"
! Release 
! D'base
!colspan="2"| Data Collection
!colspan="3"| Presentation (Main)
!colspan="3"| Presentation (Other)
|-
! Version 1
| [[VI-DB_vi-db-initial|initial]]
| [[VI-DB_db-base-data-v1.0.ps1|db-base-data-v1.0.ps1]]
| [[VI-DB_db-esx-check-v4.ps1|db-esx-check-v4.ps1]]
| [[VI-DB_index.php_v1|index.php v1]]
| [[VI-DB_status.php_v1|status.php v1]]
| [[VI-DB_alpha.css_v1|alpha.css v1]]
| [[VI-DB_vc-detail.php_v1|vc-detail.php v1]], [[VI-DB_cluster-detail.php_v1|cluster-detail.php v1]], [[VI-DB_vm-detail.php_v1|vm-detail.php v1]]
|}


# VC table - this is the only table that must be manually populated
CREATE TABLE vc (vcid TINYINT UNSIGNED AUTO_INCREMENT, name VARCHAR(32), site VARCHAR(3), ver VARCHAR(7), build INT UNSIGNED, last_pass DATETIME, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vcid), UNIQUE KEY (name));


# Cluster table
[[Category:VMware]]
CREATE TABLE cluster (clid TINYINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, ha BOOL, drs VARCHAR(18), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (clid), UNIQUE KEY (vcid, name));
 
# VM tables
CREATE TABLE vm (vmid INT UNSIGNED AUTO_INCREMENT, uuid VARCHAR(37), vmvcid SMALLINT UNSIGNED, pid VARCHAR(37), name VARCHAR(32), exist BOOL, is_on BOOL, vcid TINYINT UNSIGNED, clid TINYINT UNSIGNED, esxid SMALLINT UNSIGNED, hostname VARCHAR(255), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid), UNIQUE KEY (uuid, pid), UNIQUE KEY (vmvcid, vcid));
 
CREATE TABLE vm_ext (vmid INT UNSIGNED, cpu TINYINT UNSIGNED, mem INT UNSIGNED, vc_path VARCHAR(512), vmx_path VARCHAR(512), osid SMALLINT UNSIGNED, scsi_hw VARCHAR(16), notes VARCHAR(512), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid));
 
# ESX tables
CREATE TABLE esx (esxid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, clid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, state VARCHAR(16), ver VARCHAR(7), build INT UNSIGNED, model VARCHAR(32), cpu_core TINYINT UNSIGNED, cpu_sock TINYINT UNSIGNED, mem INT UNSIGNED COMMENT 'MB', cpu_model VARCHAR(128), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (esxid), UNIQUE KEY (vcid, clid, name));
 
CREATE TABLE esx_state (esxid SMALLINT UNSIGNED, state VARCHAR(16), status VARCHAR(6), cpu_pc TINYINT, mem_pc TINYINT, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (esxid));
 
CREATE TABLE esx2ds (esxid SMALLINT UNSIGNED, dsid SMALLINT UNSIGNED, PRIMARY KEY (esxid, dsid));
CREATE TABLE esx2nw (esxid SMALLINT UNSIGNED, nwid SMALLINT UNSIGNED, PRIMARY KEY (esxid, nwid));
 
# Datastore and VMDK tables
CREATE TABLE ds (dsid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, size INT UNSIGNED, used INT UNSIGNED, san_model VARCHAR(32), lun TINYINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (dsid), UNIQUE KEY (vcid, name));
 
CREATE TABLE vmdk (dkid INT UNSIGNED AUTO_INCREMENT, dsid SMALLINT UNSIGNED, vmid INT UNSIGNED, num SMALLINT UNSIGNED, size INT UNSIGNED, thin BOOL, path VARCHAR(256), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (dkid), UNIQUE KEY (vmid, num));
 
# Networking tables
CREATE TABLE nw (nwid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(64), exist BOOL, vlan SMALLINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (nwid), UNIQUE KEY (vcid, name));
 
CREATE TABLE vm_nic (vnicid INT UNSIGNED AUTO_INCREMENT, vmid INT UNSIGNED, num SMALLINT UNSIGNED, nwid SMALLINT UNSIGNED, type VARCHAR(16), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vnicid), UNIQUE KEY (vmid, num));
CREATE TABLE vm2ip (vmid INT UNSIGNED, vnicid INT UNSIGNED, ip INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, vnicid));
 
CREATE TABLE vm2ip (vmid INT UNSIGNED, vnicid INT UNSIGNED, ip INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, vnicid));
 
# Other tables
CREATE TABLE os (osid TINYINT UNSIGNED AUTO_INCREMENT, guest_name VARCHAR(128), short_name VARCHAR(32), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (osid), UNIQUE KEY (guest_name));
 
CREATE TABLE script_run (runid INT UNSIGNED AUTO_INCREMENT, start DATETIME, end DATETIME, version VARCHAR(8), error INT UNSIGNED, warn INT UNSIGNED, PRIMARY KEY (runid));
 
CREATE TABLE cluster_stats (clid TINYINT UNSIGNED, dated DATE, esxs TINYINT UNSIGNED, vms_exist SMALLINT UNSIGNED, vms_on SMALLINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (clid, dated));
 
CREATE TABLE snap (vmid INT UNSIGNED, sid INT UNSIGNED, vcid TINYINT UNSIGNED, name VARCHAR(256), descr VARCHAR(1024), created DATETIME, quiesced BOOL, vm_on BOOL, size MEDIUMINT UNSIGNED, parent_sid INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, sid), UNIQUE KEY (vcid, sid));
 
# Eventing
CREATE DEFINER=`root`@`192.168.196.1` PROCEDURE `update_cluster_stats`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE clust_id INT;
    DECLARE clust CURSOR FOR SELECT clid FROM cluster;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   
    OPEN clust;
   
    read_loop: LOOP
        FETCH clust INTO clust_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
       
        INSERT INTO cluster_stats (clid, dated, esxs, vms_exist, vms_on)
            SELECT clust_id, NOW(),
            (SELECT COUNT(*) FROM esx WHERE clid=clust_id AND exist=1) AS esxs,
            COUNT(*) AS vms_exist, SUM(IF(is_on=1,1,0)) AS vms_on
            FROM vm WHERE clid=clust_id AND exist=1;
       
    END LOOP;
   
    CLOSE clust;
END
 
CREATE EVENT do_update_cluster_stats ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE, '23:30:00') DO CALL update_cluster_stats();
</source>
 
== Data Collection ==
=== Main Daily Script ===
 
[[VI-DB_db-base-data-v1.0.ps1|db-base-data-v1.0.ps1]]
 
=== Intra-Day Status ===
[[VI-DB_db-esx-check-v4.ps1|db-esx-check-v4.ps1]]
Retrieved from ‘https://vwiki.co.uk/index.php?title=Special:MobileDiff/1219’

Navigation menu