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/85To87_Task_Upgrade_Script.sql

Text File | 2009-09-30 | 17KB | 516 lines

-------------------------------------------------------------------------------

-------------  V S E  8 . 5  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
    [VSE85AutoID] [int]            -- with VSE 8.5 autoid
)
go

-- Don't copy any tasks if 8.7 tasks already exist
if not exists (select * from Task where ProductCode = 'VIRUSCAN8700' and ParentType = 7 and GlobalTaskID > 0)
begin

-- Convert VSE8.5 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.5 policies to 8.7 policies
-- Copy over global root tasks, but only those with GlobalTaskID > 0

-- copy all VSE 8.5 tasks
insert #VseTmpUpgradeTask
(ParentType, ParentID, GlobalTaskID, TaskStatus, TheTimestamp, ProductCode, DeleteTimestamp,
    TaskType, TaskName, PlatformsSupported, JustPlaceHolder, Priority, VSE87AutoID, VSE85AutoID)
select
ParentType, ParentID, GlobalTaskID, TaskStatus, TheTimestamp, 'VIRUSCAN8700' as ProductCode, DeleteTimestamp,
    TaskType, TaskName, PlatformsSupported, JustPlaceHolder, Priority, 0 as VSE87AutoID, autoid as VSE85AutoID
from Task
where
Task.ProductCode = 'VIRUSCAN8600'
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.5i)" if it is an old upgraded 7.1 task
update Task
Set Task.TaskName = replace(Task.TaskName, ' (VSE 8.5i)', '')
where
patindex('% (VSE 8.5i)%', 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.5 task
update Task
Set GlobalTaskID = VSE87AutoID
From #VseTmpUpgradeTask
where
Task.GlobalTaskID = #VseTmpUpgradeTask.VSE85AutoID
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 @Root85Taskid int
Select @Root85Taskid = AutoId
From Task
where ParentType = 7
and GlobalTaskID = 0
and ProductCode = 'VIRUSCAN8600'
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 = @Root85Taskid
and
(
ParentType in (6,5,4,3,1)
or
(ParentType = 7 and GlobalTaskID > 0 )
)
and TaskType = 'VSC700_Scan_Task'

-- copy VSE 8.5 tasksettings to VSE 8.7 task settings
-- Copy all non global TaskSetting from 8.5 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.VSE85AutoID

/* ----------------------------------------------------------------------------
 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