/build/static/layout/Breadcrumb_cap_w.png
11/27/2018 207 views

Hello, I'm new to KACE and want to see if someone(s) would be gracious enough to point me in the right direction. I've been tasked with creating a daily report for monitoring node heartbeats and inventory. I'm using SMA 9.0


This is the basic criteria for now is to report devices that have been disconnected for longer than the periods specified :

Agent Disconnect: Over 5 minutes for heartbeat and 2-3 hours for inventory

Agent-less Disconnect: Over 5 minutes for heartbeat and 24-28 hours for inventory


I'd like heartbeats to be reported that haven't polled in over 5 minutes and ensure nodes are being inventoried based on whether they're agent/agent-less since we have a mixed OS environment. 


They should continue being inventoried within the 2-3 hours/24-28 hours.


Thank you in advance. 

4 Comments   [ + ] Show comments

Comments

  • My understanding is that agent-less devices don't have a constant connection to the appliance, so I don't know how you could know if it wasn't connected.

    Also, I don't understand the need for a report that runs once a day that shows computers that have been disconnected for over five minutes. Do you mean that you want a report of machines that were disconnected for a five minute or longer span within the last 24 hours?
  • I have high-visibility needs and would need to know if there are any devices that haven't polled within the 5 minutes. If there's not heartbeat, I'd have to look into why one didn't poll for 30 minutes, for example. Was it due to server performance, a scheduled job, etc.? It would help give improved insight on the network.
  • A little bit too much, and that won't be possible with agent less devices... as @chucksteel said...

    Plus you will need to some kind of array to store the results, I mean reports always run vs. existing data, I don't think there is a field in the Database that tracks:
    "HOW_MANY_TIMES_A_DEVICE_WAS_DISCONNECTED_FOR_OVER_5_MINUTES" and "WHEN"

    You are talking about a Monitoring+Reporting type of thing, the KACE does that but for Servers and is a different license....

    Plus an agent who was disconnected for +5 minutes, might be because there was patching job running, and a reboot was required... the PC might take a while before the login screen at "Applying Updates..... X out of X"... then it logs in and check-ins fine with the KACE Appliance.
  • What about a report that would give me just details for disconnect time from the appliance? Is there any workaround for the agent-less device reporting in this scenario?

All Answers

0

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?

Answered 11/29/2018 by: chucksteel
Red Belt

  • Thanks for this. Yes, the second query is more of what I need. Basically, what disconnected in the previous day that way I can track trends if it's the same device and prevent future disconnects. My SQL knowledge is just ok.