129 lines
3.8 KiB
SQL
129 lines
3.8 KiB
SQL
CREATE SCHEMA IF NOT EXISTS vmreports;
|
|
|
|
ALTER SCHEMA vmreports OWNER TO dbo;
|
|
GRANT USAGE ON SCHEMA vmreports TO nifi;
|
|
|
|
GRANT ALL ON ALL TABLES IN SCHEMA vmreports TO nifi;
|
|
GRANT ALL ON ALL SEQUENCES IN SCHEMA vmreports TO nifi;
|
|
|
|
--ALTER DEFAULT PRIVILEGES IN SCHEMA vmreports GRANT ALL ON TABLES TO nifi;
|
|
--ALTER DEFAULT PRIVILEGES IN SCHEMA vmreports GRANT ALL ON SEQUENCES TO nifi;
|
|
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE dbo, postgres IN SCHEMA vmreports GRANT ALL ON TABLES TO nifi;
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE dbo, postgres IN SCHEMA vmreports GRANT SELECT ON TABLES TO appserver;
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE dbo, postgres IN SCHEMA vmreports GRANT ALL ON SEQUENCES TO nifi;
|
|
ALTER DEFAULT PRIVILEGES FOR ROLE dbo, postgres IN SCHEMA vmreports GRANT USAGE ON SEQUENCES TO appserver;
|
|
|
|
drop table if exists vmreports.host;
|
|
create table vmreports.host(
|
|
"Name" varchar(255) NULL,
|
|
"Model" varchar(255) NULL,
|
|
"Cluster" varchar(255) NULL,
|
|
"Cluster_v2" varchar(255) NULL,
|
|
"Maintenance" varchar(255) NULL,
|
|
|
|
"CPU_Cores" float NULL, -- per host, not per socket
|
|
"CPU_Sockets" float NULL, -- not used in caclulations
|
|
"CPU_Speed" float NULL,
|
|
"RAM_Total" float NULL,
|
|
"RAM_Used" float NULL,
|
|
"CPU_Used" float NULL,
|
|
"CPU_Used_AVG" float NULL,
|
|
"CPU_Contention_95" float NULL,
|
|
"HT_Active" boolean NULL,
|
|
"CPUtoVM" float NULL,
|
|
"RAMtoVM" float NULL,
|
|
"Object_UUID" uuid NOT NULL,
|
|
ts timestamptz NOT NULL
|
|
,CONSTRAINT PK_host PRIMARY KEY (ts, "Object_UUID")
|
|
);
|
|
--grant all on vmreports.host to nifi;
|
|
|
|
drop table if exists vmreports.compute;
|
|
create table vmreports.compute (
|
|
|
|
"Name" varchar(255) NULL,
|
|
"UUID_VM" varchar(255) NULL,
|
|
"VDC" varchar(255) NULL,
|
|
"Organization_UUID" uuid NULL,
|
|
"Organization" varchar(255) NULL,
|
|
"WZ" varchar(255) NULL,
|
|
"Cluster" varchar(255) NULL,
|
|
"Host" varchar(255) NULL,
|
|
"Host_Model" varchar(255) NULL,
|
|
"CPU_Model" varchar(255) NULL,
|
|
|
|
"Host_CPU_Cores" float NULL,
|
|
"Host_CPU_Sockets" float NULL,
|
|
"Host_CPU_Speed" float NULL,
|
|
"Host_RAM" float NULL,
|
|
"Host_CPU_Used" float NULL,
|
|
"Host_CPU_Used_Avg" float NULL,
|
|
"VDC_CPU_Reserv" float NULL,
|
|
"VDC_CPU_Speed" float NULL,
|
|
"VM_vCPU_count" float NULL,
|
|
"VM_CPU_Used" float NULL,
|
|
"VM_CPU_Used_AVG" float NULL,
|
|
"VM_CPU_Reserv_GHz" float NULL,
|
|
"VM_CPU_Reserv_perc" float NULL,
|
|
"VM_RAM_Allocated" float NULL,
|
|
"VM_RAM_Used" float NULL,
|
|
"VM_is_ON" float NULL,
|
|
--"Summary": false,
|
|
--"Grand Total": false,
|
|
--"Group UUID": null,
|
|
"Object_UUID" uuid NOT NULL,
|
|
ts timestamptz NOT NULL
|
|
,CONSTRAINT PK_compute PRIMARY KEY (ts, "Object_UUID")
|
|
);
|
|
--grant all on vmreports.compute to nifi;
|
|
|
|
drop table if exists vmreports.storage;
|
|
create table vmreports.storage (
|
|
|
|
"Name" varchar(255) NULL,
|
|
"VM_UUID" varchar(255) NULL,
|
|
"VDC" varchar(255) NULL,
|
|
"Organization_UUID" uuid NULL,
|
|
"Organization" varchar(255) NULL,
|
|
"WZ" varchar(255) NULL,
|
|
"Cluster" varchar(255) NULL,
|
|
"Disk" varchar(255) NULL,
|
|
"Storage_Policy" varchar(255) NULL,
|
|
|
|
"Disk_Allocated" float NULL,
|
|
"Disk_Used" float NULL,
|
|
"VM_is_ON" float NULL,
|
|
"Object_UUID" uuid NOT NULL,
|
|
ts timestamptz NOT NULL
|
|
,CONSTRAINT PK_storage PRIMARY KEY (ts, "Object_UUID")
|
|
);
|
|
--grant all on vmreports.storage to nifi;
|
|
|
|
drop table if exists vmreports.vm_gpu;
|
|
create table vmreports.vm_gpu (
|
|
"Name" varchar(255) NULL,
|
|
"ClientID" varchar(255) NULL, --WZ
|
|
"VCD" varchar(255) NULL, --vcloud
|
|
"Org" varchar(255) NULL,
|
|
"OrgVDC" varchar(255) NULL,
|
|
"vCenter" varchar(255) NULL,
|
|
"GPU" jsonb NULL,
|
|
"OrgStatus" varchar(255) NULL,
|
|
"Object_UUID" uuid NOT NULL,
|
|
ts timestamptz NOT NULL
|
|
,CONSTRAINT PK_vm_gpu PRIMARY KEY (ts, "Object_UUID")
|
|
);
|
|
--grant all on vmreports.vm_gpu to nifi;
|
|
|
|
DROP TABLE IF EXISTS vmreports.vm_power_state;
|
|
CREATE TABLE IF NOT EXISTS vmreports.vm_power_state
|
|
(
|
|
"Object_UUID" uuid NOT NULL,
|
|
ts timestamp with time zone NOT NULL,
|
|
value float,
|
|
CONSTRAINT PK_vm_power_state PRIMARY KEY ("Object_UUID", ts)
|
|
);
|
|
--grant all on vmreports.vm_power_state to nifi;
|
|
|