Jump to content
Duong

Automate Offline Agents - Monitor & Script

Recommended Posts

Special thanks: CTaylor, Gavsto, Mendy Green, kelliott_cio

This thread is for my solution (using Gavsto's SQL, and CTaylor's PoSH modules) detecting agents that are offline in ConnectWise Automate, but are online in ConnectWise Control.

 

Step 1. Create the monitor:

This is an exact copy of Gavsto's SQL. I take no credit.

image.thumb.png.e55b17fc4683338801f24e5765ba925e.png

SELECT     TIMESTAMPDIFF(MINUTE,c.LastContact, IFNULL(LastHeartbeatTime, "0000-00-00 00:00:00")) AS TestValue,
    c.name AS IdentityField,          
    c.ComputerID AS ComputerID,    
    c.LastContact,      
    h.LastHeartbeatTime,    
    acd.NoAlerts,    
    acd.UpTimeStart,    
    acd.UpTimeEnd    
FROM Computers AS c     
LEFT JOIN HeartBeatComputers AS h ON h.ComputerID = c.ComputerID
LEFT JOIN AgentComputerData AS acd ON c.ComputerID = acd.ComputerID
LEFT JOIN Clients ON Clients.ClientID = c.clientid
WHERE (c.LastContact > NOW() - INTERVAL 30 MINUTE OR h.LastHeartbeatTime > NOW() - INTERVAL 30 MINUTE)
AND (TIMESTAMPDIFF(MINUTE,c.LastContact, IFNULL(LastHeartbeatTime, "0000-00-00 00:00:00")) < -6 OR TIMESTAMPDIFF(MINUTE,c.LastContact, IFNULL(LastHeartbeatTime, "0000-00-00 00:00:00")) > 6)

 

Step 2. Create the script:

Please import the XML attached. You will have to modify lines 8-11, line 19 with your information.

Line 8, line 19 = the computer to run the script on. I'm hosted, so I could not use the ConnectWise Automate server. This computer needs to have PowerShell v3 or higher.

 

Step 3. Create the alert template:

image.png.5f50e1a53097217dba45775915e52bb8.png

 

Step 4. Set the alert template on your newly created global internal monitor.

 

 

 

 

Duong_Restart Labtech Agent.xml

Edited by Duong
  • Like 3

Share this post


Link to post
Share on other sites
1 hour ago, BlueToast said:

What does the script "Duong_Restart Labtech Agent*" do? Is there an Excel export of the script that I can review here?

I'm not sure how to do that. You can import the script, and review yourself.

The script gets the offline computer ID, then gets the ScreenConnect GUID, then restarts the LTService using a computer to run CWC PoSH.

Share this post


Link to post
Share on other sites

Version 1.1 script uploaded to original post.

I've added:
- check for non-Windows OS
- a check for the LTService after restart
- a reinstall of LTService if the restart fails
- a second check for the LTService after reinstall
- ticketing

 

Version 1.2 script uploaded to original post.

Fixed OS check.

Edited by Duong

Share this post


Link to post
Share on other sites

for line 9 in the script...the does the url for the control server need to have the port specified?

on the import of your script that value for the variable @ControlServer@ = https://:8040

so should my value look like: https://myFQDN:8040

or should it be: https://myFQDN

?

Thanks for posting your solution :)

Edited by cbrown_jsc

Share this post


Link to post
Share on other sites
1 hour ago, cbrown_jsc said:

for line 9 in the script...the does the url for the control server need to have the port specified?

on the import of your script that value for the variable @ControlServer@ = https://:8040

so should my value look like: https://myFQDN:8040

or should it be: https://myFQDN

?

Thanks for posting your solution :)

You must include the :8040. e.g. https://rmm.company.com:8040

Share this post


Link to post
Share on other sites

This is great, thanks for sharing.  

I removed the following portion from the RAWSQL, because the negative values are opposite (Control not running with Automate checking in).  I think I'll setup a separate monitor for the negative values to deal with Control not working properly.

(TIMESTAMPDIFF(MINUTE,c.LastContact, IFNULL(LastHeartbeatTime, "0000-00-00 00:00:00")) < -6

Is there a way to hide the Control password from the script logs?  We have co-it, and the password is shown in the script logs on the endpoint. I'd like to hide this if possible  

Share this post


Link to post
Share on other sites
On 11/30/2018 at 12:17 PM, Duong said:

I'm not sure how to do that. You can import the script, and review yourself.

The script gets the offline computer ID, then gets the ScreenConnect GUID, then restarts the LTService using a computer to run CWC PoSH.

When you exported the script as an XML file to attach here, before you save it, use the "File Type" drop-down to change from XML format to Excel XLS format. The Excel format will be a line-by-line human readable export (note: Excel XLS format cannot be imported).

Share this post


Link to post
Share on other sites
On 12/6/2018 at 12:54 AM, Appleton said:

This is great, thanks for sharing.  

I removed the following portion from the RAWSQL, because the negative values are opposite (Control not running with Automate checking in).  I think I'll setup a separate monitor for the negative values to deal with Control not working properly.


(TIMESTAMPDIFF(MINUTE,c.LastContact, IFNULL(LastHeartbeatTime, "0000-00-00 00:00:00")) < -6

Is there a way to hide the Control password from the script logs?  We have co-it, and the password is shown in the script logs on the endpoint. I'd like to hide this if possible  

But I thought Control was perfect? :P

Share this post


Link to post
Share on other sites

This is really awesome! Thanks for working this out. I was doing something like this myself but this works a bit better. 🙂

Share this post


Link to post
Share on other sites
On 11/30/2018 at 7:13 PM, Duong said:

You must include the :8040. e.g. https://rmm.company.com:8040

Just to clarify, the hostname and port should be whatever the web server responds to. I think 8040 is the default for hosted but many on-prem partners have used https as its also used for AD-HOC sessions. 

Great script - thanks for putting this together. 

Edited by amw3000

Share this post


Link to post
Share on other sites

Guys, when I try to set up the RAWSQL monitor, it is not accepting the '6' in the last section of script as it is thinking it is a column and not a value.  Anyone have this RAWSQL monitor working or have a working SQL query that works in SQLYog to throw my way?  

This is the SQL Query that when runs produces 'Unknown column '6' in 'where clause'.

 

SELECT TIMESTAMPDIFF(MINUTE,c.LastContact, IFNULL(LastHeartbeatTime, "0000-00-00 00:00:00")) AS TestValue,
c.name AS IdentityField,
c.ComputerID AS ComputerID,
c.LastContact,
h.LastHeartbeatTime,
acd.NoAlerts,
acd.UpTimeStart,
acd.UpTimeEnd
FROM Computers AS c
LEFT JOIN HeartBeatComputers AS h ON h.ComputerID = c.ComputerID
LEFT JOIN AgentComputerData AS acd ON c.ComputerID = acd.ComputerID
LEFT JOIN Clients ON Clients.ClientID = c.clientid
WHERE (c.LastContact > NOW() - INTERVAL 30 MINUTE OR h.LastHeartbeatTime > NOW() - INTERVAL 30 MINUTE)
AND (TIMESTAMPDIFF(MINUTE,c.LastContact, IFNULL(LastHeartbeatTime, "0000-00-00 00:00:00")) < -6 OR TIMESTAMPDIFF(MINUTE,c.LastContact, IFNULL(LastHeartbeatTime, "0000-00-00 00:00:00")) > 6)

Share this post


Link to post
Share on other sites

I am getting the following in the script log. Any ideas of what I need to change to correct this?

 

Invoke-CWCCommand : Needs permission
At line:1 char:41
+ irm 'https://bit.ly/controlposh' | iex; Invoke-CWCCommand -Server 
http://labtech ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorExcep 
   tion
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorExceptio 
   n,Invoke-CWCCommand
 
WARNING: Switch to -Credentials [PSCredential] authentication method.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×