ConnectWise Automate: SQL Spy Settings and Use

This week we are going to go through that annoying window that pops up and talk about actual use cases and how to enable/disable it.

Step by Step:

1. Today I wanted to go through that annoying little window that pops up called SQL Spy if you get it if you don’t then you may not know what I’m talking about but it actually is kind of useful let’s hop into why.
2. So if you are getting it and you don’t want it or you aren’t getting it and you do want it or find out what’s going on.
3. Come down to system, configuration dashboard, come over to config and control center.
4. On the right hand side we have all the system options. These are all well and fine the one we’re looking for is towards the bottom here display SQL queries for development and debugging.
5. Once you hit save if it’s off it will pop up if it’s on it will stay here you just have to close it and then it will save that value to your user.
6. It is personal it is not global so if you are an MSP getting it across your partner sometimes it does get reset through patches then you just have to have everybody come in here and turn it off otherwise they will keep continuing to get it.
7. So with SQL spy here especially if you’re on prem and you’re new to Automate but you’re kind of familiar with SQL as you can see it’s running queries inside of this little query log. What’s nice is that it allows us to see what’s actually going on so you can actually delete everything out of here so you can kind of start fresh and as I right click on a machine to pull up a menu notice that it’s running a query to give me that menu. that means that I can infer data that every time that I’m right-clicking it’s querying my user permissions and what the target of that machine is to see what this menu should be and then of course each time that it builds out a new menu it is querying the subset of each one of these. So that’s why Automate is a little slow it’s just it’s querying each subsequent thing to build the next menu or the next UI so keep that in mind it’s not a huge deal it’s actually kind of nice that it is pushing it every time.
8. But something like data views, for example, this is where it gets interesting because searches, data views, or even running scripts you can find those select statements and start building your own kind of repository especially if you’re hosted and you don’t really have access to the database.
9. Something you can do is if I come over to data views and come down to assets this is just my favorite one here if I come into agent search and click open I’m actually going to get this massive query.
10. Sometimes you do have to like look for it and so as I’m looking through here I can see this is running a select count it’s not what I’m looking for this is actually selecting the data view this is the click if you will the human click I’m selecting the data view so open that.
11. If I run all of this so I’m going to select all of this where computers OS like mac because it is filtering by this so I know this is at least the right query.
12. If I select all of this up to the select and I come into a raw SQL monitor if you don’t know what that is just really quick automation monitors, internal monitor, right click anything, hit new, start fresh, instead of here type raw SQL all caps and then I can paste anything down here and it executes directly against the database.
13. This is really messy so you might want to maybe paste in a notepad or something and clean it up a bit. In theory, if I hit build and view I should get that same exact information that I get here because I’m doing that exact same query.
14. If I clear out the search and hit enter that removed the where statement.
15. So what I’m going to do is just going to remove the where statement hit build a view.
16. The only reason I’m getting something different here is as I’m selecting all of these this user kind of has database access so we’re seeing more than technically allowed just something to keep in mind if you are trying to limit permissions.
17. Sometimes you can query things outside of your current permission level but ideally, you shouldn’t and the data views do kind of take into account your permission this is because the user that I’m using is a video user for us this is basically a super admin without actually checking the boxes with the goal of we are limiting the view just strictly for YouTube so that we don’t have anything crazy in here.