Create view SWV_SWUsage as
select hw.label as host, proddef.name as Product, reldef.name as release, his.acid, dateadd(ss,his.sttotal, '01-01-1970 00:00:00') as started, dateadd(ss,his.endtotal, '01-01-1970 00:00:00') as ended, his.sttotal, his.endtotal, (endtotal-sttotal) as duration, proddef.sw_def_uuid as prod_uuid, reldef.sw_def_uuid as rel_uuid, hw.dis_hw_uuid as host_uuid
from infohis as his
Inner join ca_discovered_hardware as hw on his.object_uuid = hw.dis_hw_uuid
inner join ca_link_sw_def as swlink on his.sw_def_uuid=swlink.secondary_sw_def_uuid
inner join ca_software_def as reldef on his.sw_def_uuid = reldef.sw_def_uuid
inner join ca_software_def as proddef on swlink.primary_sw_def_uuid = proddef.sw_def_uuid and proddef.software_type_id=8
GO
[dbo].[SWV_SWUsage]