Endpoint Protection

Hey everyone,

we are looking for a solution to automatically remove Clients from the SEPM Database. The script should delete them from the Database directly and not over the Web-Frontend. So we basically need an SQL-Statement which removed a certain client from the Database without harming it/putting the database in an inkonsistent state.

While searching the Web i found the following statements that should work. Now i need someone (preferably from Symantec) to confirm this or propose a better solution.

​

--DELETE FROM

-- SEM_COMPUTER

DELETE from [sem5].[sem5].[SEM_COMPUTER]

where  [COMPUTER_ID] in (SELECT [COMPUTER_ID]

FROM [sem5].[sem5].[SEM_CLIENT]

WHERE [COMPUTER_NAME] in 

(SELECT SEP_Clients FROM SEP_ClientToDelete))

GO

-- SEM_CLIENT

DELETE from [sem5].[sem5].[SEM_CLIENT]

where  [COMPUTER_ID] in (SELECT [COMPUTER_ID]

FROM [sem5].[sem5].[SEM_CLIENT]

WHERE [COMPUTER_NAME] in 

(SELECT SEP_Clients FROM SEP_ClientToDelete))

GO

-- SEM_AGENT

DELETE from [sem5].[sem5].[SEM_AGENT]

where  [COMPUTER_ID] in (SELECT [COMPUTER_ID]

FROM [sem5].[sem5].[SEM_CLIENT]

WHERE [COMPUTER_NAME] in 

(SELECT SEP_Clients FROM SEP_ClientToDelete))

Kind regards,

Julien

Hi,

anyone knows something about this or can provide a solution? I am really desperate.

Kind regards,

Julien

Hi Julian,

Here are a couple SQL's I've used in the past. I cannot say if this is a better or worse solution than what you already have, I just wanted to share what I've used, and you can decide if they are useful to you. I have not updated these queries in a while, there may be other tables I need to be checking, or some I should remove. Use at your own risk. 

-Mike

NukeCompID.sql

USE SEM12;

DECLARE @CompID char(32)
--Enter the unique ComputerID below.
SET @CompID = 'D624188123FDA50C002998C681D815B7'

SELECT * FROM [sem12].[dbo].[SEM_COMPUTER] Where [COMPUTER_ID] = @CompID

DELETE FROM [sem12].[dbo].[SEM_AGENT] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[SEM_CLIENT] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[SEM_COMPUTER] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[LEGACY_AGENT] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[AGENT_BEHAVIOR_LOG_1] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[AGENT_BEHAVIOR_LOG_2] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[AGENT_PACKET_LOG_1] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[AGENT_PACKET_LOG_2] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[AGENT_SECURITY_LOG_1] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[AGENT_SECURITY_LOG_2] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[AGENT_SYSTEM_LOG_1] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[AGENT_SYSTEM_LOG_2] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[AGENT_TRAFFIC_LOG_1] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[AGENT_TRAFFIC_LOG_2] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[ALERTS] Where [COMPUTER_IDX] = @CompID
DELETE FROM [sem12].[dbo].[COMPUTER_APPLICATION] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[GUP_LIST] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[LAN_DEVICE_DETECTED] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[SCANS] Where [COMPUTER_IDX] = @CompID
DELETE FROM [sem12].[dbo].[INVENTORYCURRENTRISK1] Where [COMPUTER_IDX] = @CompID
DELETE FROM [sem12].[dbo].[SEM_SVA] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[SEM_SVA_CLIENT] Where [COMPUTER_ID] = @CompID
DELETE FROM [sem12].[dbo].[SEM_SVA_COMPUTER] Where [COMPUTER_ID] = @CompID

NukeClientID.sql

USE SEM12;

DECLARE @ClientID char(32)
--Enter the unique ClientID below.
SET @ClientID = '035E1B1234FDA50C002778C60AD86BED'

DELETE FROM [sem12].[dbo].[SEM_CLIENT] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[SEM_AGENT] where [CURRENT_CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[LEGACY_AGENT] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[ENFORCER_CLIENT_LOG_1] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[ENFORCER_CLIENT_LOG_2] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[ENFORCER_TRAFFIC_LOG_1] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[ENFORCER_TRAFFIC_LOG_2] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[OAUTH_ACCESS_TOKEN] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[OAUTH_CLIENT_DETAILS] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[OAUTH_REFRESH_TOKEN] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[SEM_SVA] where [CURRENT_CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[SEM_SVA_CLIENT] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[SERVER_ADMIN_LOG_1] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[SERVER_ADMIN_LOG_2] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[SERVER_CLIENT_LOG_1] where [CLIENT_ID] = @ClientID
DELETE FROM [sem12].[dbo].[SERVER_CLIENT_LOG_2] where [CLIENT_ID] = @ClientID