iddo.avneri

How to add your own Reports to VMTurbo 4.7 – Part 2

Blog Post created by iddo.avneri on Sep 25, 2014

In my last post: "How to add your own Reports to VMTurbo 4.7 – Part1" I’ve described how to add custom reports from the inventory tab or dashboard tab starting version 4.7.

 

Another powerful new feature is the ability to create custom queries directly out of VMTurbo’s database.

I won’t go into describing the database in details, but will describe the process in this post.

 

Recently, a customer asked about the monthly summary reports. Specifically, the reports states the number of VMs added and the number of VMs removed. Customer was asking if they can get the list of those VMs.

In General, the steps needed in order to create your own custom reports against the VMTurbo MYSql DB are:

  1. Connect to our DB
  2. Write a Script that will contain the new data
  3. Insert the new script as a report.

We will use the list of added and removed VMs as an example for this process.

 

Step 1 – Connect to the VMTurbo DB.

 

The easiest way to connect is directly via the appliance. SSH into the appliance (For example using putty) and connect with root/vmturbo.

The following command will connect you to the Database:

mysql -u root -pvmturbo vmtdb

If you are interested in connecting remotely with a graphical tool, follow this link previously posted on the GC Community: https://greencircle.vmturbo.com/community/vip-forum/blog/2014/09/16/remote-connections-to-the-vmturbo-database. My personal preferences: http://www.sequelpro.com/ for mac or http://www.heidisql.com/ for windows. 

It is strongly recommended that you do so, it will make your life significantly easier trying to figure out the DB structure

 

Step 2 – write the script

This is where you will have most of the work.

In order to produce this query we uses the statistics tables. Some examples:

  • “vm_stats_by_hour” – statistics for virtual machines utilization hour by hour
  • “pm_stats_by month” – statistics for physical machines utilization month by month

Notice that there are other types of tables you might be interested in. For example the table cluster_members that has the mapping between Virtual Machines UUIDs and their display names.

 

Specifically, the SQL used for this query (as a reference to future ones):

select

distinct property, month_code, display_name

from

(

select

                *

from

(

select 'VMs Added' as property, 'm1' as month_code, uuid

from

(select distinct

  uuid

  from vm_stats_by_day

  where

                property_type="priceIndex" and

  snapshot_time  between

                end_of_month(date_sub(now(), interval 2 month))

                and

                (start_of_month(date_sub(now(), interval 1 month))-1)

) as uuids_this_month

where

  uuid not in

  (select distinct

    uuid

    from vm_stats_by_day

    where

                property_type="priceIndex" and

    snapshot_time between

                end_of_month(date_sub(now(), interval 3 month))

                and

                (start_of_month(date_sub(now(), interval 2 month))-1)

  )

  union

select 'VMs Removed' as property, 'm1' as month_code, uuid

from

(select distinct

  uuid

  from vm_stats_by_day

  where

                property_type="priceIndex" and

  snapshot_time between

                end_of_month(date_sub(now(), interval 3 month))

                and

                (start_of_month(date_sub(now(), interval 2 month))-1)

) as uuids_last_month

where

  uuid not in

  (select distinct

    uuid

    from vm_stats_by_day

    where

                property_type="priceIndex" and

    snapshot_time between

                end_of_month(date_sub(now(), interval 2 month))

                and

                (start_of_month(date_sub(now(), interval 1 month))-1)

  )

union

select 'VMs Added' as property, 'm2' as month_code, uuid

from

(select distinct

  uuid

  from vm_stats_by_day

  where

                property_type="priceIndex" and

  snapshot_time between

                end_of_month(date_sub(now(), interval 1 month))

                and

                (start_of_month(date_sub(now(), interval 0 month))-1)

) as uuids_this_month

where

  uuid not in

  (select distinct

    uuid

    from vm_stats_by_day

    where

                property_type="priceIndex" and

    snapshot_time between

                end_of_month(date_sub(now(), interval 2 month))

                and

                (start_of_month(date_sub(now(), interval 1 month))-1)

  )

union

select 'VMs Removed' as property, 'm2' as month_code, uuid

from

(select distinct

  uuid

  from vm_stats_by_day

  where

                property_type="priceIndex" and

  snapshot_time between

                end_of_month(date_sub(now(), interval 2 month))

                and

                (start_of_month(date_sub(now(), interval 1 month))-1)

) as uuids_last_month

where

  uuid not in

  (select distinct

    uuid

    from vm_stats_by_day

    where

                property_type="priceIndex" and

    snapshot_time between

                end_of_month(date_sub(now(), interval 1 month))

                and

                (start_of_month(date_sub(now(), interval 0 month))-1)

  )

) as add_rem_data

#group by property

) as uuid_list

join

(select member_uuid, display_name from cluster_members) as cluster_members

on (uuid = member_uuid)

order by property

 

 

 

Step 3 – add the query to the product.

Starting 4.7 (Please make sure you upgraded!) you can insert the new query as a report to the product.

There is a table called “user_reports” that is designed for you to add your own reports.

For example, to insert the VMs added or removed:

INSERT INTO `user_reports` (`id`, `title`, `category`, `short_desc`, `description`, `xml_descriptor`, `period`, `day_type`)

VALUES

(1, 'Added And Removed VM details', NULL, NULL, 'Vms added removed list with names', 'select \ndistinct property, month_code, display_name \n\nfrom\n(\nselect \n *\nfrom \n\n(\n\nselect \'VMs Added\' as property, \'m1\' as month_code, uuid\nfrom\n(select distinct\n  uuid\n  from vm_stats_by_day\n  where\n property_type=\"priceIndex\" and\n  snapshot_time  between \n end_of_month(date_sub(now(), interval 2 month))\n and\n (start_of_month(date_sub(now(), interval 1 month))-1)\n) as uuids_this_month\nwhere \n  uuid not in \n  (select distinct\n    uuid\n from vm_stats_by_day\n    where\n property_type=\"priceIndex\" and\n snapshot_time between \n end_of_month(date_sub(now(), interval 3 month))\n and\n (start_of_month(date_sub(now(), interval 2 month))-1)\n  )\n\n union \n  \nselect \'VMs Removed\' as property, \'m1\' as month_code, uuid\nfrom\n(select distinct\n  uuid\n from vm_stats_by_day\n  where\n property_type=\"priceIndex\" and\n snapshot_time between \n end_of_month(date_sub(now(), interval 3 month))\n and\n (start_of_month(date_sub(now(), interval 2 month))-1)\n) as uuids_last_month\nwhere \n  uuid not in \n  (select distinct\n    uuid\n from vm_stats_by_day\n    where\n property_type=\"priceIndex\" and\n snapshot_time between \n end_of_month(date_sub(now(), interval 2 month))\n and\n (start_of_month(date_sub(now(), interval 1 month))-1)\n )\n\nunion\n\nselect \'VMs Added\' as property, \'m2\' as month_code, uuid\nfrom\n(select distinct\n  uuid\n  from vm_stats_by_day\n  where\n property_type=\"priceIndex\" and\n snapshot_time between \n end_of_month(date_sub(now(), interval 1 month))\n and\n (start_of_month(date_sub(now(), interval 0 month))-1)\n) as uuids_this_month\nwhere \n  uuid not in \n  (select distinct\n    uuid\n from vm_stats_by_day\n    where\n property_type=\"priceIndex\" and\n snapshot_time between \n end_of_month(date_sub(now(), interval 2 month))\n and\n (start_of_month(date_sub(now(), interval 1 month))-1)\n  )\n\nunion \n \nselect \'VMs Removed\' as property, \'m2\' as month_code, uuid\nfrom\n(select distinct\n uuid\n  from vm_stats_by_day\n  where\n property_type=\"priceIndex\" and\n snapshot_time between \n end_of_month(date_sub(now(), interval 2 month))\n and\n (start_of_month(date_sub(now(), interval 1 month))-1)\n) as uuids_last_month\nwhere \n  uuid not in \n  (select distinct\n    uuid\n from vm_stats_by_day\n    where\n property_type=\"priceIndex\" and\n snapshot_time between \n end_of_month(date_sub(now(), interval 1 month))\n and\n (start_of_month(date_sub(now(), interval 0 month))-1)\n  )\n\n) as add_rem_data\n\n#group by property\n) as uuid_list\n\njoin\n\n(select member_uuid, display_name from cluster_members) as cluster_members\n\non (uuid = member_uuid)\n\norder by property;', NULL, NULL);

 

Once you do that, a new report will appear in the report tab: 'Added And Removed VM details'. And you can execute it as any other reports:

p2.png

 

 

 

We are looking at delivering a more UI based import workflow in the 5.0 release. But if you can’t wait with your reports! You have a great way of creating them right now.

 

p2-2.png Important! Even though VMTurbo’s DB Schema does not change often, schema changes may occur. These can affect your querys and require you to write new ones.

Outcomes