The advanced search in Automate is pretty handy, but every now and then, I run into something that is easier to find with a SQL query, or a SQL query is the only way to group computers based on some very specific conditions. And I want to use this query to define membership of an autojoin group.
For this example, I wanted to find computers that do not have a specific local user account. Local user accounts are stored in the ‘UserAccounts’ field in the ‘computers’ table, which is unfortunately not one of the fields you can query using the advanced search.
All you need to do is create a new row in the ‘sensorchecks’ table. Give the search a name in the ‘Name’ column, and paste your SQL query into the ‘SQL’ column. Make sure the ‘QueryType’ is 4, which makes it a computer search (after some experimentation, I learned that type 20 is for network devices). Define a ‘FolderID’ if known, or just use ‘0’ to put it in the root of the searches folder.
The ‘ListDATA’ column can be ‘READONLY’ and then the last thing you need is a GUID. To generate one, run SELECT UUID() and copy the result. That’s it! You don’t need to worry about the ‘SearchXML’ column.
After you reload the system cache, your search should be available to load in the Advanced Search window, and you should be able to apply it as an autojoin search to a group.