Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

How to delete particular Computers from SEPM MSSQL Database.

Created: 21 Oct 2010 • Updated: 21 Oct 2010 | 2 comments
Language Translations
sezam's picture
+5 5 Votes
Login to vote

How to delete particular Computers from SEPM MSSQL Database.

This article is concerned to face with a problems:

  • deleting clients from SEPM Console which is connected to MSSQL Database and cannot be deleted directly from SEPM Console,
  • if option to delete automatically SEP client instance after fixed time of being inactive is not working,
  • If You see in the Reports, machines that were deleted.

On the begging You would need to create txt file with Computers names which You would like to delete from DB. I know that this is not an easiest way but thanks this You can be sure that You added proper machines.

Text file should be constructed as a row.

Example:

Where “Comp1”, “Comp2”, “Comp3” are examples for Computers names in DB.

After You save this file You need to open Microsoft SQL Management Studio and create new query.

use tempdb
--
--
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SEP_ClientToDelete]') AND type in (N'U'))
DROP TABLE [dbo].[SEP_ClientToDelete]
--
--
CREATE TABLE [SEP_ClientToDelete](
    [SEP_Clients] [nvarchar](max)
)
-- Change 'C:\myfaile.txt' to correct route to the file
BULK INSERT SEP_ClientToDelete FROM 'C:\myfaile.txt'
WITH 
    ( 
        ROWTERMINATOR = '\n' 
    )
 
SELECT * FROM SEP_ClientToDelete
GO
 
--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))

If You installed SEPM DB by default this will work without changes.

If Your Database has different name You need to change Table names.

Example:

[sem5].[sem5].[SEM_CLIENT] is wrong

Go to Microsoft SQL Management Studio and find SEM_CLIENT table.

Check what is the exact name of the SEPM Database and SEM_CLIENT table.

                                         

Than change [sem5].[sem5].[TABLENAME] to [name of DB].[name of DB].[TABLENAME]

I hope this was informative for You and You evaluate my work ;)

Comments 2 CommentsJump to latest comment

James Winslow's picture

yes this is a good article man.

Thanks for sharing.

+1
Login to vote
Yoann WYFFELS's picture

Hello,

I'm not sure your MSSQL queries will work entirely...

You are deleting SEM_CLIENT entries before SEM_AGENT. But to delete SEM_AGENT entries you need COMPUTER_ID which are in SEM_CLIENT (!?) surprise

0
Login to vote