Reports: SQL Storage Percent Change Month over Month

File uploaded by robert.coppersmith on Jan 10, 2017Last modified by robert.coppersmith on Apr 12, 2017
Version 2Show Document
  • View in full screen mode

This report will show you the month over month % change (for the past 3 months) in storage utilization for each SQL drive within a specified group of SQL VMs.

 

The only adjustments that need to be made is to specify the name of your group of SQL VMs.

 

In the query you'll notice 3 lines that look like so:

 

/* REPLACE GROUP-NAME-USED WITH GROUP NAME IN THE LINE BELOW, LEAVE THE ' ' AROUND THE GROUP NAME THAT YOU ENTER*/

'SQL'

 

Just specify the group name within the ' ', i.e. if your group name is SQL_VMs then the 3 lines in the report should read as:

 

/* REPLACE GROUP-NAME-USED WITH GROUP NAME IN THE LINE BELOW, LEAVE THE ' ' AROUND THE GROUP NAME THAT YOU ENTER*/

'SQL_VMs'

 

Feel free to reach out if anybody has any questions.

 

 

Query:

 

select

distinct

VM_Name2 as VM,

VM_Disk_Drive2 as 'Drive Type',

if(

  round((if(VM_Disk_Drive2 = VM_Disk_Drive1, ADUP1-ADUP2, 0)),2)<0

  , round(min(if(VM_Disk_Drive2 = VM_Disk_Drive1, ADUP1-ADUP2, 0)),2)

  , round(max(if(VM_Disk_Drive2 = VM_Disk_Drive1, ADUP1-ADUP2, 0)),2)) as 'Avg Drive Utilization %Change from 2 Months Prior to Last Month',

 

 

if(

  round((if(VM_Disk_Drive3 = VM_Disk_Drive2, ADUP2-ADUP3, 0)),2)<0

  , round(min(if(VM_Disk_Drive3 = VM_Disk_Drive2, ADUP2-ADUP3, 0)),2)

  , round(max(if(VM_Disk_Drive3 = VM_Disk_Drive2, ADUP2-ADUP3, 0)),2)) as 'Avg Drive Utilization %Change from 3 Months Prior to 2 Months Prior'

 

 

 

 

from

 

 

(

select distinct

  vm1.snapshot_time as Month1,

  e1.uuid as uuid1,

  e1.display_name as VM_Name1,

  vm1.commodity_key as VM_Disk_Drive1,

  avg(vm1.avg_value)*100 as ADUP1,

  Max(vm1.max_value)*100 as MDUP1

  from vm_stats_by_month as vm1

  LEFT JOIN entities e1 on e1.uuid = vm1.uuid

  LEFT JOIN entity_assns_members_entities eame1 ON eame1.entity_dest_id = e1.id

  LEFT JOIN entity_assns eas1 ON eas1.id = eame1.entity_assn_src_id

  LEFT JOIN entities grp1 ON grp1.id = eas1.entity_entity_id

 

  LEFT JOIN entities en1 on en1.uuid = vm1.uuid

  LEFT JOIN entity_assns_members_entities eamee1 ON eamee1.entity_dest_id = en1.id

  LEFT JOIN entity_assns eass1 ON eass1.id = eamee1.entity_assn_src_id

  LEFT JOIN entities hst1 ON hst1.id = eass1.entity_entity_id

 

  WHERE grp1.name = concat('GROUP-USER-',

 

 

/* REPLACE GROUP-NAME-USED WITH GROUP NAME IN THE LINE BELOW, LEAVE THE ' ' AROUND THE GROUP NAME THAT YOU ENTER*/

'SQL'

 

 

)

  AND hst1.name like 'GROUP-VMs\_%'

  and vm1.property_type = 'VStorage'

  and vm1.property_subtype='utilization'

   and month(vm1.snapshot_time) = month(current_date - interval 1 month)

   and e1.display_name is not null

   and vm1.commodity_key is not null

    group by vm1.snapshot_time, e1.display_name, vm1.commodity_key

   

) as M1

 

 

JOIN

 

 

(select distinct

  vm2.snapshot_time as Month2,

  e2.uuid as uuid2,

  e2.display_name as VM_Name2,

  vm2.commodity_key as VM_Disk_Drive2,

  avg(vm2.avg_value)*100 as ADUP2,

  Max(vm2.max_value)*100 as MDUP2

  from vm_stats_by_month as vm2

  LEFT JOIN entities e2 on e2.uuid = vm2.uuid

  LEFT JOIN entity_assns_members_entities eame2 ON eame2.entity_dest_id = e2.id

  LEFT JOIN entity_assns eas2 ON eas2.id = eame2.entity_assn_src_id

  LEFT JOIN entities grp2 ON grp2.id = eas2.entity_entity_id

 

  LEFT JOIN entities en2 on en2.uuid = vm2.uuid

  LEFT JOIN entity_assns_members_entities eamee2 ON eamee2.entity_dest_id = en2.id

  LEFT JOIN entity_assns eass2 ON eass2.id = eamee2.entity_assn_src_id

  LEFT JOIN entities hst2 ON hst2.id = eass2.entity_entity_id

 

  WHERE grp2.name = concat('GROUP-USER-',

 

 

/* REPLACE GROUP-NAME-USED WITH GROUP NAME IN THE LINE BELOW, LEAVE THE ' ' AROUND THE GROUP NAME THAT YOU ENTER*/

'SQL'

 

 

)

  AND hst2.name like 'GROUP-VMs\_%'

  and vm2.property_type = 'VStorage'

  and vm2.property_subtype='utilization'

   and month(vm2.snapshot_time) = month(current_date - interval 2 month)

   and e2.display_name is not null

   and vm2.commodity_key is not null

    group by vm2.snapshot_time, e2.display_name, vm2.commodity_key

   

) as M2 on M1.uuid1 = M2.uuid2

 

 

JOIN

 

 

(select distinct

  vm3.snapshot_time as Month3,

  e3.uuid as uuid3,

  e3.display_name as VM_Name3,

  vm3.commodity_key as VM_Disk_Drive3,

  avg(vm3.avg_value)*100 as ADUP3,

  Max(vm3.max_value)*100 as MDUP3

  from vm_stats_by_month as vm3

  LEFT JOIN entities e3 on e3.uuid = vm3.uuid

  LEFT JOIN entity_assns_members_entities eame3 ON eame3.entity_dest_id = e3.id

  LEFT JOIN entity_assns eas3 ON eas3.id = eame3.entity_assn_src_id

  LEFT JOIN entities grp3 ON grp3.id = eas3.entity_entity_id

 

  LEFT JOIN entities en3 on en3.uuid = vm3.uuid

  LEFT JOIN entity_assns_members_entities eamee3 ON eamee3.entity_dest_id = en3.id

  LEFT JOIN entity_assns eass3 ON eass3.id = eamee3.entity_assn_src_id

  LEFT JOIN entities hst3 ON hst3.id = eass3.entity_entity_id

 

  WHERE grp3.name = concat('GROUP-USER-',

 

 

/* REPLACE GROUP-NAME-USED WITH GROUP NAME IN THE LINE BELOW, LEAVE THE ' ' AROUND THE GROUP NAME THAT YOU ENTER*/

'SQL'

 

 

)

  AND hst3.name like 'GROUP-VMs\_%'

  and vm3.property_type = 'VStorage'

  and vm3.property_subtype='utilization'

   and month(vm3.snapshot_time) = month(current_date - interval 3 month)

   and e3.display_name is not null

   and vm3.commodity_key is not null

    group by vm3.snapshot_time, e3.display_name, vm3.commodity_key

) as M3 on M2.uuid2 = M3.uuid3

 

 

group by VM_Name2, VM_Disk_Drive2

Outcomes