Last contacted, better SQL query
My post about how to receive approximate last contacted time spawn good feedback and thanks to Robin Drake for rewriting my SQL script without use of cursors while producing single grid. Following is his version:
use OperationsManager
Go
declare @substract float
declare @numberOfMissing float
declare @interval float
-- Get the number of missing heartbeats
select @numberOfMissing = SettingValue from GlobalSettings GS
join ManagedTypeProperty MTP with(nolock) on GS.ManagedTypePropertyId = MTP.ManagedTypePropertyId
where MTP.ManagedTypePropertyName = 'NumberOfMissingHeartBeatsToMarkMachineDown'
**
-- Get the heartbeat interval
select @interval = SettingValue from GlobalSettings GS
join ManagedTypeProperty MTP with(nolock) on GS.ManagedTypePropertyId = MTP.ManagedTypePropertyId
where MTP.ManagedTypePropertyName = 'HeartbeatInterval'
-- Calculate the amount of lapsed time before a system is marked as non contactable
select @substract = (@numberOfMissing * @interval)/100000
select B.DisplayName, AH.TimeStarted, (cast((cast(tmp.MaxTimeStarted as float)- @substract) as datetime)) as [ApproxLastContactedTime (UTC)],
dateadd ( hh, +9, (cast((cast(tmp.MaxTimeStarted as float)- @substract) as datetime))) as 'ApproxLastContactedTime (Pacific)' from Availability A
join BaseManagedEntity B with(nolock) on B.BaseManagedEntityId = A.BaseManagedEntityId
join AvailabilityHistory AH with(nolock) on AH.BaseManagedEntityId = A.BaseManagedEntityId
join
(
select MAX(AHTMP.TimeStarted) AS MaxTimeStarted, BME.BaseManagedEntityId from AvailabilityHistory AHTMP
join BaseManagedEntity BME with(nolock) on BME.BaseManagedEntityId = AHTMP.BaseManagedEntityId
where BME.IsDeleted = 0
group by BME.BaseManagedEntityId
)
TMP on AH.TimeStarted = MaxTimeStarted
where A.IsAvailable = 0 and B.IsDeleted = 0
Robin, Thanks again!
Comments
Anonymous
July 28, 2008
Thought I would let you know both this and the other query return multiple results for cluster nodes.Anonymous
July 29, 2008
Why is there ever only one row of data (i.e. one computer) listed when I run this query? This is not very useful if it only returns one computer object and times checked in....how can this query be expanded to list more? I was thinking of turning this into a task launched from the console and using sqlcmd.exe C:Program FilesMicrosoft SQL Server90ToolsBinnsqlcmd.exe -s miwimo20 -d OperationsManager -E -i "c:tempsqlquerytest.sql" -W Thoughts?Anonymous
July 31, 2008
if you add a DISTINCT clause to the last SELECT you'll avoid to have duplicate rows... select DISTINCT B.DisplayName, AH.TimeStarted ...Anonymous
August 01, 2008
Thanks! Adding DISTINCT is a good point. I guess I would do that immediatelly if I knew T-SQL better, but I'm just C++ developer :)Anonymous
August 01, 2008
As I said in my previous post, this works only against agent computers which were ever unavailable. This doesn't work for all health services within topology ...Anonymous
June 18, 2012
Several years later and this works very well, I stumbled on this while looking for an answer to something else!