topic Re: Question: Combining Tier information with custom annotations on Volumes in Active IQ Unified Manager Discussions

Question: Combining Tier information with custom annotations on Volumes

INGO_REPINZ — Wed, 04 Jun 2025 22:46:48 GMT

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

Re: Question: Combining Tier information with custom annotations on Volumes

ostiguy — Fri, 13 Nov 2015 12:40:36 GMT

Hey Ingo,

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

Re: Question: Combining Tier information with custom annotations on Volumes

INGO_REPINZ — Fri, 13 Nov 2015 13:11:16 GMT

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

Re: Question: Combining Tier information with custom annotations on Volumes

moechnig — Fri, 13 Nov 2015 16:14:47 GMT

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.

Re: Question: Combining Tier information with custom annotations on Volumes

INGO_REPINZ — Mon, 16 Nov 2015 08:39:11 GMT

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

Re: Question: Combining Tier information with custom annotations on Volumes

moechnig — Mon, 16 Nov 2015 17:17:50 GMT

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.

Re: Question: Combining Tier information with custom annotations on Volumes

INGO_REPINZ — Thu, 19 Nov 2015 13:07:26 GMT

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