1 Reply Latest reply on Oct 27, 2016 4:09 PM by I LIKE EGGS

    Trying to query Nodes not collecting data for > 24 hours

    etcentene

      I have tried to create the report in report writer using Current status of Nodes, Volumes, etc.    However when I try to filter on last_Sync time the input is in Milliseconds and does not except the value I am trying to enter.     So I thought I would try to create this report with my "beginner" sql query skills and apparently I am not even a "Beginner".   Here is what I have so far.

       

      SELECT COUNT( * ) AS N_NodesNotCollecting FROM Nodes CROSS APPLY ( SELECT TOP 1 DateTime FROM ResponseTime WHERE NodeID = Nodes.NodeID ORDER BY DateTime DESC) ResponseTimeMostRecentCollected WHERE ResponseTimeMostRecentCollected.DateTime < DATEADD( HOUR, -24, GETDATE() ) AND Nodes.UnManaged = 0

       

       

      I understand that the SELECT TOP 1 is most likely not what I am wanting since I want all nodes not collecting data but I am not sure what I need to change this to.    Hell I may not even have the right query from the git go -

       

       

      Any help would be much appreciated.....   Even if the help is in the form of a decent SQL for jackasses video on youtube.....