abel5405 Absent Member.
Absent Member.
2025 views

Query showing Maintenance Mode Duration

Greetings All,
I am trying to write a simple query to list what machines are in Maintenance Mode and how long they have been in Maintenance Mode. Below is my attempt at it. I keep getting the whole history for a machine and not the most recent. Can anybody help me figure this out please, it's driving me nuts lol. Many thanks as always for any help.

_____________

--Get UTC Time
declare @UTCtd AS datetime
set @UTCtd = getutcdate()

--Get Today's Date and Time
declare @TodayTD AS datetime

set @TodayTD = GETDATE()

--Get Time Zone Informaiton
declare @TimeDif as int
set @TimeDif = DATEDIFF(hh, getutcdate(), getdate())

select
o.ObjID,
o.Name,
m.Operation,
LogTime = min(m.LogTime),
--DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (m.LogTime)) as LogTime,
DATEDIFF(mi, @TodayTD, (DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (m.LogTime)))) * -1 as DataAgeMinutes
from
dbo.Object as o with (NOLOCK)
join dbo.MachineMntHistory as m with (NOLOCK) on o.ObjID = m.MCMachineObjID
where
o.Status & 0x00004000 != 0
and
m.Operation = 'Start-Maintenance'

group by ObjID, Name, Operation, LogTime
Tags (1)
0 Likes
2 Replies
andy_doran Absent Member.
Absent Member.

Re: Query showing Maintenance Mode Duration

Does this give you what you want?

declare @offset int = datediff(s, getutcdate(), getdate())

select
O.Name,
LogTimeGMT = dateadd(s, max(H.LogTime), '19700101'),
LogTimeLocal = dateadd(s, max(H.LogTime) + @offset, '19700101'),
TimeInMaint = datediff(s, '19700101', getutcdate()) - max(H.LogTime)
from
(
select
ObjID,
Name
from
dbo.Object
where
Status &0x08000000 = 0
and Status &0x00004000 !=0
) as O
inner join dbo.MachineMntHistory H
on H.MCMachineObjID = O.ObjID
where
H.MntType = 1
and H.Operation = 'Start-Maintenance'
group by
O.Name
order by
O.Name
0 Likes
abel5405 Absent Member.
Absent Member.

Re: Query showing Maintenance Mode Duration

Yes! That is spot on! Many thanks Andy as usual, this is awesome!

v/r
Chris

Edit:
Question, how does this query work over what I was doing before? Just trying to understand better.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.