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_For_ePO4.0.sql

Text File | 2009-09-30 | 18KB | 542 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 ,
    [ParentID] [int] NOT NULL ,
    [ParentType] [tinyint] NOT NULL ,
    [GlobalTaskID] [int] NOT NULL ,
    [ProductCode] [nvarchar] (50) NULL ,
    [TaskType] [nvarchar] (50) NULL ,
    [TaskName] [nvarchar] (256) NULL ,
    [TaskDesc] [nvarchar] (250) NULL ,
    [PlatformsSupported] [nvarchar] (250) NULL ,
    [EditURL] [nvarchar] (250) NULL ,
    [SaveURL] [nvarchar] (250) NULL ,
    [TaskFlags] [tinyint] NULL ,
    [TheTimestamp] [binary] (8) NULL ,
    [DeleteTimestamp] [binary] (8) 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 EPOTask where ProductCode = 'VIRUSCAN8700' and ParentType = 7 and GlobalTaskID > 0)
begin
    select @Continue = 0
end

if @DoNotForce80 like @DoNotForce
begin
    if exists (select * from EPOTask 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 EPOTask 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
(ParentID, ParentType, GlobalTaskID,  ProductCode, TaskType, TaskName, TaskDesc,
  PlatformsSupported, EditURL, SaveURL, TaskFlags, TheTimestamp, DeleteTimestamp, JustPlaceHolder, Priority, VSE87AutoID, VSE80AutoID)
select
ParentID, ParentType, GlobalTaskID, 'VIRUSCAN8700' as ProductCode, TaskType, TaskName, TaskDesc,
  PlatformsSupported, EditURL, SaveURL, TaskFlags, TheTimestamp, DeleteTimestamp, JustPlaceHolder, Priority, 0 as VSE87AutoID, autoid as VSE80AutoID
from EPOTask
where
EPOTask.ProductCode = 'VIRUSCAN8000'
and
(
ParentType in (6,5,4,3,1)
or
(ParentType = 7 and GlobalTaskID > 0 )
)
and EPOTask.TaskType = 'VSC700_Scan_Task'
order by autoid

-- Find next task autoid value
Declare @NextTaskid int
Select @NextTaskid = IDENT_CURRENT('EPOTask')

-- populate VSE87AutoID field in #VseTmpUpgradeTask
Update #VseTmpUpgradeTask
set VSE87AutoID = @NextTaskid + autoid

-- create VSE 8.7 tasks
insert EPOTask
(ParentID, ParentType, GlobalTaskID,  ProductCode, TaskType, TaskName, TaskDesc,
  PlatformsSupported, EditURL, SaveURL, TaskFlags, TheTimestamp, DeleteTimestamp, JustPlaceHolder, Priority)

select
ParentID, ParentType, GlobalTaskID,   'VIRUSCAN8700' as ProductCode,
    TaskType, TaskName + ' (VSE 8.7i)', TaskDesc, PlatformsSupported,
    EditURL, SaveURL, TaskFlags, TheTimestamp, DeleteTimestamp, JustPlaceHolder, Priority
from #VseTmpUpgradeTask
order by autoid

-- Delete the old "(VSE 8.0i)" if it is an old upgraded 7.1 task
update EPOTask
Set EPOTask.TaskName = replace(EPOTask.TaskName, ' (VSE 8.0i)', '')
where
patindex('% (VSE 8.0i)%', EPOTask.TaskName) > 0
and EPOTask.ProductCode = 'VIRUSCAN8700'
and
(
EPOTask.ParentType in (6,5,4,3,1)
or
(EPOTask.ParentType = 7 and EPOTask.GlobalTaskID > 0 )
)
and EPOTask.TaskType = 'VSC700_Scan_Task'

-- Update GlobalTaskID field, link it to VSE 8.7 task instead of VSE 8.0 task
update EPOTask
Set GlobalTaskID = VSE87AutoID
From #VseTmpUpgradeTask
where
EPOTask.GlobalTaskID = #VseTmpUpgradeTask.VSE80AutoID
and EPOTask.ProductCode = 'VIRUSCAN8700'
and
(
EPOTask.ParentType in (6,5,4,3,1)
or
(EPOTask.ParentType = 7 and EPOTask.GlobalTaskID > 0 )
)
and EPOTask.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 EPOTask
where ParentType = 7
and GlobalTaskID = 0
and ProductCode = 'VIRUSCAN8000'
and TaskType = 'VSC700_Scan_Task'

Declare @Root87Taskid int
Select @Root87Taskid = AutoId
From EPOTask
where ParentType = 7
and GlobalTaskID = 0
and ProductCode = 'VIRUSCAN8700'
and TaskType = 'VSC700_Scan_Task'

update EPOTask
Set GlobalTaskID = @Root87Taskid
where
EPOTask.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 task settings to VSE 8.7 task settings
-- Copy all non global task settings 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 EPOTaskSettings
(ParentID, SectionName, SettingName, Value, TheTimestamp)
select VSE87AutoID, SectionName, SettingName, Value, EPOTaskSettings.TheTimestamp
from EPOTaskSettings, #VseTmpUpgradeTask
where
EPOTaskSettings.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 EPOTaskSettings
set Value = '1'
where
SectionName = 'Actions'
and SettingName = 'uSecAction'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings b
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    and b.SectionName = 'Actions'
    and b.SettingName = 'uAction'
    and b.Value = '3'
    )

update EPOTaskSettings
set Value = '4'
where
SectionName = 'Actions'
and SettingName = 'uAction'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings 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 EPOTaskSettings
set Value = '4'
where
SectionName = 'Actions'
and SettingName = 'uSecAction'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings b, EPOTaskSettings 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 EPOTaskSettings
set Value = '1'
where
SectionName = 'Actions'
and SettingName = 'uSecAction'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings b, EPOTaskSettings 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 EPOTaskSettings
set Value = '1'
where
SectionName = 'Spyware'
and SettingName = 'uSecAction_Program'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings b
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    and b.SectionName = 'Spyware'
    and b.SettingName = 'uAction_Program'
    and b.Value = '3'
    )

update EPOTaskSettings
set Value = '4'
where
SectionName = 'Spyware'
and SettingName = 'uAction_Program'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings 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 EPOTaskSettings
set Value = '4'
where
SectionName = 'Spyware'
and SettingName = 'uSecAction_Program'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings b, EPOTaskSettings 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 EPOTaskSettings
set Value = '1'
where
SectionName = 'Spyware'
and SettingName = 'uSecAction_Program'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings b, EPOTaskSettings 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 EPOTaskSettings
where
EPOTaskSettings.SectionName = 'Actions'
and EPOTaskSettings.SettingName = 'szMoveToFolder'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings b
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    )

delete EPOTaskSettings
where
EPOTaskSettings.SectionName = 'Actions'
and EPOTaskSettings.SettingName = 'szSecMoveToFolder'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings b
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    )

/* ----------------------------------------------------------------------------
[Reports]
-bLogUsername
---------------------------------------------------------------------------- */
delete EPOTaskSettings
where
EPOTaskSettings.SectionName = 'Reports'
and EPOTaskSettings.SettingName = 'bLogUsername'
and EPOTaskSettings.parentid in
    (select b.parentid
    from #VseTmpUpgradeTask, EPOTaskSettings b
    where
    b.ParentID = #VseTmpUpgradeTask.VSE87AutoID
    )

/* ----------------------------------------------------------------------------
[Advanced]
bDeferScanOnBattery=0
---------------------------------------------------------------------------- */
insert into EPOTaskSettings
(ParentID, SectionName, SettingName, Value)
select EPOTaskSettings.parentid, 'Advanced', 'bDeferScanOnBattery', 0
from EPOTaskSettings, #VseTmpUpgradeTask
where
EPOTaskSettings.SectionName = 'Advanced'
and EPOTaskSettings.SettingName = 'bDoHsm'
and EPOTaskSettings.parentid = #VseTmpUpgradeTask.VSE87AutoID

/* ----------------------------------------------------------------------------
[Advanced]
bDeferScanInFullScreen=0
---------------------------------------------------------------------------- */
insert into EPOTaskSettings
(ParentID, SectionName, SettingName, Value)
select EPOTaskSettings.parentid, 'Advanced', 'bDeferScanInFullScreen', 0
from EPOTaskSettings, #VseTmpUpgradeTask
where
EPOTaskSettings.SectionName = 'Advanced'
and EPOTaskSettings.SettingName = 'bDoHsm'
and EPOTaskSettings.parentid = #VseTmpUpgradeTask.VSE87AutoID

/* ----------------------------------------------------------------------------
[Advanced]
uDeferTime=1
---------------------------------------------------------------------------- */
insert into EPOTaskSettings
(ParentID, SectionName, SettingName, Value)
select EPOTaskSettings.parentid, 'Advanced', 'uDeferTime', 1
from EPOTaskSettings, #VseTmpUpgradeTask
where
EPOTaskSettings.SectionName = 'Advanced'
and EPOTaskSettings.SettingName = 'bDoHsm'
and EPOTaskSettings.parentid = #VseTmpUpgradeTask.VSE87AutoID

/* ----------------------------------------------------------------------------
[Reports]
Alert_ExcludeCookies=1
---------------------------------------------------------------------------- */
insert into EPOTaskSettings
(ParentID, SectionName, SettingName, Value)
select EPOTaskSettings.parentid, 'Reports', 'Alert_ExcludeCookies', 1
from EPOTaskSettings, #VseTmpUpgradeTask
where
EPOTaskSettings.SectionName = 'Reports'
and EPOTaskSettings.SettingName = 'bLogToFile'
and EPOTaskSettings.parentid = #VseTmpUpgradeTask.VSE87AutoID

/* ----------------------------------------------------------------------------
[Advanced]
dwHeuristicNetCheckSensitivity=0
---------------------------------------------------------------------------- */
insert into EPOTaskSettings
(ParentID, SectionName, SettingName, Value)
select EPOTaskSettings.parentid, 'Advanced', 'dwHeuristicNetCheckSensitivity', 0
from EPOTaskSettings, #VseTmpUpgradeTask
where
EPOTaskSettings.SectionName = 'Advanced'
and EPOTaskSettings.SettingName = 'bDoHsm'
and EPOTaskSettings.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 EPOTask set TheTimestamp = @Currenttime
where ProductCode = 'VIRUSCAN8700'

select parentid as autoid, parenttype, max(thetimestamp)as maxtime
into #vsetasktime
from EPOTask
where ProductCode = 'VIRUSCAN8700'
group by parentid, parenttype

update EPOLeafNode
set tasktimestamp = #vsetasktime.maxtime + 1
from #vsetasktime
where EPOLeafNode.autoid = #vsetasktime.autoid
and #vsetasktime.parenttype = EPOLeafNode.type

update EPOBranchNode
set tasktimestamp = #vsetasktime.maxtime + 1
from #vsetasktime
where EPOBranchNode.autoid = #vsetasktime.autoid
and #vsetasktime.parenttype = EPOBranchNode.type

update EPOTaskSettings set TheTimestamp = @Currenttime
from EPOTask
where EPOTaskSettings.parentid = EPOTask.autoid
and EPOTask.ProductCode = 'VIRUSCAN8700'

end
go

if exists (select * from tempdb..sysobjects where name like '#vsetasktime%')
drop table #vsetasktime
go