How do I remove VigilEnt Log Analyzer components from NetIQ Vulnerability Manager database? (NETIQKB41784)

goal
How do I remove VigilEnt Log Analyzer components from NetIQ Vulnerability Manager database?

fact
NetIQ Vulnerability Manager 5.0

fact
VigilEnt Log Analyzer 1.2

fix

Copy and paste the following script into Microsoft SQL Query Analyzer:

/*

BACKUP DATABASE BEFORE RUNNING THIS SCRIPT.

This script will DELETE all endpoints and agents associated with VIM/LA.

*/

SET NOCOUNT ON

PRINT 'REMOVING VIM Common tab'
UPDATE osidgroups SET Enabled = 0 where Displayname = 'VIM Common' AND Enabled = 1

--create temp table to hold agent types to be removed
IF OBJECT_ID('tempdb.dbo.#Atype', 'U') IS NOT NULL
 DROP TABLE #Atype

CREATE TABLE #Atype (
 TypeID integer
)

--Get VIM/LA agent types
insert into #Atype
select TypeID from typetable where typeclass = 'Agent' and (typename like '%VIM%')

DECLARE
   @agentid INT,
   @x INT,
   @name varchar(100)

PRINT 'Unlinking Endpoints from Agents'
DECLARE cEP CURSOR LOCAL FOR
   select agentid, serverid from agentstoservers where agentid in (select agentid from agents where typeid in (select typeid from #atype)) and relationship <> 'H'

OPEN cEP
FETCH NEXT FROM cEP INTO @agentid, @x
 
WHILE @@FETCH_STATUS = 0
BEGIN

   SELECT @name=name FROM Servers where serverid = @x
   PRINT 'Unlinking EP: '+ISNULL(@name,'No name available') +'. ServerID:'+ STR(ISNULL(@x,-1))+' from AgentID:'+ STR(ISNULL(@agentid,-1))
   EXEC sp_DeleteLinkServerToAgent @ServerID=@x, @AgentID=@AgentID
   FETCH NEXT FROM cEP INTO @agentid, @x
END
CLOSE cEP
DEALLOCATE cEP

PRINT 'DELETING Agents'

--Agents
DECLARE cAAAA CURSOR LOCAL FOR
   select agentid from agentstoservers where agentid in (select agentid from agents where typeid in (select typeid from #atype))

OPEN cAAAA
FETCH NEXT FROM cAAAA INTO @x
 
WHILE @@FETCH_STATUS = 0
BEGIN

   SELECT @name=DisplayName FROM agents where agentid = @x
   PRINT 'Deleting: '+ISNULL(@name,'No name available') +'. AgentID:'+ STR(ISNULL(@x,-1))
   EXEC sp_deleteAgent @x

   FETCH NEXT FROM cAAAA INTO @x
END
CLOSE cAAAA
DEALLOCATE cAAAA

PRINT 'DELETING Log & Correlation Engines'
delete from #Atype
insert into #Atype select 829
insert into #Atype select 832

DECLARE cAAAA CURSOR LOCAL FOR
   select agentid from agentstoservers where agentid in (select agentid from agents where typeid in (select typeid from #atype))

OPEN cAAAA
FETCH NEXT FROM cAAAA INTO @x
 
WHILE @@FETCH_STATUS = 0
BEGIN

   SELECT @name=DisplayName FROM agents where agentid = @x
   PRINT 'Deleting: '+ISNULL(@name,'No name available') +'. AgentID:'+ STR(ISNULL(@x,-1))

   DELETE FROM ServerGroupMembers where servergroupid in (select servergroupid FROM ServerGroups WHERE AgentID = @x)
   UPDATE ServerGroupMembers_History set expirationtime = getdate() where servergroupid in (select servergroupid FROM ServerGroups WHERE AgentID = @x) and expirationtime is NULL

   EXEC sp_deleteAgent @x

   FETCH NEXT FROM cAAAA INTO @x
END
CLOSE cAAAA
DEALLOCATE cAAAA

PRINT 'DELETING Analysis Engines'
delete from #Atype
insert into #Atype select 830

DECLARE cAAAA CURSOR LOCAL FOR
   select agentid from agentstoservers where agentid in (select agentid from agents where typeid in (select typeid from #atype))

OPEN cAAAA
FETCH NEXT FROM cAAAA INTO @x
 
WHILE @@FETCH_STATUS = 0
BEGIN

   SELECT @name=DisplayName FROM agents where agentid = @x
   PRINT 'Deleting: '+ISNULL(@name,'No name available') +'. AgentID:'+ STR(ISNULL(@x,-1))

   DELETE FROM ServerGroupMembers where s.
ervergroupid in (select servergroupid FROM ServerGroups WHERE AgentID = @x)
   UPDATE ServerGroupMembers_History set expirationtime = getdate() where servergroupid in (select servergroupid FROM ServerGroups WHERE AgentID = @x) and expirationtime is NULL

   EXEC sp_deleteAgent @x

   FETCH NEXT FROM cAAAA INTO @x
END
CLOSE cAAAA
DEALLOCATE cAAAA

PRINT 'REMOVING VIM/LA relationships'

--list of all relationship types
delete from agentxref where ParentObjectTypeID in (
   select typeid from typetable where typeclass = 'Agent' and (typename like '%VIM%' or typename like '%engine%'))
   or ChildObjectTypeID in (
   select typeid from typetable where typeclass = 'Agent' and (typename like '%VIM%' or typename like '%engine%'))

PRINT 'REMOVING VIM Middleware node'
DELETE FROM VSOC_SnapinAppsConfig WHERE TaskID = 16004
DELETE FROM VSOC_SnapinApps WHERE TaskID = 16004

PRINT 'DONE!'

.


note

This script removes 3 database components related to VigilEnt Log Analyzer:

1. VIM Common Tab
2. VLA endpoints  
3. VIM Middleware node

Formerly known as NETIQKB41784