MySQL Query on wich table?

[ssanna]

Hello, before posting my question i've already searched on forum similar case but the answer founded was not clear if it can be done or not. So i need to extract the "last value" data on "Latest Data" tab in monitoring. Ive do some test to do my query in history and trends_uint table but i'm not sure if is correct because the value seems not consistent. Are these the correct table where i do my query for looking about CPU usage, Ram Usage and disk space value?
Below my latest query, thanks for help.

SELECT "Server" as Server, "CPUMIN" As CPUMIN, "CPUMAX" As CPUMAX, "CPUAVG" As CPUAVG, "RAMMIN" As RAMMIN, "RAMMAX" As RAMMAX, "RAMAVG" As RAMAVG, "DISCOPERC" As DISCOPERC, "DISCO Libero" As "Disco Libero", "DISCO Totale" As "Disco Totale", "DISCO Usato" As "Disco Usato"
UNION DISTINCT
SELECT "Server1" as Server, min(value) CPUMIN, max(value) CPUMAX, AVG(value) CPUAVG,(SELECT min(value) RAMMIN FROM history WHERE itemid IN (26744) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMIN,(SELECT max(value) RAMMAX FROM history WHERE itemid IN (26744) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMAX,(SELECT avg(value) RAMAVG FROM history WHERE itemid IN (26744) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMAVG,(SELECT AVG(value) DISCOPERC FROM history WHERE itemid IN (24800) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) DISCOPERC , (select value_min from trends_uint where itemid IN (24799) order by clock desc LIMIT 1) "Disco Libero", (select value_min from trends_uint where itemid IN (24801) order by clock desc LIMIT 1) "Disco Totale", (select value_min from trends_uint where itemid IN (24802) order by clock desc LIMIT 1) "Disco Usato" FROM history WHERE itemid IN (26738) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid
UNION DISTINCT
SELECT "Server2" as Server, min(value) CPUMIN, max(value) CPUMAX, AVG(value) CPUAVG,(SELECT min(value) RAMMIN FROM history WHERE itemid IN (26733) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMIN,(SELECT max(value) RAMMAX FROM history WHERE itemid IN (26733) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMAX,(SELECT avg(value) RAMAVG FROM history WHERE itemid IN (26733) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMAVG,(SELECT AVG(value) DISCOPERC FROM history WHERE itemid IN (25433) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) DISCOPERC , (select value_min from trends_uint where itemid IN (25433) order by clock desc LIMIT 1) "Disco Libero", (select value_min from trends_uint where itemid IN (25437) order by clock desc LIMIT 1) "Disco Totale", (select value_min from trends_uint where itemid IN (25439) order by clock desc LIMIT 1) "Disco Usato" FROM history WHERE itemid IN (26727) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid
UNION DISTINCT
SELECT "Server3" as Server, min(value) CPUMIN, max(value) CPUMAX, AVG(value) CPUAVG,(SELECT min(value) RAMMIN FROM history WHERE itemid IN (26734) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMIN,(SELECT max(value) RAMMAX FROM history WHERE itemid IN (26734) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMAX,(SELECT avg(value) RAMAVG FROM history WHERE itemid IN (26734) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMAVG,(SELECT AVG(value) DISCOPERC FROM history WHERE itemid IN (25442) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) DISCOPERC , (select value_min from trends_uint where itemid IN (25441) order by clock desc LIMIT 1) "Disco Libero", (select value_min from trends_uint where itemid IN (25443) order by clock desc LIMIT 1) "Disco Totale", (select value_min from trends_uint where itemid IN (25444) order by clock desc LIMIT 1) "Disco Usato" FROM history WHERE itemid IN (26728) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid
UNION DISTINCT
SELECT "Server4" as Server, min(value) CPUMIN, max(value) CPUMAX, AVG(value) CPUAVG,(SELECT min(value) RAMMIN FROM history WHERE itemid IN (25284) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMIN,(SELECT max(value) RAMMAX FROM history WHERE itemid IN (25284) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMAX,(SELECT avg(value) RAMAVG FROM history WHERE itemid IN (25284) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMAVG,(SELECT AVG(value) DISCOPERC FROM history WHERE itemid IN (25521) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) DISCOPERC , (select value_min from trends_uint where itemid IN (25520) order by clock desc LIMIT 1) "Disco Libero", (select value_min from trends_uint where itemid IN (25522) order by clock desc LIMIT 1) "Disco Totale", (select value_min from trends_uint where itemid IN (25523) order by clock desc LIMIT 1) "Disco Usato" FROM history WHERE itemid IN (25267) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid
UNION DISTINCT
SELECT "Server5" as Server, min(value) CPUMIN, max(value) CPUMAX, AVG(value) CPUAVG,(SELECT min(value) RAMMIN FROM history WHERE itemid IN (25525) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMIN,(SELECT max(value) RAMMAX FROM history WHERE itemid IN (25525) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMAX,(SELECT avg(value) RAMAVG FROM history WHERE itemid IN (25525) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMAVG,(SELECT AVG(value) DISCOPERC FROM history WHERE itemid IN (26736) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) DISCOPERC , (select value_min from trends_uint where itemid IN (25524) order by clock desc LIMIT 1) "Disco Libero", (select value_min from trends_uint where itemid IN (25526) order by clock desc LIMIT 1) "Disco Totale", (select value_min from trends_uint where itemid IN (25527) order by clock desc LIMIT 1) "Disco Usato" FROM history WHERE itemid IN (26730) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid
UNION DISTINCT
SELECT "Server5" as Server, min(value) CPUMIN, max(value) CPUMAX, AVG(value) CPUAVG,(SELECT min(value) RAMMIN FROM history WHERE itemid IN (26747) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMIN,(SELECT max(value) RAMMAX FROM history WHERE itemid IN (26747) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMMAX,(SELECT avg(value) RAMAVG FROM history WHERE itemid IN (26747) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) RAMAVG,(SELECT AVG(value) DISCOPERC FROM history WHERE itemid IN (25514) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid) DISCOPERC , (select value_min from trends_uint where itemid IN (25513) order by clock desc LIMIT 1) "Disco Libero", (select value_min from trends_uint where itemid IN (25515) order by clock desc LIMIT 1) "Disco Totale", (select value_min from trends_uint where itemid IN (25516) order by clock desc LIMIT 1) "Disco Usato" FROM history WHERE itemid IN (26741) AND clock >= UNIX_TIMESTAMP(curdate()) -7 GROUP BY itemid
INTO OUTFILE '/var/opt/report/ReportZabbix.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

[yngvith]

hi, well it seems to me those tables are treated like this:
history and trend keep data for some parameters.
If you count the number of values from history and trend for a particular itemid ( this is a cpu parameter in my system)
mysql> SELECT count(FROM_UNIXTIME(clock)) FROM history WHERE itemid=24758;
+-----------------------------+
| count(FROM_UNIXTIME(clock)) |
+-----------------------------+
| 10096 |
+-----------------------------+

mysql> SELECT count(FROM_UNIXTIME(clock)) FROM trends WHERE itemid=24758;
+-----------------------------+
| count(FROM_UNIXTIME(clock)) |
+-----------------------------+
| 8738 |
+-----------------------------+


but if you check for this itemid in history_uint and trend_uint it will not be found there. Since it's not an integer value. It depends on the variable if it's an integer or not.

so in general I think history and trend tables are used together.
and then history_uint and trend_uint.

[ssanna]

Thank you for your time.