[OCI-DWH][MySQL] Finding Where Annotations Are Applied

IMPORTANT NOTE: This only applies to annotations that are published in the OCI-DWH. There may well be annotations used extensively only in the OCI Front-End.

The two most useful tables in the NetApp OnCommand Insight DataWarehouse, with regards to getting knowledge about how annotations are used, is:

• dwh_inventory.annotation_value
• dwh_inventory.object_to_annotation

A simple -

select distinct annotationType from dwh_inventory.annotation_value;

- will list out all the distinct annotations in the environment (annotation types.)

A simple -

select distinct objectType from dwh_inventory.object_to_annotation;

- will list out all the distinct objects to which annotations are applied.

If we want to find out, for a specific annotation (called DEMO below), where it is used, we have to consider all the distinct object types with a more complex MySQL query like this:

SELECT * FROM(

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN host AS o ON o.id = obj.objectId
WHERE obj.objectType = 'Host'

UNION

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN storage_node AS o ON o.id = obj.objectId
WHERE obj.objectType = 'StorageNode'

UNION

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN storage_pool AS o ON o.id = obj.objectId
WHERE obj.objectType = 'StoragePool'

UNION

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN volume AS o ON o.id = obj.objectId
WHERE obj.objectType = 'volume'

UNION

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN storage AS o ON o.id = obj.objectId
WHERE obj.objectType = 'storage'

UNION

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN tape AS o ON o.id = obj.objectId
WHERE obj.objectType = 'Tape'

UNION

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN hv_data_store AS o ON o.id = obj.objectId
WHERE obj.objectType = 'hv_datastore'

UNION

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN switch AS o ON o.id = obj.objectId
WHERE obj.objectType = 'switch'

UNION

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN hv_virtual_machine AS o ON o.id = obj.objectId
WHERE obj.objectType = 'hv_virtualmachine'

UNION

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN internal_volume AS o ON o.id = obj.objectId
WHERE obj.objectType = 'internalVolume'

UNION

SELECT obj.objectType, o.id, o.name, a.annotationType, a.valueIdentifier
FROM object_to_annotation AS obj
JOIN annotation_value AS a ON a.id = obj.annotationValueId
JOIN qtree AS o ON o.id = obj.objectId
WHERE obj.objectType = 'qtree'

) AS t0
WHERE annotationType = 'DEMO';