MsSQL db query to discovered devices count

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

MSSQL DB query to determine  current Discovered Devices count

Environment

Release : 20.3 any release

Component : UIM MYSQL

Resolution

Below query provides list of devices that are active in the last 30 minutes:
select ( select count(1) as device_count from cm_computer_system  ) as 'total_device_count',
   count(DISTINCT(CASE WHEN sqs.sampletime is not NULL THEN ccs.cs_id ELSE NULL END)) as 'active_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
      join s_qos_snapshot sqs
               on sqd.table_id = sqs.table_id
               and CAST(sqs.sampletime as datetime) >= DATEADD(MINUTE, -30, CURRENT_TIMESTAMP)