querydump.sql

/* MFDAT AWS DB */
SELECT systemid, count(*)
FROM hp
GROUP BY systemid
ORDER BY systemid ASC;

SELECT count(DISTINCT systemid)
FROM hp;

GRANT ALL ON heath_scores TO backend;

SELECT count(a.GABE)
FROM(
SELECT count(*) AS GABE
FROM health_scores
WHERE health_score>800
GROUP BY systemid) a;

CREATE TABLE hp251 AS
SELECT *
FROM hp
WHERE systemid=251;

SELECT *
FROM HP251DT;

SELECT *
FROM HP251Scores;

SELECT * from health_scores WHERE systemid=251 LIMIT 10000;

CREATE TABLE Normalized_Bandwith_Dates AS
SELECT h.systemid, h."from", h."to", round(h.porttotalbandwidthmbps,5) AS porttotalbandwidthmbps, round(st.maxporttotalbandwidthmbps, 5) AS maxporttotalbandwidthmbps, round(h.porttotalbandwidthmbps/st.maxporttotalbandwidthmbps, 7) AS normalizedBandwidth
FROM hp_stats st, hp h
WHERE h.systemid=st.systemid AND st.maxporttotalbandwidthmbps!=0
ORDER BY h.systemid ASC;

SELECT count(*)
FROM hp
WHERE systemid=1;

CREATE TABLE hp_stats AS
  SELECT systemID, min(vvCountHistVlun) AS MINvvCountHistVlun, max(vvCountHistVlun) AS MAXvvCountHistVlun, stddev(vvCountHistVlun) AS STDDEVvvCountHistVlun, avg(vvCountHistVlun) AS AVGvvCountHistVlun, min(vvCountHistVlunPrevious) AS MINvvCountHistVlunPrevious, max(vvCountHistVlunPrevious) AS MAXvvCountHistVlunPrevious, stddev(vvCountHistVlunPrevious) AS STDDEVvvCountHistVlunPrevious, avg(vvCountHistVlunPrevious) AS AVGvvCountHistVlunPrevious, min(totalWriteIOsHistVlun) AS MINtotalWriteIOsHistVlun, max(totalWriteIOsHistVlun) AS MAXtotalWriteIOsHistVlun, stddev(totalWriteIOsHistVlun) AS STDDEVtotalWriteIOsHistVlun, avg(totalWriteIOsHistVlun) AS AVGtotalWriteIOsHistVlun, min(totalWriteIOsHistPortTargets) AS MINtotalWriteIOsHistPortTargets, max(totalWriteIOsHistPortTargets) AS MAXtotalWriteIOsHistPortTargets, stddev(totalWriteIOsHistPortTargets) AS STDDEVtotalWriteIOsHistPortTargets, avg(totalWriteIOsHistPortTargets) AS AVGtotalWriteIOsHistPortTargets, min(delAcks) AS MINdelAcks, max(delAcks) AS MAXdelAcks, stddev(delAcks) AS STDDEVdelAcks, avg(delAcks) AS AVGdelAcks, min(delAcksPct) AS MINdelAcksPct, max(delAcksPct) AS MAXdelAcksPct, stddev(delAcksPct) AS STDDEVdelAcksPct, avg(delAcksPct) AS AVGdelAcksPct, min(writesGt16s) AS MINwritesGt16s, max(writesGt16s) AS MAXwritesGt16s, stddev(writesGt16s) AS STDDEVwritesGt16s, avg(writesGt16s) AS AVGwritesGt16s, min(writesGt32msPct) AS MINwritesGt32msPct, max(writesGt32msPct) AS MAXwritesGt32msPct, stddev(writesGt32msPct) AS STDDEVwritesGt32msPct, avg(writesGt32msPct) AS AVGwritesGt32msPct, min(writesGt64msPct) AS MINwritesGt64msPct, max(writesGt64msPct) AS MAXwritesGt64msPct, stddev(writesGt64msPct) AS STDDEVwritesGt64msPct, avg(writesGt64msPct) AS AVGwritesGt64msPct, min(writesGt128msPct) AS MINwritesGt128msPct, max(writesGt128msPct) AS MAXwritesGt128msPct, stddev(writesGt128msPct) AS STDDEVwritesGt128msPct, avg(writesGt128msPct) AS AVGwritesGt128msPct, min(writesGt256msPct) AS MINwritesGt256msPct, max(writesGt256msPct) AS MAXwritesGt256msPct, stddev(writesGt256msPct) AS STDDEVwritesGt256msPct, avg(writesGt256msPct) AS AVGwritesGt256msPct, min(writesGt512msPct) AS MINwritesGt512msPct, max(writesGt512msPct) AS MAXwritesGt512msPct, stddev(writesGt512msPct) AS STDDEVwritesGt512msPct, avg(writesGt512msPct) AS AVGwritesGt512msPct, min(writesGt1024msPct) AS MINwritesGt1024msPct, max(writesGt1024msPct) AS MAXwritesGt1024msPct, stddev(writesGt1024msPct) AS STDDEVwritesGt1024msPct, avg(writesGt1024msPct) AS AVGwritesGt1024msPct, min(writesGt2048msPct) AS MINwritesGt2048msPct, max(writesGt2048msPct) AS MAXwritesGt2048msPct, stddev(writesGt2048msPct) AS STDDEVwritesGt2048msPct, avg(writesGt2048msPct) AS AVGwritesGt2048msPct, min(writesGt4096msPct) AS MINwritesGt4096msPct, max(writesGt4096msPct) AS MAXwritesGt4096msPct, stddev(writesGt4096msPct) AS STDDEVwritesGt4096msPct, avg(writesGt4096msPct) AS AVGwritesGt4096msPct, min(writes0_062msPct) AS MINwrites0_062msPct, max(writes0_062msPct) AS MAXwrites0_062msPct, stddev(writes0_062msPct) AS STDDEVwrites0_062msPct, avg(writes0_062msPct) AS AVGwrites0_062msPct, min(writes0_125msPct) AS MINwrites0_125msPct, max(writes0_125msPct) AS MAXwrites0_125msPct, stddev(writes0_125msPct) AS STDDEVwrites0_125msPct, avg(writes0_125msPct) AS AVGwrites0_125msPct, min(writes0_25msPct) AS MINwrites0_25msPct, max(writes0_25msPct) AS MAXwrites0_25msPct, stddev(writes0_25msPct) AS STDDEVwrites0_25msPct, avg(writes0_25msPct) AS AVGwrites0_25msPct, min(writes0_5msPct) AS MINwrites0_5msPct, max(writes0_5msPct) AS MAXwrites0_5msPct, stddev(writes0_5msPct) AS STDDEVwrites0_5msPct, avg(writes0_5msPct) AS AVGwrites0_5msPct, min(writes1msPct) AS MINwrites1msPct, max(writes1msPct) AS MAXwrites1msPct, stddev(writes1msPct) AS STDDEVwrites1msPct, avg(writes1msPct) AS AVGwrites1msPct, min(writes2msPct) AS MINwrites2msPct, max(writes2msPct) AS MAXwrites2msPct, stddev(writes2msPct) AS STDDEVwrites2msPct, avg(writes2msPct) AS AVGwrites2msPct, min(writes4msPct) AS MINwrites4msPct, max(writes4msPct) AS MAXwrites4msPct, stddev(writes4msPct) AS STDDEVwrites4msPct, avg(writes4msPct) AS AVGwrites4msPct, min(writes8msPct) AS MINwrites8msPct, max(writes8msPct) AS MAXwrites8msPct, stddev(writes8msPct) AS STDDEVwrites8msPct, avg(writes8msPct) AS AVGwrites8msPct, min(writes16msPct) AS MINwrites16msPct, max(writes16msPct) AS MAXwrites16msPct, stddev(writes16msPct) AS STDDEVwrites16msPct, avg(writes16msPct) AS AVGwrites16msPct, min(writes32msPct) AS MINwrites32msPct, max(writes32msPct) AS MAXwrites32msPct, stddev(writes32msPct) AS STDDEVwrites32msPct, avg(writes32msPct) AS AVGwrites32msPct, min(writes64msPct) AS MINwrites64msPct, max(writes64msPct) AS MAXwrites64msPct, stddev(writes64msPct) AS STDDEVwrites64msPct, avg(writes64msPct) AS AVGwrites64msPct, min(writes128msPct) AS MINwrites128msPct, max(writes128msPct) AS MAXwrites128msPct, stddev(writes128msPct) AS STDDEVwrites128msPct, avg(writes128msPct) AS AVGwrites128msPct, min(writes256msPct) AS MINwrites256msPct, max(writes256msPct) AS MAXwrites256msPct, stddev(writes256msPct) AS STDDEVwrites256msPct, avg(writes256msPct) AS AVGwrites256msPct, min(writes512msPct) AS MINwrites512msPct, max(writes512msPct) AS MAXwrites512msPct, stddev(writes512msPct) AS STDDEVwrites512msPct, avg(writes512msPct) AS AVGwrites512msPct, min(writes1024msPct) AS MINwrites1024msPct, max(writes1024msPct) AS MAXwrites1024msPct, stddev(writes1024msPct) AS STDDEVwrites1024msPct, avg(writes1024msPct) AS AVGwrites1024msPct, min(writes2048msPct) AS MINwrites2048msPct, max(writes2048msPct) AS MAXwrites2048msPct, stddev(writes2048msPct) AS STDDEVwrites2048msPct, avg(writes2048msPct) AS AVGwrites2048msPct, min(writes4096msPct) AS MINwrites4096msPct, max(writes4096msPct) AS MAXwrites4096msPct, stddev(writes4096msPct) AS STDDEVwrites4096msPct, avg(writes4096msPct) AS AVGwrites4096msPct, min(writes8192msPct) AS MINwrites8192msPct, max(writes8192msPct) AS MAXwrites8192msPct, stddev(writes8192msPct) AS STDDEVwrites8192msPct, avg(writes8192msPct) AS AVGwrites8192msPct, min(writes16384msPct) AS MINwrites16384msPct, max(writes16384msPct) AS MAXwrites16384msPct, stddev(writes16384msPct) AS STDDEVwrites16384msPct, avg(writes16384msPct) AS AVGwrites16384msPct, min(writes32768msPct) AS MINwrites32768msPct, max(writes32768msPct) AS MAXwrites32768msPct, stddev(writes32768msPct) AS STDDEVwrites32768msPct, avg(writes32768msPct) AS AVGwrites32768msPct, min(writes65536msPct) AS MINwrites65536msPct, max(writes65536msPct) AS MAXwrites65536msPct, stddev(writes65536msPct) AS STDDEVwrites65536msPct, avg(writes65536msPct) AS AVGwrites65536msPct, min(readsGt32msPct) AS MINreadsGt32msPct, max(readsGt32msPct) AS MAXreadsGt32msPct, stddev(readsGt32msPct) AS STDDEVreadsGt32msPct, avg(readsGt32msPct) AS AVGreadsGt32msPct, min(readsGt64msPct) AS MINreadsGt64msPct, max(readsGt64msPct) AS MAXreadsGt64msPct, stddev(readsGt64msPct) AS STDDEVreadsGt64msPct, avg(readsGt64msPct) AS AVGreadsGt64msPct, min(readsGt128msPct) AS MINreadsGt128msPct, max(readsGt128msPct) AS MAXreadsGt128msPct, stddev(readsGt128msPct) AS STDDEVreadsGt128msPct, avg(readsGt128msPct) AS AVGreadsGt128msPct, min(readsGt256msPct) AS MINreadsGt256msPct, max(readsGt256msPct) AS MAXreadsGt256msPct, stddev(readsGt256msPct) AS STDDEVreadsGt256msPct, avg(readsGt256msPct) AS AVGreadsGt256msPct, min(readsGt512msPct) AS MINreadsGt512msPct, max(readsGt512msPct) AS MAXreadsGt512msPct, stddev(readsGt512msPct) AS STDDEVreadsGt512msPct, avg(readsGt512msPct) AS AVGreadsGt512msPct, min(readsGt1024msPct) AS MINreadsGt1024msPct, max(readsGt1024msPct) AS MAXreadsGt1024msPct, stddev(readsGt1024msPct) AS STDDEVreadsGt1024msPct, avg(readsGt1024msPct) AS AVGreadsGt1024msPct, min(readsGt2048msPct) AS MINreadsGt2048msPct, max(readsGt2048msPct) AS MAXreadsGt2048msPct, stddev(readsGt2048msPct) AS STDDEVreadsGt2048msPct, avg(readsGt2048msPct) AS AVGreadsGt2048msPct, min(readsGt4096msPct) AS MINreadsGt4096msPct, max(readsGt4096msPct) AS MAXreadsGt4096msPct, stddev(readsGt4096msPct) AS STDDEVreadsGt4096msPct, avg(readsGt4096msPct) AS AVGreadsGt4096msPct, min(reads0_062msPct) AS MINreads0_062msPct, max(reads0_062msPct) AS MAXreads0_062msPct, stddev(reads0_062msPct) AS STDDEVreads0_062msPct, avg(reads0_062msPct) AS AVGreads0_062msPct, min(reads0_125msPct) AS MINreads0_125msPct, max(reads0_125msPct) AS MAXreads0_125msPct, stddev(reads0_125msPct) AS STDDEVreads0_125msPct, avg(reads0_125msPct) AS AVGreads0_125msPct, min(reads0_25msPct) AS MINreads0_25msPct, max(reads0_25msPct) AS MAXreads0_25msPct, stddev(reads0_25msPct) AS STDDEVreads0_25msPct, avg(reads0_25msPct) AS AVGreads0_25msPct, min(reads0_5msPct) AS MINreads0_5msPct, max(reads0_5msPct) AS MAXreads0_5msPct, stddev(reads0_5msPct) AS STDDEVreads0_5msPct, avg(reads0_5msPct) AS AVGreads0_5msPct, min(reads1msPct) AS MINreads1msPct, max(reads1msPct) AS MAXreads1msPct, stddev(reads1msPct) AS STDDEVreads1msPct, avg(reads1msPct) AS AVGreads1msPct, min(reads2msPct) AS MINreads2msPct, max(reads2msPct) AS MAXreads2msPct, stddev(reads2msPct) AS STDDEVreads2msPct, avg(reads2msPct) AS AVGreads2msPct, min(reads4msPct) AS MINreads4msPct, max(reads4msPct) AS MAXreads4msPct, stddev(reads4msPct) AS STDDEVreads4msPct, avg(reads4msPct) AS AVGreads4msPct, min(reads8msPct) AS MINreads8msPct, max(reads8msPct) AS MAXreads8msPct, stddev(reads8msPct) AS STDDEVreads8msPct, avg(reads8msPct) AS AVGreads8msPct, min(reads16msPct) AS MINreads16msPct, max(reads16msPct) AS MAXreads16msPct, stddev(reads16msPct) AS STDDEVreads16msPct, avg(reads16msPct) AS AVGreads16msPct, min(reads32msPct) AS MINreads32msPct, max(reads32msPct) AS MAXreads32msPct, stddev(reads32msPct) AS STDDEVreads32msPct, avg(reads32msPct) AS AVGreads32msPct, min(reads64msPct) AS MINreads64msPct, max(reads64msPct) AS MAXreads64msPct, stddev(reads64msPct) AS STDDEVreads64msPct, avg(reads64msPct) AS AVGreads64msPct, min(reads128msPct) AS MINreads128msPct, max(reads128msPct) AS MAXreads128msPct, stddev(reads128msPct) AS STDDEVreads128msPct, avg(reads128msPct) AS AVGreads128msPct, min(reads256msPct) AS MINreads256msPct, max(reads256msPct) AS MAXreads256msPct, stddev(reads256msPct) AS STDDEVreads256msPct, avg(reads256msPct) AS AVGreads256msPct, min(reads512msPct) AS MINreads512msPct, max(reads512msPct) AS MAXreads512msPct, stddev(reads512msPct) AS STDDEVreads512msPct, avg(reads512msPct) AS AVGreads512msPct, min(reads1024msPct) AS MINreads1024msPct, max(reads1024msPct) AS MAXreads1024msPct, stddev(reads1024msPct) AS STDDEVreads1024msPct, avg(reads1024msPct) AS AVGreads1024msPct, min(reads2048msPct) AS MINreads2048msPct, max(reads2048msPct) AS MAXreads2048msPct, stddev(reads2048msPct) AS STDDEVreads2048msPct, avg(reads2048msPct) AS AVGreads2048msPct, min(reads4096msPct) AS MINreads4096msPct, max(reads4096msPct) AS MAXreads4096msPct, stddev(reads4096msPct) AS STDDEVreads4096msPct, avg(reads4096msPct) AS AVGreads4096msPct, min(reads8192msPct) AS MINreads8192msPct, max(reads8192msPct) AS MAXreads8192msPct, stddev(reads8192msPct) AS STDDEVreads8192msPct, avg(reads8192msPct) AS AVGreads8192msPct, min(reads16384msPct) AS MINreads16384msPct, max(reads16384msPct) AS MAXreads16384msPct, stddev(reads16384msPct) AS STDDEVreads16384msPct, avg(reads16384msPct) AS AVGreads16384msPct, min(reads32768msPct) AS MINreads32768msPct, max(reads32768msPct) AS MAXreads32768msPct, stddev(reads32768msPct) AS STDDEVreads32768msPct, avg(reads32768msPct) AS AVGreads32768msPct, min(reads65536msPct) AS MINreads65536msPct, max(reads65536msPct) AS MAXreads65536msPct, stddev(reads65536msPct) AS STDDEVreads65536msPct, avg(reads65536msPct) AS AVGreads65536msPct, min(totalsGt32msPct) AS MINtotalsGt32msPct, max(totalsGt32msPct) AS MAXtotalsGt32msPct, stddev(totalsGt32msPct) AS STDDEVtotalsGt32msPct, avg(totalsGt32msPct) AS AVGtotalsGt32msPct, min(totalsGt64msPct) AS MINtotalsGt64msPct, max(totalsGt64msPct) AS MAXtotalsGt64msPct, stddev(totalsGt64msPct) AS STDDEVtotalsGt64msPct, avg(totalsGt64msPct) AS AVGtotalsGt64msPct, min(totalsGt128msPct) AS MINtotalsGt128msPct, max(totalsGt128msPct) AS MAXtotalsGt128msPct, stddev(totalsGt128msPct) AS STDDEVtotalsGt128msPct, avg(totalsGt128msPct) AS AVGtotalsGt128msPct, min(totalsGt256msPct) AS MINtotalsGt256msPct, max(totalsGt256msPct) AS MAXtotalsGt256msPct, stddev(totalsGt256msPct) AS STDDEVtotalsGt256msPct, avg(totalsGt256msPct) AS AVGtotalsGt256msPct, min(totalsGt512msPct) AS MINtotalsGt512msPct, max(totalsGt512msPct) AS MAXtotalsGt512msPct, stddev(totalsGt512msPct) AS STDDEVtotalsGt512msPct, avg(totalsGt512msPct) AS AVGtotalsGt512msPct, min(totalsGt1024msPct) AS MINtotalsGt1024msPct, max(totalsGt1024msPct) AS MAXtotalsGt1024msPct, stddev(totalsGt1024msPct) AS STDDEVtotalsGt1024msPct, avg(totalsGt1024msPct) AS AVGtotalsGt1024msPct, min(totalsGt2048msPct) AS MINtotalsGt2048msPct, max(totalsGt2048msPct) AS MAXtotalsGt2048msPct, stddev(totalsGt2048msPct) AS STDDEVtotalsGt2048msPct, avg(totalsGt2048msPct) AS AVGtotalsGt2048msPct, min(totalsGt4096msPct) AS MINtotalsGt4096msPct, max(totalsGt4096msPct) AS MAXtotalsGt4096msPct, stddev(totalsGt4096msPct) AS STDDEVtotalsGt4096msPct, avg(totalsGt4096msPct) AS AVGtotalsGt4096msPct, min(totals0_062msPct) AS MINtotals0_062msPct, max(totals0_062msPct) AS MAXtotals0_062msPct, stddev(totals0_062msPct) AS STDDEVtotals0_062msPct, avg(totals0_062msPct) AS AVGtotals0_062msPct, min(totals0_125msPct) AS MINtotals0_125msPct, max(totals0_125msPct) AS MAXtotals0_125msPct, stddev(totals0_125msPct) AS STDDEVtotals0_125msPct, avg(totals0_125msPct) AS AVGtotals0_125msPct, min(totals0_25msPct) AS MINtotals0_25msPct, max(totals0_25msPct) AS MAXtotals0_25msPct, stddev(totals0_25msPct) AS STDDEVtotals0_25msPct, avg(totals0_25msPct) AS AVGtotals0_25msPct, min(totals0_5msPct) AS MINtotals0_5msPct, max(totals0_5msPct) AS MAXtotals0_5msPct, stddev(totals0_5msPct) AS STDDEVtotals0_5msPct, avg(totals0_5msPct) AS AVGtotals0_5msPct, min(totals1msPct) AS MINtotals1msPct, max(totals1msPct) AS MAXtotals1msPct, stddev(totals1msPct) AS STDDEVtotals1msPct, avg(totals1msPct) AS AVGtotals1msPct, min(totals2msPct) AS MINtotals2msPct, max(totals2msPct) AS MAXtotals2msPct, stddev(totals2msPct) AS STDDEVtotals2msPct, avg(totals2msPct) AS AVGtotals2msPct, min(totals4msPct) AS MINtotals4msPct, max(totals4msPct) AS MAXtotals4msPct, stddev(totals4msPct) AS STDDEVtotals4msPct, avg(totals4msPct) AS AVGtotals4msPct, min(totals8msPct) AS MINtotals8msPct, max(totals8msPct) AS MAXtotals8msPct, stddev(totals8msPct) AS STDDEVtotals8msPct, avg(totals8msPct) AS AVGtotals8msPct, min(totals16msPct) AS MINtotals16msPct, max(totals16msPct) AS MAXtotals16msPct, stddev(totals16msPct) AS STDDEVtotals16msPct, avg(totals16msPct) AS AVGtotals16msPct, min(totals32msPct) AS MINtotals32msPct, max(totals32msPct) AS MAXtotals32msPct, stddev(totals32msPct) AS STDDEVtotals32msPct, avg(totals32msPct) AS AVGtotals32msPct, min(totals64msPct) AS MINtotals64msPct, max(totals64msPct) AS MAXtotals64msPct, stddev(totals64msPct) AS STDDEVtotals64msPct, avg(totals64msPct) AS AVGtotals64msPct, min(totals128msPct) AS MINtotals128msPct, max(totals128msPct) AS MAXtotals128msPct, stddev(totals128msPct) AS STDDEVtotals128msPct, avg(totals128msPct) AS AVGtotals128msPct, min(totals256msPct) AS MINtotals256msPct, max(totals256msPct) AS MAXtotals256msPct, stddev(totals256msPct) AS STDDEVtotals256msPct, avg(totals256msPct) AS AVGtotals256msPct, min(totals512msPct) AS MINtotals512msPct, max(totals512msPct) AS MAXtotals512msPct, stddev(totals512msPct) AS STDDEVtotals512msPct, avg(totals512msPct) AS AVGtotals512msPct, min(totals1024msPct) AS MINtotals1024msPct, max(totals1024msPct) AS MAXtotals1024msPct, stddev(totals1024msPct) AS STDDEVtotals1024msPct, avg(totals1024msPct) AS AVGtotals1024msPct, min(totals2048msPct) AS MINtotals2048msPct, max(totals2048msPct) AS MAXtotals2048msPct, stddev(totals2048msPct) AS STDDEVtotals2048msPct, avg(totals2048msPct) AS AVGtotals2048msPct, min(totals4096msPct) AS MINtotals4096msPct, max(totals4096msPct) AS MAXtotals4096msPct, stddev(totals4096msPct) AS STDDEVtotals4096msPct, avg(totals4096msPct) AS AVGtotals4096msPct, min(totals8192msPct) AS MINtotals8192msPct, max(totals8192msPct) AS MAXtotals8192msPct, stddev(totals8192msPct) AS STDDEVtotals8192msPct, avg(totals8192msPct) AS AVGtotals8192msPct, min(totals16384msPct) AS MINtotals16384msPct, max(totals16384msPct) AS MAXtotals16384msPct, stddev(totals16384msPct) AS STDDEVtotals16384msPct, avg(totals16384msPct) AS AVGtotals16384msPct, min(totals32768msPct) AS MINtotals32768msPct, max(totals32768msPct) AS MAXtotals32768msPct, stddev(totals32768msPct) AS STDDEVtotals32768msPct, avg(totals32768msPct) AS AVGtotals32768msPct, min(totals65536msPct) AS MINtotals65536msPct, max(totals65536msPct) AS MAXtotals65536msPct, stddev(totals65536msPct) AS STDDEVtotals65536msPct, avg(totals65536msPct) AS AVGtotals65536msPct, min(portReadBandwidthMBPS) AS MINportReadBandwidthMBPS, max(portReadBandwidthMBPS) AS MAXportReadBandwidthMBPS, stddev(portReadBandwidthMBPS) AS STDDEVportReadBandwidthMBPS, avg(portReadBandwidthMBPS) AS AVGportReadBandwidthMBPS, min(portWriteBandwidthMBPS) AS MINportWriteBandwidthMBPS, max(portWriteBandwidthMBPS) AS MAXportWriteBandwidthMBPS, stddev(portWriteBandwidthMBPS) AS STDDEVportWriteBandwidthMBPS, avg(portWriteBandwidthMBPS) AS AVGportWriteBandwidthMBPS, min(portTotalBandwidthMBPS) AS MINportTotalBandwidthMBPS, max(portTotalBandwidthMBPS) AS MAXportTotalBandwidthMBPS, stddev(portTotalBandwidthMBPS) AS STDDEVportTotalBandwidthMBPS, avg(portTotalBandwidthMBPS) AS AVGportTotalBandwidthMBPS, min(cpuLatestSysAvgPct) AS MINcpuLatestSysAvgPct, max(cpuLatestSysAvgPct) AS MAXcpuLatestSysAvgPct, stddev(cpuLatestSysAvgPct) AS STDDEVcpuLatestSysAvgPct, avg(cpuLatestSysAvgPct) AS AVGcpuLatestSysAvgPct, min(cpuLatestUserAvgPct) AS MINcpuLatestUserAvgPct, max(cpuLatestUserAvgPct) AS MAXcpuLatestUserAvgPct, stddev(cpuLatestUserAvgPct) AS STDDEVcpuLatestUserAvgPct, avg(cpuLatestUserAvgPct) AS AVGcpuLatestUserAvgPct, min(cpuLatestTotalAvgPct) AS MINcpuLatestTotalAvgPct, max(cpuLatestTotalAvgPct) AS MAXcpuLatestTotalAvgPct, stddev(cpuLatestTotalAvgPct) AS STDDEVcpuLatestTotalAvgPct, avg(cpuLatestTotalAvgPct) AS AVGcpuLatestTotalAvgPct, min(cpuLatestSysMaxPct) AS MINcpuLatestSysMaxPct, max(cpuLatestSysMaxPct) AS MAXcpuLatestSysMaxPct, stddev(cpuLatestSysMaxPct) AS STDDEVcpuLatestSysMaxPct, avg(cpuLatestSysMaxPct) AS AVGcpuLatestSysMaxPct, min(cpuLatestUserMaxPct) AS MINcpuLatestUserMaxPct, max(cpuLatestUserMaxPct) AS MAXcpuLatestUserMaxPct, stddev(cpuLatestUserMaxPct) AS STDDEVcpuLatestUserMaxPct, avg(cpuLatestUserMaxPct) AS AVGcpuLatestUserMaxPct, min(cpuLatestTotalMaxPct) AS MINcpuLatestTotalMaxPct, max(cpuLatestTotalMaxPct) AS MAXcpuLatestTotalMaxPct, stddev(cpuLatestTotalMaxPct) AS STDDEVcpuLatestTotalMaxPct, avg(cpuLatestTotalMaxPct) AS AVGcpuLatestTotalMaxPct, min(portReadAvgIOSizeKB) AS MINportReadAvgIOSizeKB, max(portReadAvgIOSizeKB) AS MAXportReadAvgIOSizeKB, stddev(portReadAvgIOSizeKB) AS STDDEVportReadAvgIOSizeKB, avg(portReadAvgIOSizeKB) AS AVGportReadAvgIOSizeKB, min(portWriteAvgIOSizeKB) AS MINportWriteAvgIOSizeKB, max(portWriteAvgIOSizeKB) AS MAXportWriteAvgIOSizeKB, stddev(portWriteAvgIOSizeKB) AS STDDEVportWriteAvgIOSizeKB, avg(portWriteAvgIOSizeKB) AS AVGportWriteAvgIOSizeKB, min(portTotalAvgIOSizeKB) AS MINportTotalAvgIOSizeKB, max(portTotalAvgIOSizeKB) AS MAXportTotalAvgIOSizeKB, stddev(portTotalAvgIOSizeKB) AS STDDEVportTotalAvgIOSizeKB, avg(portTotalAvgIOSizeKB) AS AVGportTotalAvgIOSizeKB, min(ddsSizeUsedTiB) AS MINddsSizeUsedTiB, max(ddsSizeUsedTiB) AS MAXddsSizeUsedTiB, stddev(ddsSizeUsedTiB) AS STDDEVddsSizeUsedTiB, avg(ddsSizeUsedTiB) AS AVGddsSizeUsedTiB, min(nodeCountOffline) AS MINnodeCountOffline, max(nodeCountOffline) AS MAXnodeCountOffline, stddev(nodeCountOffline) AS STDDEVnodeCountOffline, avg(nodeCountOffline) AS AVGnodeCountOffline, min(nodeCountMissing) AS MINnodeCountMissing, max(nodeCountMissing) AS MAXnodeCountMissing, stddev(nodeCountMissing) AS STDDEVnodeCountMissing, avg(nodeCountMissing) AS AVGnodeCountMissing, min(ddsSizeUsedTiBPrevious) AS MINddsSizeUsedTiBPrevious, max(ddsSizeUsedTiBPrevious) AS MAXddsSizeUsedTiBPrevious, stddev(ddsSizeUsedTiBPrevious) AS STDDEVddsSizeUsedTiBPrevious, avg(ddsSizeUsedTiBPrevious) AS AVGddsSizeUsedTiBPrevious
  FROM hp
  GROUP BY systemid
  ORDER BY systemid ASC;

CREATE TABLE health_scores(
  systemId integer ,
  "from" timestamp ,
  "to" timestamp ,
  "health_score" INTEGER,
  write_score INTEGER,
  read_score INTEGER,
  cpu_bandwidth_score INTEGER,
  del_ack_score INTEGER);

SELECT h.*, NB.maxporttotalbandwidthmbps
FROM hp h, system_normalized_bandwith NB
WHERE h."from"=NB."from";

/* MFDAT DO DB */

CREATE TABLE Normalized_Bandwith_Dates AS
SELECT h.systemid, h."from", h."to", round(h.porttotalbandwidthmbps,5) AS porttotalbandwidthmbps,
      round(st.maxporttotalbandwidthmbps, 5) AS maxporttotalbandwidthmbps,
      round(h.porttotalbandwidthmbps/st.maxporttotalbandwidthmbps, 7) AS normalizedBandwidth
FROM hp_stats st, hp h
WHERE h.systemid=st.systemid AND st.maxporttotalbandwidthmbps!=0
ORDER BY h.systemid ASC;

SELECT h.*, NB.maxporttotalbandwidthmbps, NB.normalizedBandwidth
FROM hp h
FULL OUTER JOIN normalized_bandwith_dates NB
ON h."from"=NB."from" AND h."to"=NB."to" AND h.systemid=NB.systemid
ORDER BY systemid ASC, "from" ASC;

SELECT hs.*
FROM(
    SELECT systemid, max("from") AS maxDate
    FROM health_scores
    GROUP BY systemid) m,
    health_scores hs
WHERE m.systemid=hs.systemid AND m.maxDate=hs."from"
ORDER BY health_score ASC;

SELECT systemid, maxporttotalbandwidthmbps
FROM hp_stats
WHERE maxporttotalbandwidthmbps=0;

DELETE
FROM health_scores;

SELECT DISTINCT health_scores.*
FROM hpnmb, health_scores
WHERE normalizedbandwidth is NULL AND health_scores.systemid=hpnmb.systemid;