Active IQ Unified Manager Discussions

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

cheers Ingo
1 ACCEPTED SOLUTION

moechnig

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. 

View solution in original post

6 REPLIES 6

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

cheers 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

cheers Ingo

moechnig

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. 

View solution in original post

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

cheers Ingo
Announcements
NetApp on Discord Image

We're on Discord, are you?

Live Chat, Watch Parties, and More!

Explore Banner

Meet Explore, NetApp’s digital sales platform

Engage digitally throughout the sales process, from product discovery to configuration, and handle all your post-purchase needs.

NetApp Insights to Action
I2A Banner
Public