Example query I use to have a list of devices currently under maintenance:
select distinct cs.name, cs.ip, cs.origin,ms.schedule_id, ms.SCHEDULE_NAME, ms.DESCRIPTION, max(CONVERT(VARCHAR(50), ms.START_TIME,111)) as 'Start Date',
max(CONVERT(VARCHAR(50), ms.START_TIME,108)) as 'Start Time', max(CONVERT(VARCHAR(50), mw.END_TIME, 111)) as 'End Date', max(CONVERT(VARCHAR(50), mw.END_TIME, 108)) as 'End Time'
from CM_COMPUTER_SYSTEM cs with(nolock)
INNER JOIN MAINTENANCE_SCHEDULE_MEMBERS msm with(nolock) ON cs.cs_id=msm.CS_ID
INNER JOIN MAINTENANCE_WINDOW mw with(nolock) ON msm.SCHEDULE_ID=mw.SCHEDULE_ID
INNER JOIN MAINTENANCE_SCHEDULE ms with(nolock) ON msm.SCHEDULE_ID=ms.SCHEDULE_ID
WHERE mw.END_TIME >=GETDATE()
group by name,ip,origin,ms.schedule_id,ms.SCHEDULE_NAME,ms.DESCRIPTION