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.
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 @4hoursDECLARE 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 curCountersDEALLOCATE curCounters
Saved me loads of time figuring out how the tables are linked!
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 sincebeing 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 agentlicense), can and could still check back in at any point down the road and will re-populate into the VSAand previous name group.
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.