CREATE    PROCEDURE    dbo.aip_generateviews
/*    Drops and rebuilds the Asset Intellegence views as a post step during
    database creation

    2005-10-27    Bill Merrow add hwuse to linuxcomputers.
    2005-8-4    Bill Merrow use swpub in pubappcomputers and pubappcacomputers.
            Fix pubappcacomputers/pubappcomputers to have swbld and so on.
    2005-08-04    George Curran, Computer Associates
            Add grant statements for all views
    2005-5-22    Bill Merrow add swuuid to apps for App count query in aiDetailComputerReport.
    2005-4-24    Bill Merrow hwuse.
    2005-4-19    Bill Merrow changes for MS SQL MDB (based on Alex Ingres optimization to align with that).
            Various views will likely disappear as they become hw cat entries for final beta.
    2005-3-28    Bill Merrow add fssize to linuxcomputers view - never caught before.
    2005-3-24    Bill Merrow
            Add hwsource to allcomputers.
    2005-2-25    Bill Merrow
            Update etrust/noetrust for new app defs CA eTrust antivirus.
    2005-1-22    Bill Merrow
            Add hwcpuperf to allcomputers (for Performance tab).
    2004-11-15    Bill Merrow
            Add fssize and fsfree to oldef_prop (for GIS reports).
    2004-11-02    Bill Merrow
            Remove osdef_prop references from allcomputers and use oldef_prop
            copies of fields instead.  osbuild and osuuid are not longer returned.
            This removes all left joins from allcomputers which improves performance.
            Updated linuxcomputers list to reflect the new content.
            These changes are all based upon data review done by George when DDS completed.
            They significantly improve Ingres performance for r11 sp1 release.
    2004-09-27    George Curran
            Remove unnecessary references to 'domainid' and 'unitid'
            columns.  Remove obsolete view 'pubsum' and 'pubsumcomputers'.
            Derive 'pubappdesktops', 'pubapplaptops' and pubappserver for
            pre-existing 'destopcomputers', 'laptopcomputers' and
            'servercomputers' views instead of re-executing queries with
            joins to source tables.  Add 'WHERE' and 'ORDER BY' clauses
            to 'storagebot50freespace' to ensure results match describption.
            Derive 'storagedestopcomputers' from 'storagecomputers' and
            'desktopcomputers'.
    2004-09-20    George Curran
            Adjust placement of pubapplaptops and pubappservers
            to follow creation of needed prerequisites lapcomputers
            and server computers
    2004-09-18    Bill Merrow
            Add pubapplaptops, pubappdesktops, pubappservers.
    2004-09-10    Bill Merrow
            Do not count Linux in desktops, only in servers.
    2004-09-07    Changes to support case insensitive - all names to lowercase.
    2004-09-03    George Curran, Computer Associates
            Stored procedure created to replace script used to
            generate required views.
            
            Depends on existance of all "ai' tables including
            the following created by post/external processing...
            
            ai_oldef_prop
            ai_usrdef_prop
            ai_usrdef_orgtree_prop

            Must be run after all other objects are created.
            
*/
AS
--    Set environment
    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON

--      Enable NOCOUNT option to suppress unnecessary messages
        SET NOCOUNT ON

--    Declare and initialize local variables
    DECLARE    @intReturn int

    SET    @intReturn = 0

--    View dbo.allcomputers    Script Date: 6/19/2004 6:55:36 PM
--    Modified 2004-09-27 to remove unnecessary columns 'domainid' and 'unitid', George Curran, Computers Associates
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[allcomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[allcomputers]

    EXEC    ('    
        CREATE    VIEW dbo.allcomputers
        AS
        SELECT    DISTINCT dbo.ai_oldef_prop.userid,
            dbo.ai_oldef_prop.orgnm,
            dbo.ai_oldef_prop.office,
            dbo.ai_oldef_prop.hwsource,
            dbo.ai_oldef_prop.region,
            dbo.ai_oldef_prop.osname,
            dbo.ai_oldef_prop.ossp,
            dbo.ai_oldef_prop.osver,
            dbo.ai_oldef_prop.ospub,
            dbo.ai_oldef_prop.oslanguage,
            dbo.ai_oldef_prop.hwuse,
            ai_oldef_prop.fssize,
            ai_oldef_prop.fsfree,
            dbo.ai_oldef_prop.instdate,
            dbo.ai_oldef_prop.lscandate,
            dbo.ai_hwdef_prop.*,
            round(round(convert(decimal(5,2),coreavg/10),0)*10,0) as hwcpuperf
        FROM    dbo.ai_hwdef_prop
            INNER JOIN
            dbo.ai_oldef_prop
            ON dbo.ai_hwdef_prop.hwuuid = dbo.ai_oldef_prop.hwuuid
            LEFT JOIN
            ai_hwdef_cpuperf_kpi
            ON ai_hwdef_prop.hwuuid = ai_hwdef_cpuperf_kpi.hwuuid
        WHERE     ai_hwdef_prop.hwname != ''''
            ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on allcomputers to aiadmin
    grant delete on allcomputers to aiadmin
    grant select on allcomputers to aiadmin
    grant update on allcomputers to aiadmin
    grant delete on allcomputers to aiadmin
    grant insert on allcomputers to aiadmin
    grant select on allcomputers to aipublic

--    View dbo.laptopcomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[laptopcomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[laptopcomputers]

    EXEC    ('
        CREATE    VIEW dbo.laptopcomputers
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers WHERE hwuse=''Laptop''
        /*WHERE    (hwtype = ''Notebook'')
        OR    (hwtype = ''Laptop'')
        OR    (hwtype = ''Portable'')*/
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on laptopcomputers to aiadmin
    grant delete on laptopcomputers to aiadmin
    grant select on laptopcomputers to aiadmin
    grant update on laptopcomputers to aiadmin
    grant delete on laptopcomputers to aiadmin
    grant insert on laptopcomputers to aiadmin
    grant select on laptopcomputers to aipublic

--    View dbo.desktopcomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[desktopcomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[desktopcomputers]    

    EXEC    ('
        CREATE    VIEW dbo.desktopcomputers
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers WHERE hwuse=''Desktop''
        /*WHERE    (osname NOT LIKE ''%Server%'')
        AND    (osname NOT LIKE ''%server%'')
        AND    (osname!=''AIX'')
        AND    (osname!=''SunOS'')
        AND    (osname NOT LIKE ''%Linux%'')
        AND    (osname!=''HP-UX'')
        AND     (    hwtype IS NULL
            OR    NOT (hwtype = ''Notebook''
            OR    hwtype = ''Laptop''
            OR    hwtype = ''Portable''))*/
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on desktopcomputers to aiadmin
    grant delete on desktopcomputers to aiadmin
    grant select on desktopcomputers to aiadmin
    grant update on desktopcomputers to aiadmin
    grant delete on desktopcomputers to aiadmin
    grant insert on desktopcomputers to aiadmin
    grant select on desktopcomputers to aipublic

--    View dbo.servercomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[servercomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[servercomputers]

    EXEC    ('
        CREATE  VIEW dbo.servercomputers
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers WHERE hwuse=''Server''
        /*WHERE    (    (osname LIKE ''%Server'')
            OR    (osname LIKE ''%Unix%'')
            OR    (osname LIKE ''%Linux%'')
            OR    (osname LIKE ''%Server%'')
            OR    (osname LIKE ''%server%'')
            OR    (osname=''HP-UX'')
            OR    (osname=''SunOS'')
            OR    (osname=''AIX''))
        AND    (    hwtype IS NULL
            OR    NOT (hwtype = ''Notebook''
            OR    hwtype = ''Laptop''
            OR    hwtype = ''Portable''))*/
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on servercomputers to aiadmin
    grant delete on servercomputers to aiadmin
    grant select on servercomputers to aiadmin
    grant update on servercomputers to aiadmin
    grant delete on servercomputers to aiadmin
    grant insert on servercomputers to aiadmin
    grant select on servercomputers to aipublic


--    View allwinxpsp2    Script Date: 10/06/2004    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[allwinxpsp2]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[allwinxpsp2]

    EXEC    ('
        CREATE    VIEW dbo.allwinxpsp2
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
        WHERE    osname like ''Windows XP%'' and ossp=''Service Pack 2''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on allwinxpsp2 to aiadmin
    grant delete on allwinxpsp2 to aiadmin
    grant select on allwinxpsp2 to aiadmin
    grant update on allwinxpsp2 to aiadmin
    grant delete on allwinxpsp2 to aiadmin
    grant insert on allwinxpsp2 to aiadmin
    grant select on allwinxpsp2 to aipublic


--    View allwinnotxpsp2    Script Date: 10/06/2004    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[allwinnotxpsp2]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[allwinnotxpsp2]

    EXEC    ('
        CREATE    VIEW dbo.allwinnotxpsp2
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
        WHERE    osname like ''Windows XP%'' and ossp!=''Service Pack 2''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on allwinnotxpsp2 to aiadmin
    grant delete on allwinnotxpsp2 to aiadmin
    grant select on allwinnotxpsp2 to aiadmin
    grant update on allwinnotxpsp2 to aiadmin
    grant delete on allwinnotxpsp2 to aiadmin
    grant insert on allwinnotxpsp2 to aiadmin
    grant select on allwinnotxpsp2 to aipublic


--    View dbo.aiv_usrtoorglast    Script Date: 10/02/2004    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aiv_usrtoorglast]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[aiv_usrtoorglast]

    EXEC    ('
        CREATE  VIEW aiv_usrtoorglast AS SELECT dbo.ai_usrdef_orgtree_prop.pmf as userid,
            ud_orgdef_list.olorgname, ud_orgdef_list.olnote, ud_orgdef_list.oldiv
        FROM    dbo.ai_usrdef_orgtree_prop INNER JOIN
                dbo.ud_orgdef_list ON dbo.ai_usrdef_orgtree_prop.reportsto LIKE ''%'' + dbo.ud_orgdef_list.oluserlist
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on aiv_usrtoorglast to aiadmin
    grant delete on aiv_usrtoorglast to aiadmin
    grant select on aiv_usrtoorglast to aiadmin
    grant update on aiv_usrtoorglast to aiadmin
    grant delete on aiv_usrtoorglast to aiadmin
    grant insert on aiv_usrtoorglast to aiadmin
    grant select on aiv_usrtoorglast to aipublic



--    View dbo.allorgloccomputers    Script Date: 6/19/2004 6:55:36 PM    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[allorgloccomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[allorgloccomputers]

    EXEC    ('
        CREATE    VIEW dbo.allorgloccomputers
        AS
        SELECT    dbo.ai_oldef_prop.userid,
            dbo.ai_oldef_prop.orgnm,
            dbo.ai_oldef_prop.office,
            dbo.ai_oldef_prop.hwsource,
            dbo.ai_oldef_prop.region,
            dbo.ai_hwdef_prop.hwname,
            dbo.ai_hwdef_prop.hwmfgr,
            dbo.ai_hwdef_prop.hwproctype,
            dbo.ai_hwdef_prop.hwuuid
        FROM    dbo.ai_hwdef_prop
            INNER JOIN
            dbo.ai_oldef_prop
            ON dbo.ai_hwdef_prop.hwuuid = dbo.ai_oldef_prop.hwuuid
        WHERE     ai_hwdef_prop.hwname != ''''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on allorgloccomputers to aiadmin
    grant delete on allorgloccomputers to aiadmin
    grant select on allorgloccomputers to aiadmin
    grant update on allorgloccomputers to aiadmin
    grant delete on allorgloccomputers to aiadmin
    grant insert on allorgloccomputers to aiadmin
    grant select on allorgloccomputers to aipublic

--    View dbo.Appsbycategory    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[appsbycategory]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[appsbycategory]    
    
    EXEC    ('
        CREATE    VIEW dbo.appsbycategory
        AS
        SELECT    dbo.ai_swdef_prop.*,
            dbo.ai_swcat_prop.scname AS scname,
            dbo.ai_swcat_prop.scnote AS scnote
        FROM    dbo.ai_swdef_prop
            INNER JOIN
            dbo.ai_swcat_swdef_rel
            ON dbo.ai_swdef_prop.swuuid = dbo.ai_swcat_swdef_rel.swuuid
            INNER JOIN
            dbo.ai_swcat_prop
            ON dbo.ai_swcat_swdef_rel.scuuid = dbo.ai_swcat_prop.scuuid
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on appsbycategory to aiadmin
    grant delete on appsbycategory to aiadmin
    grant select on appsbycategory to aiadmin
    grant update on appsbycategory to aiadmin
    grant delete on appsbycategory to aiadmin
    grant insert on appsbycategory to aiadmin
    grant select on appsbycategory to aipublic

--    View dbo.appsetrust    Script Date: 2/28/2005
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[appsetrust]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[appsetrust]

    EXEC    ('
        CREATE    VIEW dbo.appsetrust
        AS
        SELECT    dbo.ai_swdef_prop.swname,
            dbo.ai_swdef_hwdef_rel.hwuuid
        FROM    dbo.ai_swdef_hwdef_rel
            INNER JOIN
            dbo.ai_swdef_prop
            ON dbo.ai_swdef_hwdef_rel.swuuid = dbo.ai_swdef_prop.swuuid
        WHERE    UPPER(dbo.ai_swdef_prop.swname) like ''%ETRUST ANTIVIRUS%''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on appsetrust to aiadmin
    grant delete on appsetrust to aiadmin
    grant select on appsetrust to aiadmin
    grant update on appsetrust to aiadmin
    grant delete on appsetrust to aiadmin
    grant insert on appsetrust to aiadmin
    grant select on appsetrust to aipublic

--    View dbo.hotfixdetailforcomputer    Script Date: 6/19/2004 6:55:36 PM
--    Modified 2004-09-27 to remove reference to 'domainid' and 'unitid' columns, George Curran, Computer Associates
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotfixdetailforcomputer]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[hotfixdetailforcomputer]

    EXEC    ('
        CREATE    VIEW dbo.hotfixdetailforcomputer
        AS
        SELECT    dbo.ai_hfdef_prop.hfpub,
            dbo.ai_hfdef_prop.hfname,
            dbo.ai_hfdef_prop.hfsp,
            dbo.ai_hfdef_prop.hfid,
            dbo.ai_hfdef_prop.hfver,
            dbo.ai_hfdef_prop.hfrel,
            dbo.ai_hfdef_prop.hfmod,
            dbo.ai_hfdef_prop.hfbld,
            dbo.ai_hfdef_hwdef_rel.hwuuid
        FROM    dbo.ai_hfdef_prop
            INNER JOIN
            dbo.ai_hfdef_hwdef_rel
            ON dbo.ai_hfdef_prop.hfuuid = dbo.ai_hfdef_hwdef_rel.hfuuid
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on hotfixdetailforcomputer to aiadmin
    grant delete on hotfixdetailforcomputer to aiadmin
    grant select on hotfixdetailforcomputer to aiadmin
    grant update on hotfixdetailforcomputer to aiadmin
    grant delete on hotfixdetailforcomputer to aiadmin
    grant insert on hotfixdetailforcomputer to aiadmin
    grant select on hotfixdetailforcomputer to aipublic

--    View dbo.hotfixpubnmsphfcomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotfixpubnmsphfcomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[hotfixpubnmsphfcomputers]

    EXEC    ('
        CREATE    VIEW dbo.hotfixpubnmsphfcomputers
        AS
        SELECT    dbo.allcomputers.*,
            dbo.ai_hfdef_hwdef_rel.hfuuid
        FROM    dbo.allcomputers
            RIGHT OUTER JOIN
            dbo.ai_hfdef_hwdef_rel
            ON dbo.allcomputers.hwuuid = dbo.ai_hfdef_hwdef_rel.hwuuid
        WHERE    (dbo.allcomputers.hwname <> '''')
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on hotfixpubnmsphfcomputers to aiadmin
    grant delete on hotfixpubnmsphfcomputers to aiadmin
    grant select on hotfixpubnmsphfcomputers to aiadmin
    grant update on hotfixpubnmsphfcomputers to aiadmin
    grant delete on hotfixpubnmsphfcomputers to aiadmin
    grant insert on hotfixpubnmsphfcomputers to aiadmin
    grant select on hotfixpubnmsphfcomputers to aipublic

--    View dbo.linuxcomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[linuxcomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[linuxcomputers]

    EXEC    ('
        CREATE    VIEW dbo.linuxcomputers
        AS
        SELECT    ospub + '' '' + osname AS osname,
            userid,
            orgnm,
            office,
            hwsource,
            region,
            hwuuid,
            hwdom,
            hwname,
            hwsn,
            hwtype,
            hwmfgr,
            hwmodel,
            hwmem,
            hwuse,
            hwbiosmfgr,
            hwbiosver,
            hwbiosdate,
            hwprocmfgr,
            hwprocmodel,
            hwproctype,
            hwproccount,
            hwprocspeed,
            fssize,
            fsfree,
            ospub,
            osver,
            ossp,
            oslanguage,
            lscandate,
            instdate
        FROM    dbo.allcomputers
        WHERE    (osname LIKE ''%Linux%'')
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on linuxcomputers to aiadmin
    grant delete on linuxcomputers to aiadmin
    grant select on linuxcomputers to aiadmin
    grant update on linuxcomputers to aiadmin
    grant delete on linuxcomputers to aiadmin
    grant insert on linuxcomputers to aiadmin
    grant select on linuxcomputers to aipublic

--    View dbo.locationsum    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[locationsum]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[locationsum]

    EXEC    ('
        CREATE    VIEW dbo.locationsum
        AS
        SELECT    TOP 200 COUNT(region) AS countofregion,
            region
        FROM    dbo.ai_oldef_prop
        GROUP BY region
        ORDER BY COUNT(region) DESC, region
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on locationsum to aiadmin
    grant delete on locationsum to aiadmin
    grant select on locationsum to aiadmin
    grant update on locationsum to aiadmin
    grant delete on locationsum to aiadmin
    grant insert on locationsum to aiadmin
    grant select on locationsum to aipublic

--    View dbo.notscannedlast30days    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[notscannedlast30days]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[notscannedlast30days]

    EXEC    ('
        CREATE    VIEW dbo.notscannedlast30days
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
            INNER JOIN ai_hwdef_lscandate_kpi
            ON allcomputers.hwuuid= ai_hwdef_lscandate_kpi.hwuuid
        WHERE    ( ai_hwdef_lscandate_kpi.lscandate < DATEADD(m, - 1, GETDATE()))
        AND    osname != ''''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on notscannedlast30days to aiadmin
    grant delete on notscannedlast30days to aiadmin
    grant select on notscannedlast30days to aiadmin
    grant update on notscannedlast30days to aiadmin
    grant delete on notscannedlast30days to aiadmin
    grant insert on notscannedlast30days to aiadmin
    grant select on notscannedlast30days to aipublic

--    View dbo.notscannedlast60days    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[notscannedlast60days]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[notscannedlast60days]    
    
    EXEC    ('
        CREATE    VIEW dbo.notscannedlast60days
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
            INNER JOIN ai_hwdef_lscandate_kpi
            ON allcomputers.hwuuid= ai_hwdef_lscandate_kpi.hwuuid
        WHERE    (ai_hwdef_lscandate_kpi.lscandate < DATEADD(m, - 2, GETDATE()))
        AND    osname != ''''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on notscannedlast60days to aiadmin
    grant delete on notscannedlast60days to aiadmin
    grant select on notscannedlast60days to aiadmin
    grant update on notscannedlast60days to aiadmin
    grant delete on notscannedlast60days to aiadmin
    grant insert on notscannedlast60days to aiadmin
    grant select on notscannedlast60days to aipublic

--    View dbo.notscannedlast90days    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[notscannedlast90days]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[notscannedlast90days]    
    
    EXEC    ('
        CREATE    VIEW dbo.notscannedlast90days
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
            INNER JOIN ai_hwdef_lscandate_kpi
        ON    allcomputers.hwuuid= ai_hwdef_lscandate_kpi.hwuuid
        WHERE    (ai_hwdef_lscandate_kpi.lscandate < DATEADD(m, - 3, GETDATE()))
        AND    osname != ''''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on notscannedlast90days to aiadmin
    grant delete on notscannedlast90days to aiadmin
    grant select on notscannedlast90days to aiadmin
    grant update on notscannedlast90days to aiadmin
    grant delete on notscannedlast90days to aiadmin
    grant insert on notscannedlast90days to aiadmin
    grant select on notscannedlast90days to aipublic

--    View dbo.osbuiltlast30days    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[osbuiltlast30days]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[osbuiltlast30days]

    EXEC    ('
        CREATE VIEW dbo.osbuiltlast30days
        AS
        SELECT     dbo.allcomputers.*
        FROM         dbo.allcomputers
        WHERE     (instdate > DATEADD(m, - 1, GETDATE()))
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on osbuiltlast30days to aiadmin
    grant delete on osbuiltlast30days to aiadmin
    grant select on osbuiltlast30days to aiadmin
    grant update on osbuiltlast30days to aiadmin
    grant delete on osbuiltlast30days to aiadmin
    grant insert on osbuiltlast30days to aiadmin
    grant select on osbuiltlast30days to aipublic

--    View dbo.osbuiltlast60days    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[osbuiltlast60days]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[osbuiltlast60days]

    EXEC    ('
        CREATE VIEW dbo.osbuiltlast60days
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
        WHERE    (instdate > DATEADD(m, - 2, GETDATE()))
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on osbuiltlast60days to aiadmin
    grant delete on osbuiltlast60days to aiadmin
    grant select on osbuiltlast60days to aiadmin
    grant update on osbuiltlast60days to aiadmin
    grant delete on osbuiltlast60days to aiadmin
    grant insert on osbuiltlast60days to aiadmin
    grant select on osbuiltlast60days to aipublic

--    View dbo.osbuiltlast90days    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[osbuiltlast90days]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[osbuiltlast90days]

    EXEC    ('
        CREATE VIEW dbo.osbuiltlast90days
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
        WHERE    (instdate > DATEADD(m, - 3, GETDATE()))
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on osbuiltlast90days to aiadmin
    grant delete on osbuiltlast90days to aiadmin
    grant select on osbuiltlast90days to aiadmin
    grant update on osbuiltlast90days to aiadmin
    grant delete on osbuiltlast90days to aiadmin
    grant insert on osbuiltlast90days to aiadmin
    grant select on osbuiltlast90days to aipublic

--    View dbo.osbuiltthisyear    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[osbuiltthisyear]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[osbuiltthisyear]

    EXEC    ('
        CREATE VIEW dbo.osbuiltthisyear
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
        WHERE    (instdate > DATEADD(yyyy, - 1, GETDATE()))
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on osbuiltthisyear to aiadmin
    grant delete on osbuiltthisyear to aiadmin
    grant select on osbuiltthisyear to aiadmin
    grant update on osbuiltthisyear to aiadmin
    grant delete on osbuiltthisyear to aiadmin
    grant insert on osbuiltthisyear to aiadmin
    grant select on osbuiltthisyear to aipublic

--    View dbo.osunsupported    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[osunsupported]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[osunsupported]

    EXEC    ('
        CREATE    VIEW dbo.osunsupported
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
        WHERE    (osname LIKE ''Windows Me'')
        OR    (osname LIKE ''Windows 9%'')
        OR    (osname LIKE ''Windows NT%'')
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on osunsupported to aiadmin
    grant delete on osunsupported to aiadmin
    grant select on osunsupported to aiadmin
    grant update on osunsupported to aiadmin
    grant delete on osunsupported to aiadmin
    grant insert on osunsupported to aiadmin
    grant select on osunsupported to aipublic

--    View dbo.pmftoorg    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pmftoorg]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[pmftoorg]

    EXEC    ('
        CREATE    VIEW dbo.pmftoorg
        AS
        SELECT    dbo.ai_usrdef_orgtree_prop.pmf,
            ud_orgdef_list.olorgname,
            ud_orgdef_list.olnote,
            ud_orgdef_list.oldiv
        FROM    dbo.ai_usrdef_orgtree_prop
            INNER JOIN dbo.ud_orgdef_list
            ON dbo.ai_usrdef_orgtree_prop.reportsto LIKE ''%'' + dbo.ud_orgdef_list.oluserlist + ''%''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on pmftoorg to aiadmin
    grant delete on pmftoorg to aiadmin
    grant select on pmftoorg to aiadmin
    grant update on pmftoorg to aiadmin
    grant delete on pmftoorg to aiadmin
    grant insert on pmftoorg to aiadmin
    grant select on pmftoorg to aipublic

--    View dbo.pubappca    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubappca]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[pubappca]

    EXEC    ('
        CREATE  VIEW dbo.pubappca
        AS
        SELECT    swuuid
        FROM    ai_swdef_prop
            INNER JOIN ai_pubdef_prop
        ON    ai_swdef_prop.pubuuid = ai_pubdef_prop.pubuuid
        WHERE    pubname = ''Computer Associates International''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on pubappca to aiadmin
    grant delete on pubappca to aiadmin
    grant select on pubappca to aiadmin
    grant update on pubappca to aiadmin
    grant delete on pubappca to aiadmin
    grant insert on pubappca to aiadmin
    grant select on pubappca to aipublic

--    View dbo.pubappcamachines    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubappcamachines]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[pubappcamachines]

    EXEC    ('
        CREATE VIEW  dbo.pubappcamachines
        AS
        SELECT    DISTINCT hwuuid
        FROM    ai_swdef_hwdef_rel
            INNER JOIN pubappca
        ON    ai_swdef_hwdef_rel.swuuid = pubappca.swuuid
        GROUP
        BY    ai_swdef_hwdef_rel.hwuuid
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on pubappcamachines to aiadmin
    grant delete on pubappcamachines to aiadmin
    grant select on pubappcamachines to aiadmin
    grant update on pubappcamachines to aiadmin
    grant delete on pubappcamachines to aiadmin
    grant insert on pubappcamachines to aiadmin
    grant select on pubappcamachines to aipublic

--    View dbo.pubappcacomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubappcacomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[pubappcacomputers]

    EXEC    ('
        CREATE VIEW  dbo.pubappcacomputers
        AS
        SELECT    dbo.ai_swdef_hwdef_rel.swuuid,
            dbo.ai_swdef_prop.swname, swver, swrel, swmod, swbld, swnote,
            dbo.allcomputers.*,
            dbo.ai_pubdef_prop.pubname as swpub
        FROM    dbo.ai_swdef_hwdef_rel
            INNER JOIN dbo.allcomputers
            ON dbo.ai_swdef_hwdef_rel.hwuuid = dbo.allcomputers.hwuuid
            INNER JOIN dbo.ai_swdef_prop
            ON dbo.ai_swdef_hwdef_rel.swuuid = dbo.ai_swdef_prop.swuuid
            INNER JOIN dbo.ai_pubdef_prop
            ON  dbo.ai_swdef_prop.pubuuid = dbo.ai_pubdef_prop.pubuuid
            AND (pubname=''Computer Associates International, Inc.'' or pubname=''Computer Associates International'' or pubname=''Computer Associates'' or pubname=''CA'' or pubname=''ca'')
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on pubappcacomputers to aiadmin
    grant delete on pubappcacomputers to aiadmin
    grant select on pubappcacomputers to aiadmin
    grant update on pubappcacomputers to aiadmin
    grant delete on pubappcacomputers to aiadmin
    grant insert on pubappcacomputers to aiadmin
    grant select on pubappcacomputers to aipublic

--    View dbo.pubappcomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubappcomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[pubappcomputers]

    EXEC    ('
        CREATE VIEW dbo.pubappcomputers
        AS
        SELECT    dbo.ai_swdef_hwdef_rel.swuuid,
            dbo.ai_swdef_prop.swname, swver, swrel, swmod, swbld, swnote,
            dbo.allcomputers.*,
            dbo.ai_pubdef_prop.pubname as swpub
        FROM    dbo.ai_swdef_hwdef_rel
            INNER JOIN dbo.ai_swdef_prop
            ON dbo.ai_swdef_hwdef_rel.swuuid = dbo.ai_swdef_prop.swuuid
            INNER JOIN dbo.allcomputers
            ON dbo.ai_swdef_hwdef_rel.hwuuid = dbo.allcomputers.hwuuid
            INNER JOIN dbo.ai_pubdef_prop
            ON dbo.ai_swdef_prop.pubuuid = dbo.ai_pubdef_prop.pubuuid
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on pubappcomputers to aiadmin
    grant delete on pubappcomputers to aiadmin
    grant select on pubappcomputers to aiadmin
    grant update on pubappcomputers to aiadmin
    grant delete on pubappcomputers to aiadmin
    grant insert on pubappcomputers to aiadmin
    grant select on pubappcomputers to aipublic

--    View dbo.pubapplaptops    Script Date: 9/18/2004
--    Modified 2004-09-27 George Curran, Computer Associates
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubapplaptops]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[pubapplaptops]

    EXEC    ('
        CREATE VIEW dbo.pubapplaptops
        AS
        SELECT    dbo.pubappcomputers.*
        FROM    dbo.pubappcomputers
        WHERE hwuse=''Laptop''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on pubapplaptops to aiadmin
    grant delete on pubapplaptops to aiadmin
    grant select on pubapplaptops to aiadmin
    grant update on pubapplaptops to aiadmin
    grant delete on pubapplaptops to aiadmin
    grant insert on pubapplaptops to aiadmin
    grant select on pubapplaptops to aipublic

--    View dbo.pubappdesktops    Script Date: 9/18/2004
--    Modified 2004-09-27 George Curran, Coomputer Associates
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubappdesktops]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[pubappdesktops]

    EXEC    ('
        CREATE VIEW dbo.pubappdesktops
        AS
        SELECT    dbo.pubappcomputers.*
        FROM    dbo.pubappcomputers
        WHERE hwuse=''Desktop''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on pubappdesktops to aiadmin
    grant delete on pubappdesktops to aiadmin
    grant select on pubappdesktops to aiadmin
    grant update on pubappdesktops to aiadmin
    grant delete on pubappdesktops to aiadmin
    grant insert on pubappdesktops to aiadmin
    grant select on pubappdesktops to aipublic

--    View dbo.pubappservers    Script Date: 9/18/2004
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pubappservers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[pubappservers]

    EXEC    ('
        CREATE VIEW dbo.pubappservers
        AS
        SELECT    dbo.pubappcomputers.*
        FROM    dbo.pubappcomputers
        WHERE hwuse=''Server''
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on pubappservers to aiadmin
    grant delete on pubappservers to aiadmin
    grant select on pubappservers to aiadmin
    grant update on pubappservers to aiadmin
    grant delete on pubappservers to aiadmin
    grant insert on pubappservers to aiadmin
    grant select on pubappservers to aipublic

    
--    View dbo.storagebot50freespace    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[storagebot50freespace]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[storagebot50freespace]

    EXEC    ('
        CREATE    VIEW dbo.storagebot50freespace
        AS
        SELECT    TOP 50
            dbo.servercomputers.*
            FROM dbo.servercomputers
        WHERE    servercomputers.fsfree > 0
        ORDER
        BY    servercomputers.fsfree
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on storagebot50freespace to aiadmin
    grant delete on storagebot50freespace to aiadmin
    grant select on storagebot50freespace to aiadmin
    grant update on storagebot50freespace to aiadmin
    grant delete on storagebot50freespace to aiadmin
    grant insert on storagebot50freespace to aiadmin
    grant select on storagebot50freespace to aipublic

--    View dbo.storagecomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[storagecomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[storagecomputers]

    EXEC    ('
        CREATE    VIEW dbo.storagecomputers
        AS
        SELECT    dbo.ai_fsdef_prop.fsname,
            dbo.allcomputers.*
        FROM    dbo.ai_fsdef_prop
            INNER JOIN
            dbo.allcomputers
            ON dbo.ai_fsdef_prop.hwuuid = dbo.allcomputers.hwuuid
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on storagecomputers to aiadmin
    grant delete on storagecomputers to aiadmin
    grant select on storagecomputers to aiadmin
    grant update on storagecomputers to aiadmin
    grant delete on storagecomputers to aiadmin
    grant insert on storagecomputers to aiadmin
    grant select on storagecomputers to aipublic

--    View dbo.storagedesktopcomputers    Script Date: 6/19/2004 6:55:36 PM
--    Modified 2004-09-27 to derive from existing views, George Curran, Computer Associates
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[storagedesktopcomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[storagedesktopcomputers]
    EXEC    ('
        CREATE    VIEW dbo.storagedesktopcomputers
        AS
        SELECT    dbo.storagecomputers.*
        FROM        dbo.storagecomputers
                INNER JOIN
                dbo.desktopcomputers
                ON    dbo.storagecomputers.hwuuid = dbo.desktopcomputers.hwuuid
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on storagedesktopcomputers to aiadmin
    grant delete on storagedesktopcomputers to aiadmin
    grant select on storagedesktopcomputers to aiadmin
    grant update on storagedesktopcomputers to aiadmin
    grant delete on storagedesktopcomputers to aiadmin
    grant insert on storagedesktopcomputers to aiadmin
    grant select on storagedesktopcomputers to aipublic

--    View dbo.storagefssum    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[storagefssum]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[storagefssum]

    EXEC    ('
        CREATE    VIEW dbo.storagefssum
        AS
        SELECT    TOP 200
            COUNT(dbo.ai_fsdef_prop.fsname) AS countoffilesystem,
            dbo.ai_fsdef_prop.fsname,
            SUM(dbo.ai_fsdef_prop.fssize) AS sumofdrivesize
        FROM    dbo.ai_fsdef_prop
        GROUP
        BY    dbo.ai_fsdef_prop.fsname
        HAVING    (((dbo.ai_fsdef_prop.fsname)<>''''))
        ORDER
        BY    sumofdrivesize DESC
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on storagefssum to aiadmin
    grant delete on storagefssum to aiadmin
    grant select on storagefssum to aiadmin
    grant update on storagefssum to aiadmin
    grant delete on storagefssum to aiadmin
    grant insert on storagefssum to aiadmin
    grant select on storagefssum to aipublic

--    View dbo.StorageFileSystemSum    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[storagefilesystemsum]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[storagefilesystemsum]

    EXEC    ('
        CREATE    VIEW dbo.storagefilesystemsum
        AS
        SELECT    TOP 200
            COUNT(fsname) AS countoffilesystem,
            fsname,
            SUM(fssize) AS sumofdrivesize
        FROM    dbo.ai_fsdef_prop
        GROUP
        BY    fsname
        HAVING    (fsname <> ''N/A'')
        ORDER
        BY    SUM(fssize) DESC
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on storagefilesystemsum to aiadmin
    grant delete on storagefilesystemsum to aiadmin
    grant select on storagefilesystemsum to aiadmin
    grant update on storagefilesystemsum to aiadmin
    grant delete on storagefilesystemsum to aiadmin
    grant insert on storagefilesystemsum to aiadmin
    grant select on storagefilesystemsum to aipublic

--    View dbo.storagetopfreespace    Script Date: 11/15/2004
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[storagetopfreespace]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[storagetopfreespace]

    EXEC    ('
        CREATE    VIEW dbo.storagetopfreespace
        AS
        SELECT    TOP 500 dbo.servercomputers.*
        FROM    dbo.servercomputers
        ORDER
        BY    servercomputers.fsfree DESC
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on storagetopfreespace to aiadmin
    grant delete on storagetopfreespace to aiadmin
    grant select on storagetopfreespace to aiadmin
    grant update on storagetopfreespace to aiadmin
    grant delete on storagetopfreespace to aiadmin
    grant insert on storagetopfreespace to aiadmin
    grant select on storagetopfreespace to aipublic

--    View dbo.storagetop50freespace    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[storagetop50freespace]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[storagetop50freespace]

    EXEC    ('
        CREATE    VIEW dbo.storagetop50freespace
        AS
        SELECT    TOP 50
            dbo.servercomputers.*
        FROM    servercomputers
        ORDER
        BY    servercomputers.fsfree DESC
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on storagetop50freespace to aiadmin
    grant delete on storagetop50freespace to aiadmin
    grant select on storagetop50freespace to aiadmin
    grant update on storagetop50freespace to aiadmin
    grant delete on storagetop50freespace to aiadmin
    grant insert on storagetop50freespace to aiadmin
    grant select on storagetop50freespace to aipublic

--    View dbo.unixcomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[unixcomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[unixcomputers]

    EXEC    ('
        CREATE    VIEW dbo.unixcomputers
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
        WHERE    (osname NOT LIKE ''%Windows%'')
        AND    (osname NOT LIKE ''%Linux%'')
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on unixcomputers to aiadmin
    grant delete on unixcomputers to aiadmin
    grant select on unixcomputers to aiadmin
    grant update on unixcomputers to aiadmin
    grant delete on unixcomputers to aiadmin
    grant insert on unixcomputers to aiadmin
    grant select on unixcomputers to aipublic

--    View dbo.userdirectscomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[userdirectscomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[userdirectscomputers]

    EXEC    ('
        CREATE    VIEW dbo.userdirectscomputers
        AS
        SELECT    dbo.ai_usrdef_prop.samid as pmf,
            dbo.ai_usrdef_prop.mgr as reportsto,
            dbo.allcomputers.*
        FROM    dbo.ai_usrdef_prop
            INNER JOIN
            dbo.allcomputers
            ON dbo.ai_usrdef_prop.samid = dbo.allcomputers.userid
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on userdirectscomputers to aiadmin
    grant delete on userdirectscomputers to aiadmin
    grant select on userdirectscomputers to aiadmin
    grant update on userdirectscomputers to aiadmin
    grant delete on userdirectscomputers to aiadmin
    grant insert on userdirectscomputers to aiadmin
    grant select on userdirectscomputers to aipublic

--    View dbo.userhasreports    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[userhasreports]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[userhasreports]

    EXEC    ('
        CREATE    VIEW dbo.userhasreports
        AS
        SELECT    ai_usrdef_prop.samid as PMF,
            ai_usrdef_prop.mgr as reportsto
        FROM    ai_usrdef_prop
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on userhasreports to aiadmin
    grant delete on userhasreports to aiadmin
    grant select on userhasreports to aiadmin
    grant update on userhasreports to aiadmin
    grant delete on userhasreports to aiadmin
    grant insert on userhasreports to aiadmin
    grant select on userhasreports to aipublic

--    View dbo.vmwarecomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vmwarecomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[vmwarecomputers]

    EXEC    ('
        CREATE    VIEW dbo.vmwarecomputers
        AS
        SELECT    dbo.allcomputers.*
        FROM    dbo.allcomputers
        WHERE    (hwmfgr LIKE ''%VMware%'')
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on vmwarecomputers to aiadmin
    grant delete on vmwarecomputers to aiadmin
    grant select on vmwarecomputers to aiadmin
    grant update on vmwarecomputers to aiadmin
    grant delete on vmwarecomputers to aiadmin
    grant insert on vmwarecomputers to aiadmin
    grant select on vmwarecomputers to aipublic

--    View dbo.windowscomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[windowscomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[windowscomputers]

    EXEC    ('
        CREATE    VIEW dbo.windowscomputers
        AS
        SELECT    dbo.allcomputers.*
        FROM     dbo.allcomputers
        WHERE    (osname LIKE ''Windows%'')
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on windowscomputers to aiadmin
    grant delete on windowscomputers to aiadmin
    grant select on windowscomputers to aiadmin
    grant update on windowscomputers to aiadmin
    grant delete on windowscomputers to aiadmin
    grant insert on windowscomputers to aiadmin
    grant select on windowscomputers to aipublic


--    View dbo.userreportscomputers    Script Date: 6/19/2004 6:55:36 PM
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[userreportscomputers]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[userreportscomputers]

    EXEC    ('
        CREATE    VIEW dbo.userreportscomputers
        AS
        SELECT    dbo.ai_usrdef_orgtree_prop.reportsto,
            dbo.ai_usrdef_orgtree_prop.pmf,
            dbo.allcomputers.*
        FROM    dbo.ai_usrdef_orgtree_prop
            INNER JOIN
            dbo.allcomputers
            ON dbo.ai_usrdef_orgtree_prop.pmf = dbo.allcomputers.userid
        ')
    
    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on userreportscomputers to aiadmin
    grant delete on userreportscomputers to aiadmin
    grant select on userreportscomputers to aiadmin
    grant update on userreportscomputers to aiadmin
    grant delete on userreportscomputers to aiadmin
    grant insert on userreportscomputers to aiadmin
    grant select on userreportscomputers to aipublic

--    View dbo.appsnotetrust    Script Date: 02/28/2005
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[appsnotetrust]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[appsnotetrust]

    EXEC    ('
        CREATE    VIEW dbo.appsnotetrust
        AS
        SELECT    allcomputers.*
        FROM    dbo.allcomputers
        WHERE    hwuuid NOT IN
            (SELECT    hwuuid
            FROM    ai_swdef_hwdef_rel
            WHERE    swuuid IN
                (SELECT    swuuid
                FROM    ai_swdef_prop
                WHERE    UPPER(swname) LIKE ''%ETRUST ANTIVIRUS%''))
        ')

    IF    @@ERROR > 0 SET @intReturn = 1

--    Grant permissions to view    
    grant insert on appsnotetrust to aiadmin
    grant delete on appsnotetrust to aiadmin
    grant select on appsnotetrust to aiadmin
    grant update on appsnotetrust to aiadmin
    grant delete on appsnotetrust to aiadmin
    grant insert on appsnotetrust to aiadmin
    grant select on appsnotetrust to aipublic

--    Exit with return code
    RETURN(@intReturn)


GO