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

Text File | 2009-09-30 | 17KB | 521 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 ,
    [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
    [VSE85AutoID] [int]            -- with VSE 8.5 autoid
)
go

-- Don't copy any tasks if 8.7 tasks already exist
if not exists (select * from EPOTask 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 EPOTask 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
(ParentID, ParentType, GlobalTaskID,  ProductCode, TaskType, TaskName, TaskDesc,
  PlatformsSupported, EditURL, SaveURL, TaskFlags, TheTimestamp, DeleteTimestamp, JustPlaceHolder, Priority, VSE87AutoID, VSE85AutoID)
select
ParentID, ParentType, GlobalTaskID, 'VIRUSCAN8700' as ProductCode, TaskType, TaskName, TaskDesc,
  PlatformsSupported, EditURL, SaveURL, TaskFlags, TheTimestamp, DeleteTimestamp, JustPlaceHolder, Priority, 0 as VSE87AutoID, autoid as VSE85AutoID
from EPOTask
where
EPOTask.ProductCode = 'VIRUSCAN8600'
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.5i)" if it is an old upgraded 7.1 task
update EPOTask
Set EPOTask.TaskName = replace(EPOTask.TaskName, ' (VSE 8.5i)', '')
where
patindex('% (VSE 8.5i)%', 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.5 task
update EPOTask
Set GlobalTaskID = VSE87AutoID
From #VseTmpUpgradeTask
where
EPOTask.GlobalTaskID = #VseTmpUpgradeTask.VSE85AutoID
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 @Root85Taskid int
Select @Root85Taskid = AutoId
From EPOTask
where ParentType = 7
and GlobalTaskID = 0
and ProductCode = 'VIRUSCAN8600'
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 = @Root85Taskid
and
(
ParentType in (6,5,4,3,1)
or
(ParentType = 7 and GlobalTaskID > 0 )
)
and TaskType = 'VSC700_Scan_Task'

-- copy VSE 8.5 task settings to VSE 8.7 task settings
-- Copy all non global task settings 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 EPOTaskSettings
(ParentID, SectionName, SettingName, Value, TheTimestamp)
select VSE87AutoID, SectionName, SettingName, Value, EPOTaskSettings.TheTimestamp
from EPOTaskSettings, #VseTmpUpgradeTask
where
EPOTaskSettings.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 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