Video Screencast Help

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