Kaseya Community

Here is a SQL script to automatically Clear Overrides

  • I have been working on an automatic means of clearing over rides for some time now. For me it seems that the machines go out of compliance for a host of reasons and im constantly having to go back and clear overrides to ensure the proper policies are pushed out
    since then i've spent significant time reverse enginering Kaseya to figure out how i could automate this process.  
    I have come up with this SQL that will clear all overrides on all machines. i created an SQL server agent job which is run after the compliance check every night this SQL job then goes though and clears those over rides
     
    Declare @Agentguid varchar(50)

    Declare @policyObjectTypeFK varchar(5)

     

    use ksubscribers

     

    DECLARE curCounters CURSOR For

    SELECT policy.policyObjectAgentStatus.agentGuid, policy.policyObjectAgentStatus.policyObjectTypeFK from policy.policyObjectAgentStatus where policy.policyObjectAgentStatus.policyObjectStatus <> 5 

     

                    OPEN curCounters

                    FETCH next FROM curCounters INTO @Agentguid, @policyObjectTypeFK;

                                    WHILE @@FETCH_STATUS = 0

                                                    BEGIN                                  

                                                                    --print @Agentguid

                                                                    --print @policyObjectTypeFK;

                                                                   

                                                                    EXEC [policy].[agentPolicyObjectOverrideRemove] @partitionId = 1,@agentGuid = @Agentguid,@policyObjectTypeFK = @policyObjectTypeFK

                                                                 

                                                    FETCH next FROM curCounters INTO @Agentguid, @policyObjectTypeFK;

                                                    END

        CLOSE curCounters

       DEALLOCATE curCounters  

    i guessing some of you might say but i dont want to clear over riders on all my machines. i would then change this script to look for a custom field against the machine which you could add the state too.

  • I'm not sure I need it yet, but thank you for sharing this.

  • thanks for sharing I had to add one thing to let it process the changes

    EXEC [policy].[eventProcessAgentPolicyObjectOverrideRemove] @partitionid=1,@agentguid = @agentguid,@policyobjecttypefk = @policyobjecttypefk

  • Hi,

    can you share the sql script ? How do you run it against the server ?

    rgds

  • Hi,

    I Run this as a job on the SQL server Agent

    Declare @Agentguid varchar(50)

    Declare @policyObjectTypeFK varchar(5)

    use ksubscribers

    DECLARE curCounters CURSOR For

    SELECT policy.policyObjectAgentStatus.agentGuid, policy.policyObjectAgentStatus.policyObjectTypeFK from policy.policyObjectAgentStatus where policy.policyObjectAgentStatus.policyObjectStatus <> 5

    OPEN curCounters

    FETCH next FROM curCounters INTO @Agentguid, @policyObjectTypeFK;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC [policy].[agentPolicyObjectOverrideRemove] @partitionId = 1,@agentGuid = @Agentguid,@policyObjectTypeFK = @policyObjectTypeFK

    EXEC [policy].[eventProcessAgentPolicyObjectOverrideRemove] @partitionid=1,@agentguid = @agentguid,@policyobjecttypefk = @policyobjecttypefk

    FETCH next FROM curCounters INTO @Agentguid, @policyObjectTypeFK;

    END

    CLOSE curCounters

    DEALLOCATE curCounters

  • can it be scheduled as from within the kaseya interface to have it run once a week as this problem re-occurs often

  • I'm not sure if you can run this from within a procedure, since 6.3 (on-premise only) you are able to run SQL queries after you upload them to the kaseya server in XML format.

    but I tried only single line queries until now if you can run this level of queries I'm not sure.

    but in the SQL job agent you also can schedule it, I run it personally every day.

  • Somebody having the above in xml format ?

    rgds

  • Have Accidentally clicked on Flag as SPAM/Abuse...

    My apologies.

  • Is this query still valid in R9.1 ?

    I've been running into this issue more often lately.

  • I just received this error today, also running 9.1, and it looks to be caused (this time by .net)

    11:36:28 7-Mar-16 $inst$ptc0$kaseyadb01._nonvaults.test.custom._.ubistor--inc- *System*

    Script Summary: Failed in the if step

    11:36:28 7-Mar-16 $inst$ptc0$kaseyadb01._nonvaults.test.custom._.ubistor--inc- *System*

    FAILED to load $inst$ptc0$kaseyadb01._nonvaults.test.custom._.ubistor--inc- (ID = 1414838001).

    **Error: Failed to query the approval status of the agent procedure.

    11:30:32 7-Mar-16 $inst$ptc0$kaseyadb01._nonvaults.test.custom._.ubistor--inc- *System*

    Executed patch (4608): KB3127229/MS16-019 -

    Security Update for Microsoft .NET Framework 4.5.2 on Windows 7, Vista, Windows Server 2008, Windows Server 2008 R2 for x64 (KB3127229)

    My work around was a reboot if pending procedure. I run it against all the servers I'm currently doing maintenance on that seem to not do anything for hours. Once rebooted, patching continues fine on some servers, others need more hand holding.

  • Does anyone have an updated clear override script for 9.2?

    Is it also possible to clear override a specific policy?

    For example, my administrators keep overriding the Remote Control policy to Silently take control and keep forgetting to clear this override when they're done. I would like to automatically clear this override every night.

    Regards,

    Pattie

  • Although i have not tested this on 9.2 i would still expect it to work.( though NOT supported by Kaseya)

    It should be possible to modify this so it works for only one policy but that would take some work to do so.

  • Thanks Michael Dixon, script works great. Modified the query a little so it only overrides the remote control policy.

    I do have one question though: "EXEC [policy].[agentPolicyObjectOverrideRemove]" basically runs the override button right?

    How did you know about this? I am new to scripting and would like to know how to find function names like this.