UIM 20.3 what queries does usage_metering use to obtain data from the UIM database.

Products

• DX Infrastructure Management

Issue/Introduction

To help troubleshooting these are the queries usage_metering uses to obtain data from the UIM database. 

Environment

Release : 20.3

Component : UIM - USAGE_METERING/BILLING

Resolution

To retrieve device data: 

Executing query with access id sqlserverRetrieveDeviceData query is 
   select distinct cs_id,name,dns_name,probe_name,origin,ip,robot,dedicated
   from (select distinct
   c.cs_id, c.name,c.dns_name, q.probe as
   probe_name,q.origin,c.ip,q.robot,c.dedicated from
   CM_COMPUTER_SYSTEM c
   inner join
   CM_DEVICE d
   on c.cs_id = d.cs_id and d.dev_id not in (Select dev_id from CM_DEVICE_ATTRIBUTE where dev_attr_key like '%UMB_Ignore' and dev_attr_value='true')
   inner join CM_CONFIGURATION_ITEM ci
   on
   d.dev_id = ci.dev_id
   inner join CM_CONFIGURATION_ITEM_METRIC met
   on
   ci.ci_id = met.ci_id
   inner join S_QOS_DATA q
   on met.ci_metric_id =
   q.ci_metric_id
   inner join S_QOS_SNAPSHOT ss
   on q.table_id = ss.table_id
   where ss.sampletime > dateadd(hour,-24,GETDATE())
   union
   select distinct
   c.cs_id, c.name,c.dns_name,na.prid as probe_name,na.origin,c.ip,na.robot,c.dedicated from
   CM_COMPUTER_SYSTEM c
   inner join CM_DEVICE d
   on c.cs_id = d.cs_id
   inner
   join nas_alarms na
   on d.dev_id=na.dev_id and d.dev_id not in (Select dev_id from CM_DEVICE_ATTRIBUTE where dev_attr_key like '%UMB_Ignore' and dev_attr_value='true')
   where na.time_arrival >
   dateadd(hour,-24,GETDATE())) as temp

 

To retrieve storage device capacity data: 

Executing query with access id sqlserverRetrieveStorageDeviceCapacityData query is 

   select
   a.cs_id,a.origin,a.ip,a.name,a.probe_name,a.robot,a.qos,totalcapacity,selectedcapacity
   from (select
   cs_id,origin,ip,name,probe_name,robot,qos,sum(samplevalue) as
   totalcapacity
   from (select
   ccim.ci_metric_id,
   cci.ci_id,
   ccs.cs_id,
   cci.ci_name,
   ccs.ip,
   sqd.origin,
   ccs.name,
   probe_name = sqd.probe,
   cd.dev_id,
   sqd.qos,
   sqd.robot,
   sqs.samplevalue
   from cm_computer_system ccs
   inner join cm_device cd on ccs.cs_id=cd.cs_id
   inner join cm_configuration_item cci ON cd.dev_id = cci.dev_id
   inner join cm_configuration_item_metric ccim on cci.ci_id = ccim.ci_id
   inner join s_qos_data sqd ON sqd.ci_metric_id = ccim.ci_metric_id and
   qos in ('QOS_STORAGE_TOTAL_CAPACITY') and sqd.probe in
   ('hp_3par','netapp_ontap','vnxe_monitor','celerra','clariion','hitachi','ibm-ds','ibm_svc','ibm_ds_next','netapp_ontap','purestorage','vmax','vplex','xtremio','nutanix_monitor','netapp','ceph')
   inner join s_qos_snapshot sqs on sqd.table_id=sqs.table_id and
   sqs.sampletime > dateadd(hour,-24,GETDATE())
   ) as tmp2 group by cs_id,origin,ip,name,probe_name,robot,qos) as a
   left outer join
   (select cs_id,origin,ip,name,probe_name,robot,qos,sum(samplevalue) as
   selectedcapacity
   from (select
   ccim.ci_metric_id,
   cci.ci_id,
   ccs.cs_id,
   cci.ci_name,
   ccs.ip,
   sqd.origin,
   ccs.name,
   probe_name = sqd.probe,
   cd.dev_id,
   sqd.qos,
   sqd.robot,
   sqs.samplevalue
   from cm_computer_system ccs
   inner join cm_device cd on ccs.cs_id=cd.cs_id
   inner join cm_configuration_item cci ON cd.dev_id = cci.dev_id
   inner join cm_configuration_item_metric ccim on cci.ci_id = ccim.ci_id
   inner join s_qos_data sqd ON sqd.ci_metric_id = ccim.ci_metric_id and
   qos in ('QOS_STORAGE_TOTAL_CAPACITY') and sqd.probe in
   ('hp_3par','netapp_ontap','vnxe_monitor','celerra','clariion','hitachi','ibm-ds','ibm_svc','ibm_ds_next','netapp_ontap','purestorage','vmax','vplex','xtremio','nutanix_monitor','netapp','ceph')
   inner join s_qos_snapshot sqs on sqd.table_id=sqs.table_id and
   sqs.sampletime > dateadd(hour,-24,GETDATE())
   inner join
   (select cci.ci_id,count(*) as cnt
   from cm_computer_system ccs
   inner join cm_device cd on ccs.cs_id=cd.cs_id
   inner join cm_configuration_item cci ON cd.dev_id = cci.dev_id
   inner join cm_configuration_item_metric ccim on cci.ci_id = ccim.ci_id
   inner join s_qos_data sqd ON sqd.ci_metric_id = ccim.ci_metric_id and
   sqd.probe in
   ('hp_3par','netapp_ontap','vnxe_monitor','celerra','clariion','hitachi','ibm-ds','ibm_svc','ibm_ds_next','netapp_ontap','purestorage','vmax','vplex','xtremio','nutanix_monitor','netapp','ceph')
   inner join s_qos_snapshot sqs on sqd.table_id=sqs.table_id
   where sqs.sampletime > dateadd(hour,-24,GETDATE()) group by cci.ci_id
   having count(*) > 1) as tmp
   on cci.ci_id=tmp.ci_id) as tmp2 group by
   cs_id,origin,ip,name,probe_name,robot,qos) as b
   on a.cs_id=b.cs_id and a.origin=b.origin and a.ip=b.ip and
   a.probe_name=b.probe_name and a.robot=b.robot and a.qos=b.qos

Additional Information

The other queries run are against tables in the probes local database usage_metering.h2.db.