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 != ''
            
GO
GRANT SELECT ON  [dbo].[allcomputers] TO [aiadmin]
GRANT INSERT ON  [dbo].[allcomputers] TO [aiadmin]
GRANT DELETE ON  [dbo].[allcomputers] TO [aiadmin]
GRANT UPDATE ON  [dbo].[allcomputers] TO [aiadmin]
GRANT SELECT ON  [dbo].[allcomputers] TO [aipublic]
GO