Peter Gustavsson (Customer) asked a question.

Edited January 26, 2022 at 9:21 AM
Looking for SQL-code to add time of arrival in dynamic group "Down monitor".

Hi.

I'm looking for the SQL-code to create a dynamic group like the "Down Monitor", but with the addition of time of arrival.

Now it is only showing "Down", "Down 5 min", "Down 20 min" with the query below:

  1. SELECT DISTINCT Device.nDeviceID FROM Device JOIN MonitorState ON Device.nWorstStateID = MonitorState.nMonitorStateID AND Device.bRemoved = 0 AND MonitorState.nInternalMonitorState = 1

 

In the view we want to see what time for a monitor arrived, not just 2, 5, 20 mins ago.

I understand that it should be possible to do by combining data from 2 tables, but my SQL-knowledge is limited, and i dont know in what table the "time down" is stored.

 

  • 2 answers
  • 250 views

  • Mike Rockwell (Progress Software)

    Sorry for not finding this until a year later, it's better to ask questions in the General Discussion Section and only post into the Code share if you have code to share.

     

    Dynamic groups do not return anything other than the DeviceID of the devices which match your query. Where the Time the device went into that state will be shown on either the State Change Timeline report or the State Summary report. So you would use the report and set the group picker of the report to your group which shows the monitors down, then the report should give you want you want for only those devices.

  • Jason Alberino (Progress Software Company)

    To make sure I understand your request clearly: you want T-SQL to look for any device which had an active monitor get labeled down within the past X minutes. Is that correct?