To help troubleshooting these are the queries usage_metering uses to obtain data from the UIM database.
Release : 20.3
Component : UIM - USAGE_METERING/BILLING
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
The other queries run are against tables in the probes local database usage_metering.h2.db.