The ConnectWise Manage plugin for ConnectWise Automate works great most of the time, but from time to time tickets fail to sync. This is frustrating and nearly invisible until a client calls complaining about tickets unanswered. You could assign an employee to check every few hours for unsynced tickets, or just cross your fingers and hope that the client that alerts you is a nice one. The much better option is to create a monitor in CWA to watch for it. A remote monitor wont do it, because no machine has the information you need. So this is the job for an internal monitor. Internal monitors search the data in the database.

The simple bit of information that makes this monitor work is that on the ticket table of the database, there is a field called “External ID”. Checking for that will tell us if a ticket has synced yet. So, lets finally get to creating. Internal Monitors can be found at Automation > Monitors > Internal Monitors. For this, we will be creating a “RAWSQL” monitor. For more details, see Gavsto’s page on RAWSQL. I’m not going to go into full detail here, but needless to say, RAWSQL have quite a complex set of requirements. So I’m going to break down my script, but I highly recommend reading Gavsto’s page for more detail on the process and requirements.

Go ahead and right click and create a new monitor. I recommend starting fresh. Here’s the data dump:

Monitor Internals

So there is everything you need to make this monitor work. I do recommend setting you Monitor Mode and Deduplication to something like Once a Day. That way if you miss the alert, you will be reminded tomorrow to take care of it. One of the main reasons this is important is because in the SQL we are grouping and alerting by ClientID, which means if more tickets get skipped, it wont alert on those if it has already alerted for the client. This is great for deduplication of alerts, but makes it easy to miss.

SELECT Count(ClientID) as TestValue,ClientID as IdentityField, ClientID, t.ComputerID, noalerts, uptimestart, uptimeend
FROM tickets as t
LEFT JOIN AgentComputerData as acd on t.ComputerID = acd.ComputerID
WHERE 
     ExternalID = 0
     AND StartedDate <= date_add(now(), interval -10 minute)
     AND t.ClientID > 1
     AND t.ComputerID NOT IN (Select AgentIgnore.ComputerID from AgentIgnore Where AgentID=1374941)
GROUP BY ClientID

I’ll let Gavsto explain why we use the TestValue and IdentityField, as well as why we join the AgentComputerData. I’m just going to start with the WHEREs.

The ExternalID is automatically filled in by the CWM plugin when a ticket is synced. So if that is 0, it means the ticket hasn’t synced yet.

The startdate checks to see if the ticket is at least 10 minutes old. It was pointed out the date_sub(now(), interval 10 minutes) might be a little more straight forward, but when I wrote it I didn’t know about date_sub, only date_add.

ClientID > 1 is a little particular to my setup. This filters out both tickets that don’t have a client (not sure if that is possible), and ClientID 1, which is our system client, that only holds workstations that have been retired and keep coming back. It helps clean it up.

Last of the WHEREs is explained in Gavsto’s post.

Finaly, we have the GROUP BY, in the course of making this monitor, I realized that the group by probably isn’t needed because I’m using ClientID as IdentityField, but for good measure it is in there to make sure there are no duplicate alerts for additional tickets for a client that has already triggered an alert.

And there you have it. A monitor to keep an eye on those darn unsynced tickets. Just remember to put an alert on it you can live with. I have mine going to Rocket.Chat, but you could setup an email or ticket.