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 (6)

  1. DerekHar says:

    Thought I would let you know both this and the other query return multiple results for cluster nodes.

  2. geist_23@hotmail.com says:

    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?

  3. Daniele Grandini says:

    if you add a DISTINCT clause to the last SELECT you’ll avoid to have duplicate rows…

    select DISTINCT B.DisplayName, AH.TimeStarted …

  4. MSutara says:

    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 🙂

  5. MSutara says:

    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 …

  6. Several years later and this works very well, I stumbled on this while looking for an answer to something else!