[OCI-DWH] [MySQL] NetApp ONTAP Volume Encryption Status from OCI Data Warehouse

I seem to have been neglecting this blog a little this year (perhaps adding too much stuff to my other IT blog....) This might be useful know how.

The following MySQL query run against the NetApp OnCommand Insight Data Warehouse in your MySQL query tool of choice (like MySQL Workbench), will allow your to export a CSV of all your NetApp Data ONTAP internal volumes (includes 7-Mode, even though these of course do not support any volume level encryption) and the volume encryption status.

SELECT
    s.name AS 'Storage Cluster'
  , e.fieldValue AS 'Is Encrypted?'
  , i.identifier AS 'Internal Volume Identifier'
  , s.microcodeVersion AS 'ONTAP Version'
FROM dwh_inventory.internal_volume AS i
LEFT JOIN
  (SELECT * FROM dwh_inventory.extended_data
    WHERE objectType = 'INTERNAL_VOLUME'
      AND fieldName = 'isEncrypted'
  ) AS e ON e.objectId = i.id
JOIN dwh_inventory.storage AS s ON s.id = i.storageId
WHERE s.manufacturer = 'NetApp'
  AND (s.microcodeVersion LIKE '%ONTAP'
    OR s.microcodeVersion LIKE '%7-Mode%');

What are the distinct Object Types and Field Names in dwh_inventory.extended_data?

From the above query, you can see that we're using dwh_inventory.extended_data to get the internal volume encryption status.

Just out of curiosity, what are the distinct Object Types and Field Names in dwh_inventory.extended_data?

The below outputs are from an OCI DWH 7.3.10 instance (yes, a bit behind, as I write this OCI is up to 7.3.13.)

SELECT DISTINCT objectType FROM dwh_inventory.extended_data ORDER BY 1 ASC;

  DISK

  INTERNAL_VOLUME

  STORAGE_NODE

  STORAGE_POOL

  SWITCH

  VOLUME

 

SELECT DISTINCT fieldName FROM dwh_inventory.extended_data WHERE objectType = 'DISK' ORDER BY 1 asc;

  isEncrypted

 

SELECT DISTINCT fieldName FROM dwh_inventory.extended_data WHERE objectType = 'INTERNAL_VOLUME' ORDER BY 1 asc;

  adaptiveQosPolicy

  comment

  groupName

  isEncrypted

  junctionPath

  objectStoreTieringPolicy

  objectStoreUsedSpace

  qosLimitIOPS

  qosLimitRaw

  qosPolicy

 

SELECT DISTINCT fieldName FROM dwh_inventory.extended_data WHERE objectType = 'STORAGE_NODE' ORDER BY 1 asc;

  managementIpAddresses

 

SELECT DISTINCT fieldName FROM dwh_inventory.extended_data WHERE objectType = 'STORAGE_POOL' ORDER BY 1 asc;

  isCompactionSavingsEnabled

  isEncrypted

  objectStoreMetadataSpace

  objectStoreName

  objectStorePhysicalUsedSpace

  objectStoreProviderType

  objectStoreReferencedSpace

  objectStoreS3Name

  objectStoreServer

  objectStoreSisSavedSpace

  objectStoreTieringFullnessThreshold

  objectStoreUnreclaimedSpace

  objectStoreUsedSpace

 

SELECT DISTINCT fieldName FROM dwh_inventory.extended_data WHERE objectType = 'SWITCH' ORDER BY 1 asc;

  lineCard01

  lineCard02

  lineCard03

  lineCard04

  lineCard05

  lineCard06

  lineCard07

  lineCard08

  lineCard09

  lineCard10

  lineCard11

  lineCard12

  lineCard13

  lineCard14

  lineCard15

  lineCard16

  lineCard17

  lineCard18

 

SELECT DISTINCT fieldName FROM dwh_inventory.extended_data WHERE objectType = 'VOLUME' ORDER BY 1 asc;

  isEncrypted

  qosLimitIOPS

  qosLimitMBPS

  storageGroups

~~~~~

The titular query was more around looking for NetApp NVE. The following bonus items look for disk encryption status (all arrays), and storage pool encryption status (all arrays.)

BONUS 1: Query to Find Disk Encryption Status

The following query will run for all storage arrays in your OCI DWH, and return whether the array has encrypted disks or not (like NetApp NSE).

SELECT DISTINCT

    s.name AS 'Storage Cluster'

  , s.manufacturer AS 'Manufacturer'

  , s.microcodeVersion AS 'Storage O/S'

  , e.fieldValue AS 'Has encrypted disks?'

FROM dwh_inventory.extended_data AS e

JOIN dwh_inventory.disk AS d ON d.id = e.objectId

JOIN dwh_inventory.storage As s ON s.id = d.storageId

WHERE e.objectType = 'DISK'

  AND e.fieldName = 'isEncrypted';

BONUS 2: Query to Find Storage Pool (like Aggregate) Encryption Status

The following query will run for all storage pools in your OCI DWH, and return whether the storage pool is encrypted or not (like NetApp NAE).

SELECT DISTINCT

    s.name AS 'Storage Cluster'

  , s.manufacturer AS 'Manufacturer'

  , s.microcodeVersion AS 'Storage O/S'

  , sp.identifier AS 'Storage Pool Identifier'

  , e.fieldValue AS 'Is Encrypted?'

FROM dwh_inventory.extended_data AS e

JOIN dwh_inventory.storage_pool AS sp ON sp.id = e.objectId

JOIN dwh_inventory.storage AS s ON s.id = sp.storageId

WHERE e.objectType = 'STORAGE_POOL'

  AND e.fieldName = 'isEncrypted';