CREATE VIEW [dbo].[al_bi_agent_disc_view]
  AS  
  SELECT dh.dis_hw_uuid,dbo.al_bi_bintohexstr(dh.dis_hw_uuid) as dis_hw_uuid_display,
       dh.tenant_id as tenant,
       dh.host_name,
       dh.serial_number,
       dh.primary_mac_address,
       dh.asset_tag,
       dbo.secs2date(dh.creation_date)as creation_date,
       dh.vendor_name as system_vendor,
       ag.user_def1 as login_id,
       ag.user_def3 as location,
       ag.user_def4 as external_host_key,
       dbo.secs2date(ag.last_run_date) as last_scan_date,
       ap.genBatchID as batch_id,
       ap.genModel as system_model,
       ap.genType as system_type,
       ap.genTotMemory as total_memory,
       case when ap.genTotMemory >=0 and ap.genTotMemory < 1073741824
then  round(ap.genTotMemory/1048576,2)
when ap.genTotMemory >=1073741824 and ap.genTotMemory < 1099511627776
then round(ap.genTotMemory/1073741824,2)
when ap.genTotMemory >= 1099511627776
then round(ap.genTotMemory/1099511627776,2)

end as total_memory_display,

case when ap.genTotMemory >=0 and ap.genTotMemory < 1073741824
then 'MB'
when ap.genTotMemory >=1073741824 and ap.genTotMemory < 1099511627776
then 'GB'
when ap.genTotMemory >= 1099511627776
then 'TB'
end as total_memory_display_unit,
       ap.genTotDisk as total_disk_space,
       case when ap.genTotDisk >=0 and ap.genTotDisk < 1073741824
then  round(ap.genTotDisk/1048576,2)
when ap.genTotDisk >=1073741824 and ap.genTotDisk < 1099511627776
then round(ap.genTotDisk/1073741824,2)
when ap.genTotDisk >= 1099511627776
then round(ap.genTotDisk/1099511627776,2)
end as total_disk_space_display,

case when ap.genTotDisk >=0 and ap.genTotDisk < 1073741824
then 'MB'
when ap.genTotDisk >=1073741824 and ap.genTotDisk < 1099511627776
then 'GB'
when ap.genTotDisk >= 1099511627776
then 'TB'
end as total_disk_space_display_unit,
       ap.genNetIP as ip_address,
       ap.genOS as operating_system,
       ap.genNumProcs as processor_count,
       ap.genProcType as processor_type,
       ap.genProcSpeed as processor_speed,
       case when ap.genProcSpeed >=0 and ap.genProcSpeed < 1000
then round(cast(ap.genProcSpeed as float),2)
when ap.genProcSpeed >=1000 and ap.genProcSpeed < 1000000
then round(cast(ap.genProcSpeed as float)/1000,2)
when ap.genProcSpeed >= 1000000
then round(cast(ap.genProcSpeed as float)/1000000,2)
end as proc_speed_display,

case when ap.genProcSpeed >=0 and ap.genProcSpeed < 1000
then 'MHz'
when ap.genProcSpeed >=1000 and ap.genProcSpeed < 1000000
then 'GHz'
when ap.genProcSpeed >= 1000000
then 'THz'
end as proc_speed_display_unit,
       ap.ndMAC as mac_address,
       ap.ndIP as network_ip_address,
       ap.ndSwName as switch_name,
       ap.ndPortName as port_name,
       ap.ndPortType as port_type,
       ap.ndPortDesc as port_description,
       ap.ndSwIP as switch_ip_address,
       ap.ndSwLoc as switch_location,
       ap.ndSwVendor as vendor,
       ap.ndSwSite as site,
       ap.ndSwCust as customer_name,
       ap.ndSwFn as disc_function,
       ap.ndSwScDate as scan_date,
       cast(ap.ndSwScDate as datetime) as Network_Disc_Last_Scan_Date
FROM ca_discovered_hardware dh
INNER JOIN ca_agent ag
    ON ag.object_uuid = dh.dis_hw_uuid
INNER JOIN ca_agent_prop ap
    ON ap.object_uuid = dh.dis_hw_uuid
GO