This report will give you a list of all Amp Agents who have lost connection in the last X minutes (Im using 60 here)
*************************************************
SELECT ASSET_HISTORY.NAME AS COMPUTER_NODE, ASSET_HISTORY.CHANGE_TYPE, ASSET_HISTORY.TIME AS DISCONNECT_TIME
FROM ASSET_HISTORY
LEFT JOIN MACHINE ON ASSET_HISTORY.NAME = MACHINE.NAME
WHERE ASSET_HISTORY.CHANGE_TYPE = 'Disconnection'
AND NOW() < DATE_ADD(ASSET_HISTORY.TIME, INTERVAL 60 MINUTE)
ORDER BY ASSET_HISTORY.TIME ASC
LIMIT 1000
*************************************************
You can change the timeframe to anything you want by changeing the interal number.
Below is the output.
**Note- I limited the ouput to 1000 records because the history can be quite large. I dont advise changing that unless you know what you are doing. Besides if you have more than 1000 agents that lost connection in the last hour you proabably shouldnt me running reports :)
How would I set this up to report only on a specific label of machines?
For instance if I wanted to use this to setup and email alert for when machines in the "Server Smart" label met the criteria.
Is that possible? - AFCUjstrick 11 years ago
SELECT ASSET_HISTORY.NAME AS COMPUTER_NODE, ASSET_HISTORY.CHANGE_TYPE, ASSET_HISTORY.TIME AS DISCONNECT_TIME
FROM ASSET_HISTORY
LEFT JOIN MACHINE ON ASSET_HISTORY.NAME = MACHINE.NAME
LEFT JOIN MACHINE_LABEL_JT ON MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
LEFT JOIN LABEL on MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
WHERE ASSET_HISTORY.CHANGE_TYPE = 'Disconnection'
AND NOW() < DATE_ADD(ASSET_HISTORY.TIME, INTERVAL 60 MINUTE)
AND LABEL.NAME = 'Dell'
ORDER BY ASSET_HISTORY.TIME ASC
LIMIT 1000 - jdornan 11 years ago
If a machine lost connection to the K1000 over 60 minutes ago it will show in this report right? - AFCUjstrick 11 years ago
When I let it run as a scheduled email alert with the exact same sql all I get is an email with a table with lots of very squashed rows. No actual data.
Any thoughts? - AFCUjstrick 11 years ago