How to list devices in maintenance mode via sql query

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

How to run a query to show devices in maintenance mode.

Environment

• DX UIM 20.3.x or later

 

Resolution

--The queries below shows devices in maintenance mode, along with their origin and schedule: 

select distinct cs.name, cs.ip, cs.origin, ms.SCHEDULE_NAME, ms.DESCRIPTION, mw.START_TIME, mw.END_TIME from CM_COMPUTER_SYSTEM cs INNER JOIN MAINTENANCE_SCHEDULE_MEMBERS msm ON cs.cs_id=msm.CS_ID INNER JOIN MAINTENANCE_WINDOW mw ON msm.SCHEDULE_ID=mw.SCHEDULE_ID INNER JOIN MAINTENANCE_SCHEDULE ms ON msm.SCHEDULE_ID=ms.SCHEDULE_ID WHERE mw.END_TIME >=GETDATE() 

--or 

select distinct cs.name, cs.ip, cs.origin, ms.SCHEDULE_NAME, ms.DESCRIPTION, CONVERT(VARCHAR(50), mw.START_TIME,100) as 'Start Time', CONVERT(VARCHAR(50), mw.END_TIME, 100) as 'End Time' from CM_COMPUTER_SYSTEM cs

INNER JOIN MAINTENANCE_SCHEDULE_MEMBERS msm

ON cs.cs_id=msm.CS_ID

INNER JOIN MAINTENANCE_WINDOW mw

ON msm.SCHEDULE_ID=mw.SCHEDULE_ID

INNER JOIN MAINTENANCE_SCHEDULE ms

ON msm.SCHEDULE_ID=ms.SCHEDULE_ID

WHERE mw.END_TIME >=GETDATE()