Scripting

This query will show you computers that disconnected in the past five minutes and are still disconnected:

SELECT M.NAME, C.LAST_IP, C.CONNECT_TIME, C.DISCONNECT_TIME
FROM KBSYS.SMMP_CONNECTION C
JOIN ORG1.MACHINE M on M.KUID = C.KUID
WHERE C.DISCONNECT_TIME > DATE_SUB(NOW(), INTERVAL 5 MINUTE)
AND C.CLIENT_CONNECTED = 0

The KBSYS.SMMP_CONNECTION table is the table that will tell you which machines are connected or not, and the time of their last connection and disconnection. Determining if a machine was disconnected for more than five minutes in the past hour isn't possible because when the client re-connects to the SMA the DISCONNECT_TIME value is set to "0000-00-00 00:00:00" (meaning it hasn't disconnected).

You can create a report with the above query and schedule it to be sent to you every five minutes, but that could get overwhelming.

Combining information from the KBSYS.SMMP_CONNECTION and the ORG1.ASSET_HISTORY tables could conceivably create a report that would show you computers that were disconnected for more than five minutes in the past day. You would need to start with a query like this:

SELECT * FROM ORG1.ASSET_HISTORY
WHERE CHANGE_TYPE LIKE "Disconnection"
AND TIME > DATE_SUB(NOW(), INTERVAL 5 MINUTE)
ORDER BY TIME DESC;

From there you would have to join to the MACHINE table using the ASSET_ID and then to the KBSYS.SMMP_CONNECTION table using the KUID. At that point you would need to find computers that disconnected in the past day (based on the asset history) and then reconnected in more than five minutes (based on the connection data in SMMP_CONNECTION).

How is your SQL knowledge?