/build/static/layout/Breadcrumb_cap_w.png

K1000: Report of recent Amp Connection Loss

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 :)

 

 


Comments

  • Question.
    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 7 years ago
  • Try something like this if your label was named Dell

    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 7 years ago
  • Trying now. Why would you set the interval to 60000 minutes? That seems rather long. - AFCUjstrick 7 years ago
  • I edited it to 60, that was simply a test on my machine to return results. My agent is always connected. :) - jdornan 7 years ago
  • So let me see if I understand all this correctly.
    If a machine lost connection to the K1000 over 60 minutes ago it will show in this report right? - AFCUjstrick 7 years ago
  • No within the last 60 minutes, or in reporting terms 60 or less(<= 60) - jdornan 7 years ago
  • Gotcha. Apologies for all the questions. - AFCUjstrick 7 years ago
  • No problem, its why ITNinja exists ;) - jdornan 7 years ago
  • When I run the report as an actual report I get results.
    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 7 years ago
  • Set mine up as a schedule as well and it worked the same as running it. - jdornan 7 years ago
  • I guess my problem probably is due to the fact that I'm trying to run it as an email alert rather than an emailed report. - AFCUjstrick 7 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ