Duplicate Data In Datto Automate Plugin

Something changed over the weekend in the Datto Automate plugin, which broke the integration and our internal backup checks.

Specifically, it looks like the “Volume” element is now reporting as a name, like the “Agent” attribute always has.

However, this caused the Automate plugin to create new IDs for almost every server because it uses the “Volume” element as a key.

Take a look at this example:

id	device_serial_number	ClientID	LocationID	type	volume	operating_system	agent
123456	111111111111	1	1	Agent	exampleserver	Windows Server 2008R2	exampleserver
123457	111111111111	1	1	Agent	192.0.2.1	Windows Server 2008R2	exampleserver

Notice that the volume column is different and that difference created a new row with a different id value.

In our system, we need to delete all of these old rows to fix the monitors.  To find them, here’s a quick and dirty SQL statement:

SELECT volumes.id FROM plugin_datto_backup_volumes volumes
LEFT JOIN plugin_datto_backup_volumes AS volumes2 ON ((volumes.agent = volumes2.agent) AND (volumes.device_serial_number = volumes2.device_serial_number))
WHERE volumes.id < volumes2.id
AND (volumes.type = 'Agent')

I don't claim to be any kind of SQL expert, so deleting these is at your own risk.  The above finds everything in the backup_volumes table that's a duplicate, based on the hostname and device serial number, then grabs the lower id, which is the older entry.

If you try to just throw that into a DELETE statement, you’ll find that there is an issue with self-referencing tables like that.  See a discussion about this issue on Stack Overflow here.

To fix that, you need to remove the references and make them full-table subqueries like this:

DELETE FROM plugin_datto_backup_volumes WHERE id IN
(
SELECT volumes.id FROM (SELECT * FROM plugin_datto_backup_volumes) AS volumes
LEFT JOIN (SELECT * FROM plugin_datto_backup_volumes) AS volumes2 ON ((volumes.agent = volumes2.agent) AND (volumes.device_serial_number = volumes2.device_serial_number))
WHERE volumes.id < volumes2.id
AND (volumes.type = 'Agent')
) LIMIT 30

Not the cleanest query ever, but it worked for me and closed out the mountain of tickets this generated for us.

I did this in batches broken up by LIMIT clauses because I hate deleting things.

There is, of course, absolutely NO WARRANTY or ANY SUPPORT PROVIDED for the above.   If it breaks your database, restore from backup.  It worked here.

CW Automate – Excluding Servers From Malwarebytes

While we use the Malwarebytes plugin bundled with Automate, one of the annoyances we have with the solution is that there isn’t an option to exclude servers from deployment.

For anyone unfamiliar with the setup, the plugin presents an auto-deployment option. This will deploy Malwarebytes to all machines at the client, unless the machine is specifically marked as excluded.

The issue with that approach is newly installed servers won’t have the exclusion checked and Malwarebytes will install.

There are a few ways to approach this:

The first approach we looked into was setting the configuration option via the script that runs when a new agent is installed. However, the exclusion checkbox isn’t a traditional EDF in that it’s stored in a plugin_ table and not in the extrafielddata table, so this would just be a SQL execution in that script.

I didn’t really like that idea, so wrote the following to update all servers at once:

INSERT INTO plugin_malwarebytes_computer_settings(computerid,policyGUID,excludeMBAM,excludeMBAE,excludeMBARW)
SELECT computers.computerid, NULL, 1, 1, 1
FROM computers
LEFT JOIN inv_operatingsystem ON computers.computerid = inv_operatingsystem.computerid
WHERE inv_operatingsystem.server = 1 AND computers.os LIKE '%Windows%'
ON DUPLICATE KEY UPDATE excludeMBAM = 1, excludeMBAE = 1, excludeMBARW = 1

 

This pulls every computer from the computers table that is a server.  For all of those, it inserts or updates a row in the table used by the Malwarebytes plugin to store computer settings and sets it to exclude.

Run that query manually once to exclude for all current servers.  To exclude future servers, run this as a scheduled client script every 5 minutes.