Question: Combining Tier information with custom annotations on Volumes

[INGO_REPINZ]

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?

Thanks

Ingo

[ostiguy]

Hey Ingo,

Have you taken any steps to publish those custom annotations in the DWH?

[INGO_REPINZ]

Hello,

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.

Thanks Ingo

[moechnig]

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. 

[INGO_REPINZ]

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.

Best regards

Ingo

[moechnig · Accepted solution]

Hi Ingo,

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. 

[INGO_REPINZ]

Hello,

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.

Best regards

Ingo