On using Cloud Control as a cmdb?

In an earlier post i described how you could easily set a host property in cloud control using emcli http://dbaharrison.blogspot.de/2014/01/labelling-targets-in-cloud-control-easy.html

Very clever you might think - but what's the point of that (well other than this label appearing in the alert emails - which in itself is useful)?

Well for one thing it's very useful for configuration/inventory management of your enterprise, once this host property is set it can be referred to in reports (and I'm still using the deprecated 'information publisher' reports here) - i still had trouble getting bi publisher to install OK - and even the screenshots i've seen where it is working seem about 10x more complex that the basic reports you get with information publisher.

Anyway here is an example of a simple report that can be created (I've now amended the labelling method in the above post to split the environments into the 4 main categories we use DEV/INT/UAT/Live - which is pretty much what most organisations use - at least in my expereince).

This SQL can be run (and it could be in any tool you like) to give a nice summary of your databases:

with envtype as (select tgt.target_name,tp.property_value from sysman.GC$TARGET_PROPERTIES tp,sysman.gc$target tgt
where property_name='orcl_gtp_line_of_bus'
and tp.target_guid=tgt.target_guid
and tgt.target_type='host'),
db_size as (select target_guid,sum(file_size) as dbsz from MGMT$DB_DATAFILES t group by target_guid)
select envtype.property_value as "environment",d.target_name,d.host_name,d.characterset,d.startup_time,d.release,d.dbversion,d.is_64bit
,db_size.dbsz/(1024*1024*1024) as "Database Size",os.os_summary,os.freq,os.mem,os.disk,os.cpu_count
from MGMT$DB_DBNINSTANCEINFO d,MGMT$OS_HW_SUMMARY os,envtype,db_size
where os.host_name=d.host_name
and os.host_name=envtype.target_name
and db_size.target_guid=d.target_guid

The 'envtype' subquery defined in the initial with clause returns the property that was set by the labelling earlier on. In the final report using information publisher this creates output similar to the below

This gives a really nice summary of what is out there, the link at the top right of the report also allows you to open in directly in Excel

Taking this a stage further, one of the other things we want to do is map database to applications, so any reporting we do can also be based on application.

After looking in to various ways of doing this the simplest way seems to be to define a 'container'for each application  and then map the databases to that group.

There seemed to be two ways of doing this, either using 'groups' or 'systems' - there really didn't seem to be too much difference between the two of these options and i ended up using generic systems to do this. You can either create all of these by hand or if you have a list elsewhere these can be imported in using emcli techiques (similar to how i did the labelling earlier)

Once these generic systems are created you can map the databases running the application to the 'generic system' of the same name (esentially just add the databases for that application to that application group). Once all this mapping is done (and there is no real way round quite a bit of manual effort here) well unless you already have a cmdb listing the databases against applications - in which case then again you could build emcli command lines to do all of it for you.

Anyway once it's all imported you can then report against this additional information using the query below (which also includes the join to the 'environment type' data already uploaded.



with envtype as (select tgt.target_name,tp.property_value from sysman.GC$TARGET_PROPERTIES tp,sysman.gc$target tgt
where property_name='orcl_gtp_line_of_bus'
and tp.target_guid=tgt.target_guid
and tgt.target_type='host')
select aggregate_target_name as "application" ,envtype.property_value as "environment",d.target_name,d.host_name,characterset,startup_time,release,dbversion,is_64bit
,os.os_summary,os.freq,os.mem,os.disk,os.cpu_count
from MGMT$TARGET_MEMBERS t,MGMT$DB_DBNINSTANCEINFO d,MGMT$OS_HW_SUMMARY os,envtype
where t.aggregate_target_type='generic_system'
and member_target_type='oracle_database'
and t.member_target_guid=d.target_guid
and os.host_name=d.host_name
and os.host_name=envtype.target_name

Now when i create an info publisher report with this SQL i get a nice summary of database/application/environment and even some host details

You could expand the report to include anything in the cloud control repository (which is a lot....).

I'm not sure cloud is really designed as a CMDB but the framework is there to use it as one if you so wish......