What queries does the usage_metering probe use?

book

Article ID: 222745

calendar_today

Updated On:

Products

DX Infrastructure Management

Issue/Introduction

What queries does the usage_metering probe use? 

Resolution

From the probe log: 

Executing query with access id selectForOnPremReport query is 
   SELECT
   cs_id,origin,GROUP_CONCAT(ip) as
   ip,GROUP_CONCAT(name) as name,GROUP_CONCAT(probe_name) as
   probe_name,GROUP_CONCAT(FQDN) as fqdn,GROUP_CONCAT(ROBOT) as
   robot,GROUP_CONCAT(total_size) as
   total_size,GROUP_CONCAT(monitored_size) as monitored_size
   FROM
   UM_DEVICE_DATA
   Where CREATE_DATE between ? and ?
   group by cs_id,origin
  

calculation_report_

   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

 

Executing query with access id retrieveOnPremiseReportData query is 
   select
   id,create_time,start_time,end_time,report_type,path,origin_name,flag
   from um_report_data where report_type='onpremise' order by id desc
   


Executing query with access id selectOriginBasedDataForOnPremReport query is 
   SELECT
   cs_id,origin,GROUP_CONCAT(ip) as
   ip,GROUP_CONCAT(name) as name,GROUP_CONCAT(probe_name) as
   probe_name,GROUP_CONCAT(FQDN) as fqdn,GROUP_CONCAT(ROBOT) as
   robot,GROUP_CONCAT(total_size) as
   total_size,GROUP_CONCAT(monitored_size) as monitored_size
   FROM
   UM_DEVICE_DATA
   Where CREATE_DATE between ? and ? and origin = ?
   group
   by cs_id,origin
   
   
   
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

 

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