Shirly, 

Sorry it took so long to reply. 

The issue I found was that the code for the variables required single quotes around some of the fields, such as this one:

WHERE datacenter_id ='$datacenter_id'

without the single quotes I was getting errors. 


Vincent Royer
778-825-1057


SUSTAINABLE MOBILE ENERGY SOLUTIONS




On Sun, Mar 25, 2018 at 4:44 AM, Shirly Radco <sradco@redhat.com> wrote:
Hi Vincent,


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.

Also, Please make sure to update your views prefix to the version you are using, In the example prefix is  v4_2_*
If you are using oVirt 4.1, prefix should be  v4_1_*

For example: (Did not get to test this query yet)

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

--

SHIRLY RADCO

BI SeNIOR SOFTWARE ENGINEER

Red Hat Israel


On Thu, Mar 22, 2018 at 9:05 PM, Vincent Royer <vincent@epicenergy.ca> wrote:
I setup Grafana using the instructions I found on accessing the Ovirt history database.  However, the instructions didn't work as written.  Regardless, it does work, but it's not easy to setup.  The update rate also leaves something to be desired, its ok for historical info, but it's not a good real time monitoring solution (although its possible I could set it up differently and it would work better)

Also using Grafana, I have setup Telegraf agents on most of my VMs.  

Lastly, I also installed Telegraf on the Centos hosts in my Ovirt Cluster






Vincent Royer


SUSTAINABLE MOBILE ENERGY SOLUTIONS




On Wed, Mar 21, 2018 at 8:41 PM, Terry hey <recreationh@gmail.com> wrote:
Dear all, 

Now, we can just read how many storage used, cpu usage on ovirt dashboard.
But is there any monitoring tool for monitoring virtual machine time to time?
If yes, could you guys give me the procedure? 


Regards
Terry

_______________________________________________
Users mailing list
Users@ovirt.org
http://lists.ovirt.org/mailman/listinfo/users



_______________________________________________
Users mailing list
Users@ovirt.org
http://lists.ovirt.org/mailman/listinfo/users