home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
gimli.bioss.sari.ac.uk
/
gimli.bioss.sari.ac.uk.zip
/
gimli.bioss.sari.ac.uk
/
pub
/
misc
/
McAfee8.7.Zip
/
ePOPolicyMigration.exe
/
CABFILE
/
1
/
80To87_Task_Upgrade_Script.sql
< prev
next >
Wrap
Text File
|
2009-09-30
|
17KB
|
537 lines
-------------------------------------------------------------------------------
------------- V S E 8 . 0 T O 8 . 7 T A S K U P G R A D E --------------
-------------------------------------------------------------------------------
if exists (select * from tempdb..sysobjects where name like '#VseTmpUpgradeTask%')
drop table #VseTmpUpgradeTask
go
CREATE TABLE [dbo].[#VseTmpUpgradeTask] (
[AutoID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentType] [tinyint] NOT NULL ,
[ParentID] [int] NOT NULL ,
[GlobalTaskID] [int] NOT NULL ,
[TaskStatus] [tinyint] NULL ,
[TheTimestamp] [binary] (8) NULL ,
[ProductCode] [nvarchar] (50) NULL ,
[DeleteTimestamp] [binary] (8) NULL ,
[TaskType] [nvarchar] (50) NULL ,
[TaskName] [nvarchar] (150) NULL ,
[PlatformsSupported] [nvarchar] (250) NULL ,
[JustPlaceHolder] [bit] NULL ,
[Priority] [int] NULL,
[VSE87AutoID] [int], -- must link VSE 8.7 autoid
[VSE80AutoID] [int] -- with VSE 8.0 autoid
)
go
declare @Continue int
declare @DoNotForce80 nvarchar(30)
declare @DoNotForce nvarchar(30)
select @Continue = 1
select @DoNotForce80 = 'DO_NOT_FORCE_80'
select @DoNotForce = 'DO_NOT_FORCE_%'
-- Don't copy any tasks if 8.7 tasks already exist
if exists (select * from Task where ProductCode = 'VIRUSCAN8700' and ParentType = 7 and GlobalTaskID > 0)
begin
select @Continue = 0
end
if @DoNotForce80 like @DoNotForce
begin
if exists (select * from Task where ProductCode = 'VIRUSCAN8600' and ParentType = 7 and GlobalTaskID > 0)
begin
select @Continue = 0
end
end
if @Continue = 1
begin
-- Convert VSE8.0 tasks
-- Copy over all task data and relink globaltaskid field
-- Must copy over task and policies not just move them
-- Copy all non global Task table 8.0 policies to 8.7 policies
-- Copy over global root tasks, but only those with GlobalTaskID > 0
-- copy all VSE 8.0 tasks
insert #VseTmpUpgradeTask
(ParentType, ParentID, GlobalTaskID, TaskStatus, TheTimestamp, ProductCode, DeleteTimestamp,
TaskType, TaskName, PlatformsSupported, JustPlaceHolder, Priority, VSE87AutoID, VSE80AutoID)
select
ParentType, ParentID, GlobalTaskID, TaskStatus, TheTimestamp, 'VIRUSCAN8700' as ProductCode, DeleteTimestamp,
TaskType, TaskName, PlatformsSupported, JustPlaceHolder, Priority, 0 as VSE87AutoID, autoid as VSE80AutoID
from Task
where
Task.ProductCode = 'VIRUSCAN8000'
and
(
ParentType in (6,5,4,3,1)
or
(ParentType = 7 and GlobalTaskID > 0 )
)
and Task.TaskType = 'VSC700_Scan_Task'
order by autoid
-- Find next task autoid value
Declare @NextTaskid int
Select @NextTaskid = IDENT_CURRENT('Task')
-- populate VSE87AutoID field in #VseTmpUpgradeTask
Update #VseTmpUpgradeTask
set VSE87AutoID = @NextTaskid + autoid
-- create VSE 8.7 tasks
insert Task
(ParentType, ParentID, GlobalTaskID, TaskStatus, TheTimestamp, ProductCode, DeleteTimestamp,
TaskType, TaskName, PlatformsSupported, JustPlaceHolder, Priority)
select
ParentType, ParentID, GlobalTaskID, TaskStatus, TheTimestamp, 'VIRUSCAN8700' as ProductCode, DeleteTimestamp,
TaskType, TaskName + ' (VSE 8.7i)', PlatformsSupported, JustPlaceHolder, Priority
from #VseTmpUpgradeTask
order by autoid
-- Delete the old "(VSE 8.0i)" if it is an old upgraded 7.1 task
update Task
Set Task.TaskName = replace(Task.TaskName, ' (VSE 8.0i)', '')
where
patindex('% (VSE 8.0i)%', Task.TaskName) > 0
and Task.ProductCode = 'VIRUSCAN8700'
and
(
Task.ParentType in (6,5,4,3,1)
or
(Task.ParentType = 7 and Task.GlobalTaskID > 0 )
)
and Task.TaskType = 'VSC700_Scan_Task'
-- Update GlobalTaskID field, link it to VSE 8.7 task instead of VSE 8.0 task
update Task
Set GlobalTaskID = VSE87AutoID
From #VseTmpUpgradeTask
where
Task.GlobalTaskID = #VseTmpUpgradeTask.VSE80AutoID
and Task.ProductCode = 'VIRUSCAN8700'
and
(
Task.ParentType in (6,5,4,3,1)
or
(Task.ParentType = 7 and Task.GlobalTaskID > 0 )
)
and Task.TaskType = 'VSC700_Scan_Task'
-- Update the GlobalTaskID for global root tasks,
-- (but only for those with with GlobalTaskID > 0)
Declare @Root80Taskid int
Select @Root80Taskid = AutoId
From Task
where ParentType = 7
and GlobalTaskID = 0
and ProductCode = 'VIRUSCAN8000'
and TaskType = 'VSC700_Scan_Task'
Declare @Root87Taskid int
Select @Root87Taskid = AutoId
From Task
where ParentType = 7
and GlobalTaskID = 0
and ProductCode = 'VIRUSCAN8700'
and TaskType = 'VSC700_Scan_Task'
update Task
Set GlobalTaskID = @Root87Taskid
where
Task.ProductCode = 'VIRUSCAN8700'
and GlobalTaskID = @Root80Taskid
and
(
ParentType in (6,5,4,3,1)
or
(ParentType = 7 and GlobalTaskID > 0 )
)
and TaskType = 'VSC700_Scan_Task'
-- copy VSE 8.0 tasksettings to VSE 8.7 task settings
-- Copy all non global TaskSetting from 8.0 Task settings to 8.7 Task settings
-- copy only settings where GlobalTaskID > 0
-- Change parentid to point to the VSE 8.7 task
insert TaskSettings
(ParentID, SectionName, SettingName, Value, TheTimestamp)
select VSE87AutoID, SectionName, SettingName, Value, TaskSettings.TheTimestamp
from TaskSettings, #VseTmpUpgradeTask
where
TaskSettings.ParentID = #VseTmpUpgradeTask.VSE80AutoID
/* ----------------------------------------------------------------------------
Change the SectionName and/or the SettingName of the policies that have moved.
A "-" sign indicates that the value is no longer used and is to be deleted only.
OLD VALUE: NEW VALUE:
[Actions] [Actions]
uAction = 3 (Move) => = 4 (Delete)
uSecAction => = 1 (Continue)
[Actions] [Actions]
uAction = 5 (Clean)
uSecAction = 3 (Move) => = 4 (Delete)
[Actions] [Actions]
uAction = 4 (Delete)
uSecAction = 3 (Move) => = 1 (Continue)
[Spyware] [Spyware]
uAction_Program = 3 (Move) => = 4 (Delete)
uSecAction_Program => = 1 (Continue)
[Spyware] [Spyware]
uAction_Program = 5 (Clean)
uSecAction_Program = 3 (Move) => = 4 (Delete)
[Spyware] [Spyware]
uAction_Program = 4 (Delete)
uSecAction_Program = 3 (Move) => = 1 (Continue)
[Actions]
-szMoveToFolder
-szSecMoveToFolder
[Reports]
-bLogUsername
[Advanced]
bDeferScanOnBattery=0
[Advanced]
bDeferScanInFullScreen=0
[Advanced]
uDeferTime=1
----------------------------------------------------------------------------- */
/* ----------------------------------------------------------------------------
[Actions] [Actions]
uAction = 3 (Move) => = 4 (Delete)
uSecAction => = 1 (Continue)
---------------------------------------------------------------------------- */
update TaskSettings
set Value = '1'
where
SectionName = 'Actions'
and SettingName = 'uSecAction'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
and b.SectionName = 'Actions'
and b.SettingName = 'uAction'
and b.Value = '3'
)
update TaskSettings
set Value = '4'
where
SectionName = 'Actions'
and SettingName = 'uAction'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
and b.SectionName = 'Actions'
and b.SettingName = 'uAction'
and b.Value = '3'
)
/* ----------------------------------------------------------------------------
[Actions] [Actions]
uAction = 5 (Clean)
uSecAction = 3 (Move) => = 4 (Delete)
---------------------------------------------------------------------------- */
update TaskSettings
set Value = '4'
where
SectionName = 'Actions'
and SettingName = 'uSecAction'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b, TaskSettings c
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
and b.SectionName = 'Actions'
and b.SettingName = 'uAction'
and b.Value = '5'
and b.ParentID = c.ParentID
and c.SectionName = 'Actions'
and c.SettingName = 'uSecAction'
and c.Value = '3'
)
/* ----------------------------------------------------------------------------
[Actions] [Actions]
uAction = 4 (Delete)
uSecAction = 3 (Move) => = 1 (Continue)
---------------------------------------------------------------------------- */
update TaskSettings
set Value = '1'
where
SectionName = 'Actions'
and SettingName = 'uSecAction'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b, TaskSettings c
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
and b.SectionName = 'Actions'
and b.SettingName = 'uAction'
and b.Value = '4'
and b.ParentID = c.ParentID
and c.SectionName = 'Actions'
and c.SettingName = 'uSecAction'
and c.Value = '3'
)
/* ----------------------------------------------------------------------------
[Spyware] [Spyware]
uAction_Program = 3 (Move) => = 4 (Delete)
uSecAction_Program => = 1 (Continue)
---------------------------------------------------------------------------- */
update TaskSettings
set Value = '1'
where
SectionName = 'Spyware'
and SettingName = 'uSecAction_Program'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
and b.SectionName = 'Spyware'
and b.SettingName = 'uAction_Program'
and b.Value = '3'
)
update TaskSettings
set Value = '4'
where
SectionName = 'Spyware'
and SettingName = 'uAction_Program'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
and b.SectionName = 'Spyware'
and b.SettingName = 'uAction_Program'
and b.Value = '3'
)
/* ----------------------------------------------------------------------------
[Spyware] [Spyware]
uAction_Program = 5 (Clean)
uSecAction_Program = 3 (Move) => = 4 (Delete)
---------------------------------------------------------------------------- */
update TaskSettings
set Value = '4'
where
SectionName = 'Spyware'
and SettingName = 'uSecAction_Program'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b, TaskSettings c
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
and b.SectionName = 'Spyware'
and b.SettingName = 'uAction_Program'
and b.Value = '5'
and b.ParentID = c.ParentID
and c.SectionName = 'Spyware'
and c.SettingName = 'uSecAction_Program'
and c.Value = '3'
)
/* ----------------------------------------------------------------------------
[Spyware] [Spyware]
uAction_Program = 4 (Delete)
uSecAction_Program = 3 (Move) => = 1 (Continue)
---------------------------------------------------------------------------- */
update TaskSettings
set Value = '1'
where
SectionName = 'Spyware'
and SettingName = 'uSecAction_Program'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b, TaskSettings c
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
and b.SectionName = 'Spyware'
and b.SettingName = 'uAction_Program'
and b.Value = '4'
and b.ParentID = c.ParentID
and c.SectionName = 'Spyware'
and c.SettingName = 'uSecAction_Program'
and c.Value = '3'
)
/* ----------------------------------------------------------------------------
[Actions]
-szMoveToFolder
-szSecMoveToFolder
---------------------------------------------------------------------------- */
delete TaskSettings
where
TaskSettings.SectionName = 'Actions'
and TaskSettings.SettingName = 'szMoveToFolder'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
)
delete TaskSettings
where
TaskSettings.SectionName = 'Actions'
and TaskSettings.SettingName = 'szSecMoveToFolder'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
)
/* ----------------------------------------------------------------------------
[Reports]
-bLogUsername
---------------------------------------------------------------------------- */
delete TaskSettings
where
TaskSettings.SectionName = 'Reports'
and TaskSettings.SettingName = 'bLogUsername'
and TaskSettings.parentid in
(select b.parentid
from #VseTmpUpgradeTask, TaskSettings b
where
b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
)
/* ----------------------------------------------------------------------------
[Advanced]
bDeferScanOnBattery=0
---------------------------------------------------------------------------- */
insert into TaskSettings
(ParentID, SectionName, SettingName, Value)
select TaskSettings.parentid, 'Advanced', 'bDeferScanOnBattery', 0
from TaskSettings, #VseTmpUpgradeTask
where
TaskSettings.SectionName = 'Advanced'
and TaskSettings.SettingName = 'bDoHsm'
and TaskSettings.parentid = #VseTmpUpgradeTask.VSE87AutoID
/* ----------------------------------------------------------------------------
[Advanced]
bDeferScanInFullScreen=0
---------------------------------------------------------------------------- */
insert into TaskSettings
(ParentID, SectionName, SettingName, Value)
select TaskSettings.parentid, 'Advanced', 'bDeferScanInFullScreen', 0
from TaskSettings, #VseTmpUpgradeTask
where
TaskSettings.SectionName = 'Advanced'
and TaskSettings.SettingName = 'bDoHsm'
and TaskSettings.parentid = #VseTmpUpgradeTask.VSE87AutoID
/* ----------------------------------------------------------------------------
[Advanced]
uDeferTime=1
---------------------------------------------------------------------------- */
insert into TaskSettings
(ParentID, SectionName, SettingName, Value)
select TaskSettings.parentid, 'Advanced', 'uDeferTime', 1
from TaskSettings, #VseTmpUpgradeTask
where
TaskSettings.SectionName = 'Advanced'
and TaskSettings.SettingName = 'bDoHsm'
and TaskSettings.parentid = #VseTmpUpgradeTask.VSE87AutoID
/* ----------------------------------------------------------------------------
[Reports]
Alert_ExcludeCookies=1
---------------------------------------------------------------------------- */
insert into TaskSettings
(ParentID, SectionName, SettingName, Value)
select TaskSettings.parentid, 'Reports', 'Alert_ExcludeCookies', 1
from TaskSettings, #VseTmpUpgradeTask
where
TaskSettings.SectionName = 'Reports'
and TaskSettings.SettingName = 'bLogToFile'
and TaskSettings.parentid = #VseTmpUpgradeTask.VSE87AutoID
/* ----------------------------------------------------------------------------
[Advanced]
dwHeuristicNetCheckSensitivity=0
---------------------------------------------------------------------------- */
insert into TaskSettings
(ParentID, SectionName, SettingName, Value)
select TaskSettings.parentid, 'Advanced', 'dwHeuristicNetCheckSensitivity', 0
from TaskSettings, #VseTmpUpgradeTask
where
TaskSettings.SectionName = 'Advanced'
and TaskSettings.SettingName = 'bDoHsm'
and TaskSettings.parentid = #VseTmpUpgradeTask.VSE87AutoID
end
go
if exists (select * from tempdb..sysobjects where name like '#VseTmpUpgradeTask%')
drop table #VseTmpUpgradeTask
go
/* ----------------------------------------------------------------------------
Update the task timestamps
---------------------------------------------------------------------------- */
if exists (select * from tempdb..sysobjects where name like '#vsetasktime%')
drop table #vsetasktime
go
begin
-- Update the timestamps so that the agents know there are new tasks
declare @Currenttime int
select @Currenttime = @@dbts
update task set TheTimestamp = @Currenttime
where ProductCode = 'VIRUSCAN8700'
select parentid as autoid, parenttype, max(thetimestamp)as maxtime
into #vsetasktime
from task
where ProductCode = 'VIRUSCAN8700'
group by parentid, parenttype
update leafnode
set tasktimestamp = #vsetasktime.maxtime + 1
from #vsetasktime
where leafnode.autoid = #vsetasktime.autoid
and #vsetasktime.parenttype = leafnode.type
update branchnode
set tasktimestamp = #vsetasktime.maxtime + 1
from #vsetasktime
where branchnode.autoid = #vsetasktime.autoid
and #vsetasktime.parenttype = branchnode.type
update tasksettings set TheTimestamp = @Currenttime
from task
where tasksettings.parentid = task.autoid
and task.ProductCode = 'VIRUSCAN8700'
end
go
if exists (select * from tempdb..sysobjects where name like '#vsetasktime%')
drop table #vsetasktime
go