UIM What is the query used to provide the values for 'Actively monitored (last 30 minutes)' and 'Total devices discovered'.

Products

• DX Infrastructure Management

Issue/Introduction

The OC Home page shows the number of devices for  'Actively monitored (last 30 minutes)' and 'Total devices discovered' in the upper right pain. 

How are those number obtained? 

 

Environment

Release : 20.3

Component : UIM - CABI

Resolution

This is the query used:

select * from (select 'total_device_count' as type, count(*) as device_count       from cm_computer_system 
               union 
       select 'active_device_count' as type, count(DISTINCT(CASE WHEN sqs.sampletime is not NULL THEN ccs.cs_id ELSE NULL END)) as device_count
from cm_computer_system ccs 
       join cm_device cd 
               on ccs.cs_id = cd.cs_id 
       join cm_configuration_item cci 
               on cd.dev_id = cci.dev_id 
       join cm_configuration_item_metric ccim 
               on cci.ci_id = ccim.ci_id 
       join s_qos_data sqd 
               on ccim.ci_metric_id = sqd.ci_metric_id 
               and sqd.origin IN (select * from (select distinct sqd.origin from S_QOS_DATA sqd union select distinct ccso.origin from CM_COMPUTER_SYSTEM_ORIGIN ccso union select distinct na.origin from NAS_ALARMS na) alias) 
       join s_qos_snapshot sqs 
               on sqd.table_id = sqs.table_id 
               and CAST(sqs.sampletime as datetime) >= DATEADD(MINUTE, -30, CURRENT_TIMESTAMP)) src 
        pivot (sum(device_count) for type in (total_device_count,active_device_count))piv;