Thursday, 31 March 2022

[OCI][Python] Collecting All NetApp OCI Queries Across Multiple OCI Servers and Export to CSV

A Python script to connect to multiple NetApp OnCommand Insight Servers in turn, and collect all the Queries (queries you see in the WebUI) and relating information. Perhaps useful if you're doing a consolidation exercise (like  many OCI to one CI). And in the next post, we'll collect the Annotation Rules (which will use some of these queries.)

My Python skills are pretty poor, which is a good reason for me to have a go at improving them!

Save the script as say GetQueries.py and run from your prompt as python GetQueries.py.

The Script

## IMPORT THE ESSENTIALS ##
import requests
from base64 import b64encode
import csv
import json
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

## SETUP CREDENTIALS FOR THE OCI SERVERS (ASSUMING ALL THE SAME) ##
userAndPass = b64encode(b"USERNAME:PASSWORD").decode("ascii")
headers = {'Authorization':'Basic %s' % userAndPass}

## CREATE A LIST OF OCI SERVERS ##
OCIS=[]
OCIS.append("OCI_SERVER1_FQDN")
OCIS.append("OCI_SERVER2_FQDN")
OCIS.append("OCI_SERVER3_FQDN")

## INITIALIZE A FILE FOR THE CSV OUTPUT ##
output = open('queries.csv',mode='w',newline='')
writer = csv.writer(output, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
writer.writerow(['OCI Server','Query Name','Object Type','Sort Order','Fields','filters'])

## CYCLE THROUGH THE OCIS COLLECTING THE QUERIES ##
for OCI in OCIS:
  baseurl = "https://" + OCI + "/rest/v1/"
  api = "queries"
  response = requests.request("GET",baseurl + api, headers = headers, verify = False)
  json_data = json.loads(response.text)

  for query in json_data:
    writer.writerow([OCI,query["name"], query["objectType"], query["sort"], query["fields"], query["filters"]])

## CLOSE THE CSV ##
output.close()

Wednesday, 23 March 2022

[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';