Last contacted, better SQL query

  • Article

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!