2015-11-13 03:23 AM - edited 2015-11-13 03:24 AM
Hello to all,
The idea or challange I have is follwoing:
Based on a custom annotation I would like have a totals grouped by the tiers defined in OCI.
In the inventory-datamart I have the volumes and the volume annotations, but not the tiers. The tiers I can find at the storage arrays, but how to combine both?
Also I was looking into the tables, but did not figure out a reasonable way to create a SQL for this purpose.
Is someone around here that had a similar issue and found a neat solutuion for it?
Solved! SEE THE SOLUTION
2015-11-13 05:11 AM
Yes the custom annotations are published for the hosts and the volumes.
The Tier-information on the storage array is not a customfield. I would like to minimize the customations as much as possible.
2015-11-13 08:14 AM
If you join to dwh_capacity.storage_dimension, there is a tier field available.
If you want to stay within dwh_inventory, do something like this to get your tier annotation:
select s.id, av.valueIdentifier
from dwh_inventory.storage s
join dwh_inventory.object_to_annotation o2a on o2a.objectId=s.id
join dwh_inventory.annotation_value av on av.id = o2a.annotationValueId and av.annotationType='Tier'
Note that this will carefully select the tier at the storage array level. If you have multiple tiers on a single storage array, or if you're using service levels, you will want to do this differently.
2015-11-16 12:39 AM
Thank you on this!
I need also the connection to the volumes table, because there I have the custom annotation and the data I needed.
I don't get the join to the storages table a way, that would realy help me.
And as I stated, I don't want to pollute the annotations with to many custom types, because I need also to maintain them once in a while and check them after upgrades.
2015-11-16 09:17 AM
If you're not already using it, you will want to take a look at the Database Schema page which is within the Documentation interface on the DWH web portal. I usually have at least one eye on this when working in MySQL.
If you're in dwh_capacity, you can probably use something like this to combine storage with volume:
select * from storage_dimension
join volume_dimension on volume_dimension.storageIdentifier=storage_dimension.identifier and storage_dimension.latest=1;
If your annotation is published, it will be a field in the volume table.
If working in dwh_inventory, the join is simpler:
select * from dwh_inventory.storage
join dwh_inventory.volume on volume.storageId=storage.id;
But you'll have to join in the annotation_value and object_to_annotaion tables as in the earlier example with tiers.
2015-11-19 05:07 AM
Thanks for the helpfull hints, with those and a bit fiddling around I was able to build a query that is matching the results I get with looking into the OCI Java Client.