Hardware comparison reports

select * from (
select
c1.ORACLE_HOME as label,
c1.PRODUCT_VERSION as value,
1 as order_number
from
common_elements e1
join common_ora_home c1
on c1.collection_id = e1.collection_id
where
e1.collector_id = 3
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
common_ora_home p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
ande2.clarity_manager_id = '__MGR_ID__'
and rownum = 1
)
union all
select
'CPU Name' as label,
i1.CPU_NAME as value,
2 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
union all
select
'CPU Count (Logical)' as label,
to_char(i1.CPU_COUNT_LOGICAL) as value,
3 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
union all
select
'CPU Count (Physical)' as label,
to_char(i1.CPU_COUNT_PHYSICAL) as value,
4 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
union all
select
'CPU Architecture' as label,
to_char(i1.CPU_ARCH) as value,
4 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
union all
select
'CPU Speed (Mhz)' as label,
to_char(i1.CPU_SPEED_MHZ) as value,
4 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
union all
select
'OS Name' as label,
to_char(i1.OS_NAME) as value,
5 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
union all
select
'OS Type' as label,
to_char(i1.OS_TYPE) as value,
6 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
union all
select
'OS Version' as label,
to_char(i1.OS_VERSION_MAJOR) as value,
7 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
ande1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
union all
select
'Kernel Version' as label,
to_char(i1.OS_VERSION_KERNEL) as value,
8 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
union all
select
'Swap Space (MB)' as label,
to_char(i1.SWAP_TOTAL) as value,
9 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
union all
select
'Memory (MB)' as label,
to_char(i1.MEM_TOTAL) as value,
10 as order_number
from
common_elements e1
join host i1
on e1.collection_id = i1.collection_id
where
e1.collector_id = 4
and
e1.clarity_node_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_node_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
)
order by order_number