Custom Report - Metrics for all Discovered DB Instances

Document created by robert.coppersmith on Jan 22, 2018
Version 1Show Document
  • View in full screen mode

Hi All,

 

There has been a recent request to generate a report that displays all the tracked metrics for all discovered database instances.

 

Smaller environments can leverage the usage grid that is displayed at the bottom right when selected upon a group of DBs in the inventory tab however since this only shows the top or bottom 30 instances, larger environments will need to use the report below to get insight into all their instances.

 

Per usual, navigate to the Reports tab, click add, then insert the below query into the text field provided. This report can now be generated automatically or you can save it as a dashboard by using the "Query Result" widget under custom dashboards to be able to tune into this intraday without the need to generate a PDF/Excel export.

 

select

distinct

e.display_name as 'DB_Name'

, max(if(a.property_type = 'DBMem', a.avg_value*100,0)) as 'DBMem Utilization %'

, max(if(a.property_type = 'DBCacheHitRate', a.avg_value*100,0)) as 'DBCacheHitRatio / BufferCacheHitRatio'

, max(if(a.property_type = 'Connection', a.avg_value*100,0))  'Connection Utilization %'

, max(if(a.property_type = 'Transaction', a.avg_value*100,0)) 'Transaction Utilization %'

, max(if(a.property_type = 'TransactionLog', a.avg_value*100,0)) 'Transaction Log Utilization %'

, max(if(a.property_type = 'ResponseTime', a.avg_value*100,0)) 'ResponseTime Utilization %'

, max(if(a.property_type = 'VMem', a.avg_value*100,0)) 'vMem Utilization %'

, max(if(a.property_type = 'VCPU', a.avg_value*100,0)) 'vCPU Utilization %'

from app_stats_by_hour a

join entities e

on a.uuid = e.uuid

where e.creation_class = 'Database'

and a.property_subtype = 'Utilization'

and a.snapshot_time = (select max(snapshot_time) from app_stats_by_hour)

and e.display_name not like 'pt%'

group by 1

order by 1 asc

 

 

 

1 person found this helpful

Attachments

    Outcomes