I'm sorry it was not an easy setup.
Can you please share what did not work for you in the instructions? I see you did manage to get it working... :)
If you want data from the last 24 hours in 60 seconds interval (Still not real time but can give you a better granularity),
You can use the samples tables.
SELECT DISTINCT
min(time) AS time,
MEM_Usage,
host_name || 'MEM_Usage' as metric
FROM (
SELECT
stats_hosts.host_id,
CASE
WHEN delete_date IS NULL
THEN host_name
ELSE
host_name
||
' (Removed on '
||
CAST ( CAST ( delete_date AS date ) AS varchar )
||
')'
END AS host_name,
stats_hosts.history_datetime AS time,
SUM (
COALESCE (
stats_hosts.cpu_usage_percent,
0
) *
COALESCE (
stats_hosts.minutes_in_status,
0
)
) /
SUM (
COALESCE (
stats_hosts.minutes_in_status,
0
)
) AS CPU_Usage,
SUM (
COALESCE (
stats_hosts.memory_usage_percent,
0
) *
COALESCE (
stats_hosts.minutes_in_status,
0
)
) /
SUM (
COALESCE (
stats_hosts.minutes_in_status,
0
)
) AS MEM_Usage
FROM v4_2_statistics_hosts_resources_usage_samples AS stats_hosts
INNER JOIN v4_2_configuration_history_hosts
ON (
v4_2_configuration_history_hosts.host_id =
stats_hosts.host_id
)
WHERE stats_hosts.history_datetime >= $__timeFrom()
AND stats_hosts.history_datetime < $__timeTo()
-- Here we get the latest hosts configuration
AND v4_2_configuration_history_hosts.history_id IN (
SELECT MAX ( a.history_id )
FROM v4_2_configuration_history_hosts AS a
GROUP BY a.host_id
)
AND stats_hosts.host_id IN (
SELECT a.host_id
FROM v4_2_statistics_hosts_resources_usage_samples a
INNER JOIN v4_2_configuration_history_hosts b
ON ( a.host_id = b.host_id )
WHERE
-- Here we filter by active hosts only
a.host_status = 1
-- Here we filter by the datacenter chosen by the user
AND b.cluster_id IN (
SELECT v4_2_configuration_history_clusters.cluster_id
FROM v4_2_configuration_history_clusters
WHERE
v4_2_configuration_history_clusters.datacenter_id =
$datacenter_id
)
-- Here we filter by the clusters chosen by the user
AND b.cluster_id IN ($cluster_id)
AND a. history_datetime >= $__timeFrom()
AND a.history_datetime < $__timeTo()
-- Here we get the latest hosts configuration
AND b.history_id IN (
SELECT MAX (g.history_id)
FROM v4_2_configuration_history_hosts g
GROUP BY g.host_id
)
GROUP BY a.host_id
ORDER BY
-- Hosts will be ordered according to the summery of
-- memory and CPU usage percent.
--This determines the busiest hosts.
SUM (
COALESCE (
a.memory_usage_percent * a.minutes_in_status,
0
)
) /
SUM (
COALESCE (
a.minutes_in_status,
0
)
) +
SUM (
COALESCE (
a.cpu_usage_percent * a.minutes_in_status,
0
)
) /
SUM (
COALESCE (
a.minutes_in_status,
0
)
) DESC
LIMIT 5
)
GROUP BY
stats_hosts.host_id,
host_name,
delete_date,
history_datetime
) AS a
GROUP BY a.host_name, a.mem_usage
ORDER BY time
