Query to have a quick overview about my UIM environment - robots, qos, alarms, db size

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Queries to have a quick overview about my UIM environment - robots, qos, alarms, db size

Environment

UIM 8.x or later

SQLServer

Resolution

select '01. # qos definitions' as item, COUNT(*) as cnt from S_QOS_DEFINITION with(nolock)
union
select '02. # qos views', COUNT(name) #view from sysobjects with(nolock) where xtype = 'V' and name like 'V_QOS%'
union
select '03. # qos objects', COUNT(*) from S_QOS_DATA with(nolock)
union
select '04. # robots', COUNT(*) from CM_NIMBUS_ROBOT with(nolock)
union
select '05. # robots (except hubs)', COUNT(*) from CM_NIMBUS_ROBOT with(nolock) where is_hub = 0
union
select '06. # hubs', COUNT(*) from CM_NIMBUS_ROBOT with(nolock) where is_hub = 1
union
select '07. # computer systems', COUNT(*) from CM_COMPUTER_SYSTEM with(nolock)
union
select '08. # todays alarms in db', COUNT(*) #msg from NAS_TRANSACTION_LOG with(nolock) where CAST(time as DATE) BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)) group by CAST(time as DATE)
union
select '09. # total alarms in db', COUNT(*) #msg from NAS_TRANSACTION_LOG with(nolock)
union
select '10. # todays open alarms', COUNT(*) #msg from NAS_ALARMS with(nolock) where CAST(time_origin as DATE) BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)) group by CAST(time_origin as DATE)
union
select '11. # todays open critical alarms', COUNT(*) #msg from NAS_ALARMS with(nolock) where CAST(time_origin as DATE) BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)) and level = '5' group by CAST(time_origin as DATE)
union
SELECT '12. db: CA_UIM used space MB', CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) ) 'Size_MB' FROM sys.master_files with(nolock) WHERE database_id = DB_ID('CA_UIM') and type_desc = 'ROWS'
union
SELECT '13. db: CA_UIM Log used space', CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) ) 'Size_MB' FROM sys.master_files with(nolock) WHERE database_id = DB_ID('CA_UIM') and type_desc = 'LOG'

Will receive and output like this one:

item cnt
computer systems 3223
db: CA_UIM Log used space 21159.38
db: CA_UIM used space MB 422661.13
hubs 2
qos definitions 534
qos objects 1886320
qos views 377
robots 1419
robots (except hubs) 1417
todays alarms in db 437477
todays open alarms 5425
todays open critical alarms 1050
total alarms in db 55737936