Query Long (or any) SQLs Directly to Excel - Step by Step

Document created by iddo.avneri on Sep 30, 2015Last modified by fran.schwarzmann on Aug 15, 2016
Version 2Show Document
  • View in full screen mode

In some cases, customers will want to run complicated queries against VMTurbo's data. 

These queries can take time to return that can cause time outs in the system (report never comes back). In addition, since this is typically against older data waiting for a response might be a waste of time, since these queries can be executed in advance.

 

A solution can be to create a stored procedure in the DB that will create a table of the query results on an ordinary basis. And then, query that table directly.

Moreover, these queries (or any other) can be queried directly to an excel sheet (Using Microsoft windows Excel) for later editing.

 

Here are step by step instructions:

 

1.       Create a stored procedure with the query (from within SQL):

 

delimiter //

 

CREATE DEFINER=`root`@`10.%.%.%` PROCEDURE cluster_cap ()

BEGIN

drop table if exists cluster_cap_tb;

create table cluster_cap_tb as

select substring(group_name, 5) as 'Cluster Name',

round(numhosts.value) as 'Num Hosts',

round(numvms.value) as 'Num VMs',

round(numcpu.value) as 'Num pCPU',

round(numvcpu.value) as 'Allo vCPU',

round(mem_capacity.value) as 'Total RAM',

round(vmem_capacity.value) as 'Allo RAM',

round(storage_capacity.value) as 'Total Disk',

round(storage_allocated.value) as 'Allo Disk',

round(storage_available.value) as 'Free Disk'

from

(select internal_name, value from cluster_stats_by_day where property_type = 'Host' and property_subtype = 'numHosts'and recorded_on = (select max(recorded_on) from cluster_stats_by_day) and internal_name like 'GROUP-PMsByCluster_%') as numhosts

join

(select internal_name, value from cluster_stats_by_day where property_type = 'VM' and property_subtype = 'numVMs'and recorded_on = (select max(recorded_on) from cluster_stats_by_day) and internal_name like 'GROUP-PMsByCluster_%') as numvms

on numhosts.internal_name = numvms.internal_name

join

(select internal_name, value from cluster_stats_by_day where property_type = 'CPU' and property_subtype = 'numCPUs'and recorded_on = (select max(recorded_on) from cluster_stats_by_day) and internal_name like 'GROUP-PMsByCluster_%') as numcpu

on numhosts.internal_name = numcpu.internal_name

join

(select internal_name, value from cluster_stats_by_day where property_type = 'Mem' and property_subtype = 'capacity'and recorded_on = (select max(recorded_on) from cluster_stats_by_day) and internal_name like 'GROUP-PMsByCluster_%') as mem_capacity

on numhosts.internal_name = mem_capacity.internal_name

join

(select internal_name, value from cluster_stats_by_day where property_type = 'VMem' and property_subtype = 'capacity' and recorded_on = (select max(recorded_on) from cluster_stats_by_day) and internal_name like 'GROUP-PMsByCluster_%') as vmem_capacity

on numhosts.internal_name = vmem_capacity.internal_name

join

(select internal_name, value from cluster_stats_by_day where property_type = 'Storage' and property_subtype = 'capacity' and recorded_on = (select max(recorded_on) from cluster_stats_by_day) and internal_name like 'GROUP-PMsByCluster_%') as storage_capacity

on numhosts.internal_name = storage_capacity.internal_name

join

(select internal_name, value from cluster_stats_by_day where property_type = 'Storage' and property_subtype = 'allocated' and recorded_on = (select max(recorded_on) from cluster_stats_by_day) and internal_name like 'GROUP-PMsByCluster_%') as storage_allocated

on numhosts.internal_name = storage_allocated.internal_name

join

(select internal_name, value from cluster_stats_by_day where property_type = 'Storage' and property_subtype = 'available' and recorded_on = (select max(recorded_on) from cluster_stats_by_day) and internal_name like 'GROUP-PMsByCluster_%') as storage_available

on numhosts.internal_name = storage_available.internal_name

left join

(select replace(internal_name, 'VMs', 'PMs') as internal_name, sum(avg_value) as value

from

(select uuid, avg_value from vm_stats_by_day where property_type = 'numVCPUs' and property_subtype = 'numVCPUs' and snapshot_time = (select max(snapshot_time) from vm_stats_by_day)) as vcpu

join

  (select internal_name, member_uuid from cluster_members where internal_name like 'GROUP-VMsByCluster_%' and recorded_on = (select max(recorded_on) from cluster_members)) as cluster

on vcpu.uuid = cluster.member_uuid

group by internal_name) as numvcpu

on numhosts.internal_name = numvcpu.internal_name

join

(select distinct group_name, internal_name from cluster_members) as cluster

on numhosts.internal_name = cluster.internal_name;

END//

 

delimiter ;

 

2.       Open the DB for remote connections:

 

vmturbo:~ # mysql -u root -pvmturbo

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2175

Server version: 5.5.33 openSUSE package

 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> use vmtdb

Database changed

 

mysql> GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'vmturbo';

Query OK, 0 rows affected (0.00 sec)

 

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

 

3.       Open Firewall ports :

     vi /etc/sysconfig/SuSEfirewall2

     Make sure that FW_SERVICES_EXT_TCP includes 3306. For example:

     FW_SERVICES_EXT_TCP="3306 22 80 443"

 

Restart the service by:

vmturbo:~ # service SuSEfirewall2 restart

 

4.       Configure a connection to excel: Configure the connection for excel: http://helpdeskgeek.com/office-tips/excel-to-mysql/

5.  Schedule the stored procedure:

http://stackoverflow.com/questions/6560639/how-to-schedule-a-stored-procedure-in-mysql

1 person found this helpful

Attachments

    Outcomes