MSP Automated, LLC

ConnectWise Automate RAWSQL Internal Monitors

Summary

Internal Monitors are SQL queries run against the ConnectWise Automate database. While a multitude of monitors can be created using the standard internal monitors interface, there are some items that may not be easily created this way.

One can create custom SQL queries and allow Automate to run those queries at set intervals then perform actions using Alert Templates based on the results. Historically, this feature has not be well documented or otherwise explained. Searching the web, you may find several ways to create these monitors but not all are considered best practice.

This is considered to be an advanced topic and requires knowledge of how to create SQL queries. In the below example, we’ll walk through creation of a basic RAWSQL monitor. This example uses a custom EDF that may not exist in your environment.


The SQL Query

The best way to start creating a RAWSQL monitor is by building the query using a MySQL management tool like SQLyog or MySQL Workbench. RAWSQL monitors in Automate require specific values in the query. Those values are:


TestValue – The condition we are checking. This will map to the %results% variable. This value must always be reported first in the query.

IdentityField -A value unique to the monitor to be passed to the %FieldName% variable.

ComputerID – Used to report alerts and populate the %computerid% variable.

NoAlerts – Pulled from the AgentComputerData table to determine if an alert should be generated.

UpTimeStart – Pulled from the AgentComputerData table to determine if an alert should be generated.

UpTimeEnd – Pulled from the AgentComputerData table to determine if an alert should be generated.


We are going to create a SQL query to identify computers that are excluded from CWA Patching with our Disable CWA Patch Management EDF. Here's the base query:


SELECT efd.value AS 'TestValue',
	c.name AS 'IdentiityField',
	c.computerid,
	c.name AS ‘computername’,
	l.locationid,
	l.name AS ‘locationname’,
	cl.clientid,
	cl.name AS ‘clientname’,
	acd.NoAlerts,
	acd.UpTimeStart,
	acd.UpTimeEnd
FROM computers c
LEFT JOIN agentcomputerdata acd ON c.computerid = acd.computerid
LEFT JOIN extrafielddata efd ON c.computerid = efd.id
WHERE efd.ExtrafieldID = (SELECT ef.id
			FROM extrafield ef
			WHERE ef.name = 'Disable CWA Patch Management'
			AND ef.form = 1
			AND ef.section = 'Patching')
AND efd.value = 1;


If we execute the above query directly through SQLyog, you should see a result similar to this.


Copy9dd9c47ea8e5.png


After verifying the query is functional, it’s time to create the monitor.


Creating the Monitor

In the Control Center, navigate to Automation -> Monitors. In the All Monitors windows, click on the Internal Monitors tab. Right-click in the Monitors list. Select New Monitor.


Copy2aea53635204.png


When the New Internal Monitor window appears, leave Available Monitors set as Start Fresh and click Next.

The Internal Monitor window will appear next. Name the monitor accordingly and configure settings for the run interval and alert frequency as desired.

In the Check Condition section of this window, we need to set Table to Check and Field to Check values as RAWSQL.


Copya60c0948eba7.png


In the Additional Condition text box, we need to enter our query and remove the formatting. In other words, our query we created above should look like this:


Copye9aea602b432.png


Go ahead and test the configuration by clicking Build and View Query. This should list the same results we saw when running the query in SQLyog earlier.


Copy502941fc43a2.png


Adding Support for Group Targeting and Agent Exclusion

Now we need to add some additional items to the query. If we’d like to link the monitor to specific groups (recommended) we need to add the below to the end of the query after linking the monitor to the target group or groups.


AND c.computerid IN(SELECT computerid FROM Tcomp)


If you’d like to have the ability to use the Exclusions tab of the monitor to exclude specific agents from the results, then the query should also have the below replacing XXXX with the monitor id number.


AND c.computerid NOT IN(SELECT computerid FROM agentignore WHERE agentid=XXXX)


Note that the monitor will need to be saved first so that a monitor id is generated. This number will be visible in the monitor title bar in parenthesis. Once the monitor is working, set your alert template and ticket category as you would for any other monitor.


Copy32a3ffc71c1a.png


There you have it! You can now create RAWSQL monitors to monitor just about anything in the database!

Leave a Response