Jump to content
Slaggard

Problem with RAWSQL internal monitor

Recommended Posts

This is only my 2nd RAWSQL monitor - hoping one of you guys spot the problem! 

It's designed to detect broken RMM agents by comparing Automate's lastcontact date to that of the Webroot GSM agent (the data for which is synced by its plugin into the plugin_webroot3_x tables).  It's enabled Globally (not targeted at any group), and currently it shows 44 results in its "Status" tab, as expected.  However, it only creates a single ticket, and logs all 44 alerts inside that ticket!

Here's the "additional condition" query from SQLyog (which I copy with Normalized Whitespace to paste-into the monitor).

SELECT c.lastcontact AS TestValue,c.name AS IdentityField,c.computerid AS ComputerID,c.lastcontact,cl.name,pwc.lastseen,acd.NoAlerts,acd.UpTimeStart,acd.UpTimeEnd
FROM computers c INNER JOIN plugin_webroot3_computers pwc ON c.computerid=pwc.computerid
LEFT JOIN clients cl ON c.clientid=cl.clientid
LEFT JOIN plugin_webroot3_clients pwcl ON c.clientid=pwcl.clientid
LEFT JOIN AgentComputerData AS acd ON c.ComputerID = acd.ComputerID
WHERE (c.lastcontact <= pwc.lastseen - INTERVAL 5 DAY)
AND (DATE(pwc.lastseen) >= NOW() - INTERVAL 1 DAY)
AND (cl.clientid NOT IN (290,276))
AND (c.ComputerID NOT IN (SELECT computerid FROM AgentIgnore WHERE AgentID=1560627))

 

Share this post


Link to post
Share on other sites

Use this thread to get information: https://www.mspgeek.com/topic/251--/?tab=comments#comment-20683
Or the official docs: https://docs.connectwise.com/ConnectWise_Automate/ConnectWise_Automate_Documentation/070/160/010/020?psa=1#Writing_a_Custom_Query

To get the status and alerts working right, you want to return as many columns as possible that a "normal" monitor returns, so use those examples to add in tables/columns you are missing.

The Computerid will determine which computer gets pinned with the ticket. The IdentityField determines which alerts are considered the same. (Two returned rows with the same Identity only triggers one alert).

Are all 44 alerts for the same computer? Is any computer associated to the ticket?

Share this post


Link to post
Share on other sites

Thanks Darren, looking at that now.  BTW the official documentation says to use "IDField" instead of "IdentityField", but I'm assuming that's a typo?  Every other guide I've looked at uses the latter.

For your last 2 questions: No, the alerts are all for different computers at different clients.  Yes there's a computer associated with the ticket.  It's like it handled the first alert properly, but then just stuck the rest of the alerts for that monitor under the same ticket.

Edited by Slaggard
add some info

Share this post


Link to post
Share on other sites

Not sure on the merged tickets. If you are perhaps using a script to generate the ticket, maybe there is an error there.

It doesn't matter what the first two columns returned are named, only their position matters.
For every additional column, the position (order) doesn't matter, but the name DOES.  So both IDField and IdentityField should work, since the name for that column doesn't matter.

Share this post


Link to post
Share on other sites

I'm not using a script to create tickets, just the same "Default - Create Automate Ticket" alert template we use for half our monitors.  Never seen this merging behavior during 18 months of creating monitors.

Thanks, that's good to know!  It cuts-down on the number of pseudo-random combinations I have to try.  I have a few other questions, if you happen to know off the top of your head; don't go to any trouble!

- If I use a field like computers.computerid as IdentityField in the 2nd col, do I still need to include it again in the first part of the SELECT clause?

- I'm using "Send Fail After Success", but the dialogue box says not to use date/time limiting with it.  My monitor relies on several date conditions - could that be part of the problem?  Gavsto's example here also uses DATE-based conditions, so it's obviously possible, which makes me wonder if there are just certain types of DATE-related syntax that this black-box of a SQL interpreter chokes on?

image.png.ef6c33a6ea86e9933ed4b7c44b3a0f1f.png

Edited by Slaggard
adding a sentenance at end

Share this post


Link to post
Share on other sites

Yes, even if you use computerid for your Identity, you still need a ComputerID column.

The comment about Date/Time limits is generally more for event logs, but if you include a time based criteria then when the time elapses the monitor will heal.

An example of a "bad idea" monitor that would use a time limiter - Suppose that you require that the time be between 12:00AM and 12:00PM (AND HOUR(NOW())<12). The monitor would alert on some condition while the time is between 12:00AM and 12:00PM. But anytime between 12:01PM and 11:59PM, even if the computer still would meet the criteria it will not be returned and the monitor will heal, closing the ticket.

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

×