Define Auto Connect Rule via SQL

alexander_bu asked a question.

September 21, 2018 at 7:00 AM

Hi all, Does anyone have experience whether one could define Auto Connect Rules via SQL directly rather than via SMACC? Let's say I have created a very basic rule, stating that all computers that have %WKS% as their name will be connected with a specific OU. The SQL statement then looks like this: ComputerID IN (SELECT c.ComputerID FROM tblComputer c WHERE c.CID = @CID AND c.HostName LIKE '%WKS%') Plus, the full dataset for the rule is fairly easily structured. So theroretically I should be able to create a rule directly in SQL. Why would I want to do that? Well, the Auto Connect Rules I can set in SMACC have a single major flaw: I can only set LIKE-conditions. Let's stick with the example above. My computer names are structured like [SITE]-[NAME]-[NUMBER]. While all the clients carry 'WKS' as [NAME], there is no such standard scheme for servers. For distribution of license costs I want my clients to be assigned to OUs based on their primary user. All the other computers shall be assigned to my "standard" Prod OU. The next logical step would be to create a rule like this: ComputerID IN (SELECT c.ComputerID FROM tblComputer c WHERE c.CID = @CID AND c.HostName NOT LIKE '%WKS%') I tried exactly this, but it doesn't work properly, and I don't know why. As a result both WKS-machines as well as non-WKS-machines are connected to my Prod OU. So the allocation is inconsistent. I wonder: Are their any interdependencies between the rule in SMACC and the SQL statement if they do not match contentwise (at least on the surface). And what exactly is the CID parameter? I don't quite get it. If anyone has a hint on that, please let me know. Plus, ‌ - fyi Best, Alex

Oliver Berger (Flexera Software)
8 years ago
Hi ?, well works for me. CID is the customerID. In Enterprise Editions, it's always 1, in Service Provider Editions it can vary. Make sure, - "Disable Auto Editing" is off, - you never ever use the SMACC again for touching rules or organisations - it will change and apply them when clicking okay, including reseting the unsupported "not like". - if you want to test your rule, you can use the "exec orgautocunnectrulesapply Procedure" afterwards. And if you want to move the Computers using SQL, why don't you update tblcomputerinfo.orgchecksum inside a Custom Stored Procedure, attached to the Data Update Job directly? Cheers Oliver.
Expand Post
- alexander_bu
  8 years ago
  Hi Oliver, "Never use the SMACC again for touching rules or organisations" - I do imagine that this is the problem in my test scenario. So the decision has to be made either to use SMACC OR to use SQL, right? I'll play around a little more. Thanks for the feedback! ‌: Ich halte dich auf dem Laufenden Best, Alex
  Expand Post