ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

MsSQL db query to discovered devices count


Article ID: 239388


Updated On:


DX Unified Infrastructure Management (Nimsoft / UIM)


MSSQL DB query to determine  current Discovered Devices count


Release : 20.3 any release

Component : UIM MYSQL


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)