Jump to content

Search the Community

Showing results for tags 'sql'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • MSPGeek
    • Announcements
    • The Geek Cast
  • ConnectWise Automate / Labtech
    • ConnectWise Automate / LabTech
    • ConnectWise Automate / LabTech - Development

Categories

  • ConnectWise Automate
    • Scripts
    • Plugins
    • SQL Snippets
    • Role Definitions
    • Automate PowerShell Code
    • Reports
    • Internal Monitors
    • Remote Monitors
  • ConnectWise Manage
    • API Interacting Code

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


About Me


Location


Agent Count


INTERESTS


OCCUPATION


ICQ


WEBSITE


WLM


YAHOO


AOL


FACEBOOK


GOOGLEPLUS


SKYPE


TWITTER


YOUTUBE

Found 5 results

  1. We've all come to the forums at some point looking for a way to get more specific results from a monitor than is available with the default tools and options in CWA, and have stumbled across posts detailing the complex and arcane process of making RAWSQL monitors. These are basically where you take all the builtin logic that monitors do behind the scenes and recreate it manually with a query. The advantages of this are that you can get much more specific with your queries to a degree simply not possible in a regular monitor. There are downsides to RAWSQL monitors though. They require a lot more work up front. Regular monitors do a lot in the background, like returning a bunch of info that ties the results to the computers detected, and CWA assumes this data is returned so a monitor that doesn't do this will not alert properly. This can be done manually, mostly by joining the agentcomputerdata table in the query and returning values from that. Even if you do this properly, some features like ignoring agents or group targeting (according to Darren) have to be done manually in the query rather than from the simple GUI. There are also issues with supportability, if you ask a support person to help you with anything even somewhat relating to a RAWSQL monitor they will laugh you right off the phone. So, what if you want to do something a little more complex than a regular monitor can handle, but don't want to deal with the atrocity that is RAWSQL? I would use what I like to call a LazySQL monitor. A LazySQL monitor is one in which you let the builtin monitor functions handle most of the busywork, and you use the additional conditions field to limit the results to computers returned from an SQL query or queries that do all the complex selections and limiting and whatnot. Basically, you make a monitor that by default catches every computer (I make it look for "computerid notequals 0"), I will attach an example of a monitor where I do just that so you can visualize it more easily. As you can see, the basic monitor configuration is very simple and would match all computers, then I do the more specific stuff, like returning computers that have patches missing and an EDF checked, as subqueries in the additional conditions field. This field basically just tacks on whatever is in it to the end of the SQL query put together by the monitor. For a simpler use case, let's take one I just finished explaining to a user in the slack channel. He wanted to put together a RAWSQL monitor to return all computers that didn't have a specific software installed, for our purposes let's say firefox. Instead of making a complex RAWSQL monitor to do this somewhat simple thing, he could use a LazySQL monitor. Using the same basic settings as the above picture, simply adding "AND computerid NOT IN(SELECT DISTINCT c.computerid FROM computers c JOIN software s ON c.computerid = s.computerid AND s.name LIKE "%firefox%")" as the additional condition field made the monitor limit it's results from all computers, to only those whose IDs were NOT returned by the subquery, which returns all those that do have firefox installed. This monitor will have greater supportability, greater functionality because it fits into CWA better, and took about 5 minutes to make and deploy. Please note that I am aware the last example returning computers without firefox could be accomplished easily with a regular monitor by using the invert check function. LazySQL monitors shine when you need to match a bunch of disparate criteria because it's easy to gather the computerids that match in a subquery and just check for "computerid not in (subquery)". Try not to nest a bunch of subqueries inside each other, if you can, because that can be slow. If you have any questions, you can always try asking me in the slack channel -Slartibartfast of Magrathea
  2. I have a mySQL database setup and I can run SQL Get Value queries against it successful. However when I try to run this query (SELECT preferredDC,preferredDCAID,Domain,defaultOU,defaultgroups,emailformat,emaildomain,logonformat FROM CustomerPortal.clientVars where AutomateID=12) as a SQL Get DataSet function it only returns "System.Data.DataSet" as the %sqldataset% I formed the query using HiediSQL and it works fine in there. SET: %sqlresult% = [SELECT preferredDC FROM CustomerPortal.clientVars WHERE AutomateID=12] using DSN-xxxxxx; WORKS Set %sqldataset% = SQL Get DataSet: SELECT preferredDC,preferredDCAID,Domain,defaultOU,defaultgroups,emailformat,emaildomain,logonformat FROM CustomerPortal.clientVars where AutomateID=12 using DSN=xxxxxx; DOES NOT WORK Any ideas?
  3. My situation is the following: I have created a SQL script which cleans up a SQL express database. I can run the command per machine on a need to run basis but would like to do this via a monitor that would monitor the file size of the mdf (master data file) and run whenever it reaches lets say +5GB. I went into the monitor wizard but was faced with the fact that I am only able to monitor file sizes up to 999,999,999 bytes. Does anyone know of a workaround or a better way of monitoring this file?
  4. Hi, I am trying to test an internal monitor I've created. I'm using a RAWSQL monitor. Unfortunately, when in the configuration window, if I go to the "Query Results" tab, the query seems to hang, and if I mouse over the results pane, it just displays the hourglass. Running the query in my DBMS works fine. More confusingly, another user can open the monitor configuration window and see the query results, so it doesn't seem to be an issue with the query. Does anyone have any ideas or experience with this issue? Thanks!
  5. This query can be used as an Additional Condition for an Internal Monitor, or merged with other queries. It attempts to identify computers that are "at their home" by looking at their public IP. It determines what the public IP should be for each location by selecting the probe for each location, and domain controllers in each location. If the computer's public IP matches with the probe's public IP, the computer is considered to be at it's assigned location. If this does not match, it will check the public IP for DC's at that location. If the computer's public IP matches with a Domain Controller's public IP, the computer is considered to be at it's assigned location Both tests check the last time the other computer was online. Probe's must have been online within the past 2 days. Domain Controllers must be online right now. Just add this into the Additional Conditions: AND computers.computerID IN (SELECT DISTINCT c.computerid AS ComputersAtLocation FROM computers AS c JOIN locations AS l ON c.locationid=l.locationid LEFT JOIN computers AS probes ON probes.computerid=l.probeid AND SUBSTRING_INDEX(probes.routeraddress, '.', 3)=SUBSTRING_INDEX(c.routeraddress, '.', 3) AND probes.lastcontact>DATE_ADD(NOW(),INTERVAL -2 DAY) LEFT JOIN computers AS dc ON dc.locationid=c.locationid AND dc.domain LIKE 'DC:%' AND SUBSTRING_INDEX(dc.routeraddress, '.', 3)=SUBSTRING_INDEX(c.routeraddress, '.', 3) AND dc.lastcontact>DATE_ADD(NOW(),INTERVAL -15 MINUTE) WHERE IFNULL(IFNULL(probes.computerid,dc.computerid),0)>0) To only include computers OUTSIDE of the "office", reverse the results with: AND NOT computers.computerID IN (SELECT DISTINCT c.computerid AS ComputersAtLocation FROM computers AS c JOIN locations AS l ON c.locationid=l.locationid LEFT JOIN computers AS probes ON probes.computerid=l.probeid AND SUBSTRING_INDEX(probes.routeraddress, '.', 3)=SUBSTRING_INDEX(c.routeraddress, '.', 3) AND probes.lastcontact>DATE_ADD(NOW(),INTERVAL -2 DAY) LEFT JOIN computers AS dc ON dc.locationid=c.locationid AND dc.domain LIKE 'DC:%' AND SUBSTRING_INDEX(dc.routeraddress, '.', 3)=SUBSTRING_INDEX(c.routeraddress, '.', 3) AND dc.lastcontact>DATE_ADD(NOW(),INTERVAL -15 MINUTE) WHERE IFNULL(IFNULL(probes.computerid,dc.computerid),0)>0)
×
×
  • Create New...