Version 1

    Hey Thwacksters!

     

    This report is handy, if you work with an Orion instance which manages client workstations. In many such environments, there is invariably a high turnover of equipment. It can quickly become difficult to work out which nodes have just gone down, and which ones have been down for a while, if there is a disconnect between Service Desk (the people doing the hardware replacement) and your Orion admins.

     

    If you create a new report, using the SQL query (not SWQL) below, it will return the nodes which have been down for more than 30 days. As a advocate of good use of Orion Custom Properties, I have also included a join on the NodesCustomProperty table, so you can bring these values into your report. Useful for adding grouping etc.

     

    (Note that you will need to substitute the Custom Property names in the  code below for your own CP values )

     

    Hope some of you find this useful!

     

    SELECT NodesData.Caption, NodesData.IP_Address,  NodesStatistics.LastSystemUpTimePollUtc, NodesCustomProperties.Custom_Property_A, NodesCustomProperties.Custom_Property_B, NodesCustomProperties.Custom_Property_C

    FROM dbo.NodesData

    INNER JOIN

    (NodesStatistics INNER JOIN NodesCustomProperties

    ON NodesCustomProperties.NodeID=NodesStatistics.NodeID)

    ON NodesData.NodeID=NodesStatistics.NodeID

    WHERE NodesStatistics.LastSystemUpTimePollUtc < DATEADD(day, -30, GETDATE())

    OR NodesStatistics.LastSystemUpTimePollUtc IS NULL

    ORDER BY NodesStatistics.LastSystemUpTimePollUtc ASC