Kaseya Community

SQL Script to purge all agents from the Kaseya Database

This question is answered

Hi;

Does anybody have a SQL script that they can share that will successfully purge all of the agents from the Ksubscribers DB?

Built a test server environment from our backups and it is giving me a License error because we now have twice as many agents and have gone over our limit.

Thanks Big Smile



[edited by: HardKnoX at 5:52 PM (GMT -7) on Mar 21, 2013] blah
Verified Answer
  • delete agents.sql
    Here you go this one i created to auto delete agents that haven't checked in in the last 45 days.
    PLEASE NOTE there is more to the right that you can't see 
    
    
    
    
    
    
    
    
    
    
    
    
    declare @Table0 varchar(50)  declare @Table1 varchar(50) 
    Declare @4Hours varchar(50)
    declare @Where varchar(500)
    declare @sql nvarchar(1000)
    declare @counterid varchar(50)
    Declare @60daysago varchar(50)

    USE [ksubscribers]


    set @4Hours = CONVERT(VARCHAR(50), (DATEADD (DAY, -45 , getutcdate() )), 121)
    print @4hours



    DECLARE curCounters CURSOR

    For SELECT dbo.machNameTab.agentguid FROM dbo.agentState INNER JOIN dbo.machNameTab ON dbo.agentState.agentGuid = dbo.machNameTab.agentGuid where (NOT (dbo.machNameTab.groupName LIKE '%Enfusion%')) AND (dbo.agentState.offlineTime < @4Hours)

    OPEN curCounters
    FETCH next FROM curCounters INTO @counterId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    print @counterId


    DELETE FROM machNameTab WHERE agentGuid = @counterId
    DELETE FROM users WHERE agentGuid = @counterId
    DELETE FROM agentState WHERE agentGuid = @counterId
    DELETE FROM acctRename WHERE oldAgentGuid = @counterId
    exec (@sql)

    FETCH next FROM curCounters INTO @counterId;
    END
    CLOSE curCounters
    DEALLOCATE curCounters


    [edited by: Michael Dixon (enfusion) at 6:47 PM (GMT -7) on Mar 21, 2013] l
All Replies
  • delete agents.sql
    Here you go this one i created to auto delete agents that haven't checked in in the last 45 days.
    PLEASE NOTE there is more to the right that you can't see 
    
    
    
    
    
    
    
    
    
    
    
    
    declare @Table0 varchar(50)  declare @Table1 varchar(50) 
    Declare @4Hours varchar(50)
    declare @Where varchar(500)
    declare @sql nvarchar(1000)
    declare @counterid varchar(50)
    Declare @60daysago varchar(50)

    USE [ksubscribers]


    set @4Hours = CONVERT(VARCHAR(50), (DATEADD (DAY, -45 , getutcdate() )), 121)
    print @4hours



    DECLARE curCounters CURSOR

    For SELECT dbo.machNameTab.agentguid FROM dbo.agentState INNER JOIN dbo.machNameTab ON dbo.agentState.agentGuid = dbo.machNameTab.agentGuid where (NOT (dbo.machNameTab.groupName LIKE '%Enfusion%')) AND (dbo.agentState.offlineTime < @4Hours)

    OPEN curCounters
    FETCH next FROM curCounters INTO @counterId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    print @counterId


    DELETE FROM machNameTab WHERE agentGuid = @counterId
    DELETE FROM users WHERE agentGuid = @counterId
    DELETE FROM agentState WHERE agentGuid = @counterId
    DELETE FROM acctRename WHERE oldAgentGuid = @counterId
    exec (@sql)

    FETCH next FROM curCounters INTO @counterId;
    END
    CLOSE curCounters
    DEALLOCATE curCounters


    [edited by: Michael Dixon (enfusion) at 6:47 PM (GMT -7) on Mar 21, 2013] l
  • Awesome thanks!  YesYes

    Saved me loads of time figuring out how the tables are linked! Big Smile

  • Glad i could help

  • It should be noted that for those curious..

    We (okay, me) had a huge concern in our environment of deleting agents from the VSA,
    out of fear that the agents would be gone and not able to check back in.

    Many (over 50%) of our agents out there are on home computers and personal laptops.

    Cases where applies, and some might think or consider about this particular issue is stolen machines.

    We still have some stolen machines (one I can think of in particular) which has not checked in since
    being stolen (6 months ago) and though unlikely that it will ever check in again, the possibility still exists,
    and if it ever does, I want to attempt recovery and (via Kaseya) pull some lost client data off that machine.

    Back to my point, any agents deleted from the VSA (even though NOT in your VSA anymore nor using an agent
    license), can and could still check back in at any point down the road and will re-populate into the VSA
    and previous name group.  

    Chris,

    P.S.   Routine maintenance such as purging agents that haven't checked in for awhile is good..

    And somehow, agents just seem to drop off.. ????

    (Surprises us more, cause most of those droped off, don't have system tray icon hidden)

    Out of almost 800 agents in our VSA, staff had purged against that hadn't checked in for 60 days,
    (which horrified me), and the number amounted to over 250 agents being deleted.   That shocked me,
    some being business machines, and alot of personal/home computers, etc.

  • The reason I wanted this was to purge the agents in my test environment that I created by restoring my production Kaseya VSA and database servers to  private Virtualized network.

    I needed purge the agents from the database to get around Licensing issue that prevented me from logging onto the VSA because I have gone over my license limit and I didn't want to spend the next 5-10 days being bounced around by Kaseya's support desk to get the issue resolved.