The following script was just over an afternoons work (I think my Python is improving 😊.) The requirement was to create a summary report listing the storage and switches that are in NetApp OnCommand Insight vs what is currently in NetApp Cloud Insights. I'm working on a large OCI to CI migration and we need to report on this every two weeks. The script will output 3 CSVs as below. There is a column in the switches and storages CSV, which is YES/NO depending on whether the device is in CI or not.
Here is the is the Python script. Save as say OCIvCI.py, edit the parameters as per your environment, and run simply as> python OCIvCI.py.
The Script
'''
Only do (1) if you've not got it yet.
Only do (2) if you're behind a proxy.
1) CMD> python -m pip install mysql-connector-python
2) CMD> set https_proxy=http://USERNAME:PASSWORD@YOUR-PROXY-FQDN:PORT
3) CMD> python OCIvCI.py
'''
import csv
import json
import requests
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
import mysql.connector
''' PARAMETERS '''
tenantUrl = "https://YOURTENANT.cloudinsights.netapp.com"
token = "YOUR API TOKEN(ONLY NEEDS READONLY DATA WAREHOUSE)"
batch = 1000
mysqlHost = "YOUR-OCI-DWH-FQDN"
mysqlUser = "dwhuser"
mysqlPass = "*********"
mysqlPort = 3306
''' -------- '''
''' STORAGES '''
''' -------- '''
''' ACQUIRE FROM CLOUD INSIGHTS '''
headers = {'X-CloudInsights-ApiKey': '{key}'.format(key=token)}
api = ('/rest/v1/dwh-management/odata/dwh_inventory/storage')
collect = True
skip = 0
results = []
while collect:
apiExtra = ('?&$top=' + str(batch) + '&$skip=' + str(skip))
print(apiExtra)
j = requests.get(tenantUrl + api + apiExtra,headers=headers,verify=False).json()
if len(j['value']) == 0:
print("Reached the end!")
collect = False
else:
results += j['value']
skip += batch
print("Acquired from CI DWH ODATA.")
print("Results count = " + str(len(results)),end = "\r\n\n")
''' ACQUIRE FROM OCI-DWH '''
mydb = mysql.connector.connect(
host=mysqlHost,
user=mysqlUser,
password=mysqlPass,
port=mysqlPort,
database="dwh_inventory"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT name,identifier,ip,model,manufacturer,serialNumber,microcodeVersion,family,lastAcquiredTime,Active,Country,region FROM dwh_inventory.storage")
myresult = mycursor.fetchall()
print("Acquired from OCI DWH MySQL.")
print("Results count = " + str(len(myresult)),end = "\r\n\n")
num_fields = len(mycursor.description)
field_names = [i[0] for i in mycursor.description]
nameField = field_names.index('name')
ipField = field_names.index('ip')
snField = field_names.index('serialNumber')
manuField = field_names.index('manufacturer')
''' COMPARE STORAGES IN OCI VS CI '''
myresult2 = [field_names + ['inCI'] + ['Notes']]
for row in myresult:
match = False
for row2 in results:
if row[ipField] == row2['ip']: match = True
elif row[nameField] == row2['name']: match = True
elif (type(row[snField]).__name__ != 'NoneType' and row[snField] == row2['serialNumber'] and row[manuField] != 'NetApp'): match = True
if match == True: newRow = list(row) + ['YES'] + ['']
else: newRow = list(row) + ['NO'] + ['']
myresult2 += [newRow]
''' OUTPUT TO CSV '''
output = open('OCIvCIstorages.csv',mode='w',newline='')
writer = csv.writer(output, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
for row in myresult2: writer.writerow(row)
output.close()
print("Output: OCIvCIstorages.csv")
''' COUNTS FOR OCI & CI (STORAGE) '''
counts = {}
counts["CI"] = {}
counts["OCI"] = {}
inCIcol = myresult2[0].index('inCI')
manuCol = myresult2[0].index('manufacturer')
i = 0
for row in myresult2:
if i == 0: pass
elif row[manuCol] not in counts["OCI"]:
counts["OCI"][row[manuCol]] = 1
counts["CI"][row[manuCol]] = 0
if row[inCIcol] == 'YES':
counts["CI"][row[manuCol]] = 1
elif row[manuCol] in counts["OCI"]:
counts["OCI"][row[manuCol]] += 1
if row[inCIcol] == 'YES':
counts["CI"][row[manuCol]] += 1
i += 1
''' -------- '''
''' SWITCHES '''
''' -------- '''
api = ('/rest/v1/dwh-management/odata/dwh_inventory/switch')
collect = True
skip = 0
results = []
while collect:
apiExtra = ('?&$top=' + str(batch) + '&$skip=' + str(skip))
print(apiExtra)
j = requests.get(tenantUrl + api + apiExtra,headers=headers,verify=False).json()
if len(j['value']) == 0:
print("Reached the end!")
collect = False
else:
results += j['value']
skip += batch
print("Acquired from CI DWH ODATA.")
print("Results count = " + str(len(results)),end = "\r\n\n")
''' ACQUIRE FROM OCI-DWH '''
mycursor = mydb.cursor()
mycursor.execute("SELECT ip,name,manufacturer,model,firmware,serialNumber,lastAcquiredTime,Active,Country,region FROM dwh_inventory.switch")
myresult = mycursor.fetchall()
print("Acquired from OCI DWH MySQL.")
print("Results count = " + str(len(myresult)),end = "\r\n\n")
num_fields = len(mycursor.description)
field_names = [i[0] for i in mycursor.description]
nameField = field_names.index('name')
ipField = field_names.index('ip')
snField = field_names.index('serialNumber')
''' COMPARE SWITCHES IN OCI VS CI '''
myresult2 = [field_names + ['inCI'] + ['Notes']]
for row in myresult:
match = False
for row2 in results:
if row[ipField] == row2['ip']: match = True
elif row[nameField] == row2['name']: match = True
elif row[snField] == row2['serialNumber']: match = True
if match == True: newRow = list(row) + ['YES'] + ['']
else: newRow = list(row) + ['NO'] + ['']
myresult2 += [newRow]
''' OUTPUT TO CSV '''
output = open('OCIvCIswitches.csv',mode='w',newline='')
writer = csv.writer(output, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
for row in myresult2: writer.writerow(row)
output.close()
print("Output: OCIvCIswitches.csv",end = "\r\n\n")
''' COUNTS FOR OCI & CI (SWITCH) '''
inCIcol = myresult2[0].index('inCI')
manuCol = myresult2[0].index('manufacturer')
i = 0
for row in myresult2:
if i == 0: pass
elif row[manuCol] not in counts["OCI"]:
counts["OCI"][row[manuCol]] = 1
counts["CI"][row[manuCol]] = 0
if row[inCIcol] == 'YES':
counts["CI"][row[manuCol]] = 1
elif row[manuCol] in counts["OCI"]:
counts["OCI"][row[manuCol]] += 1
if row[inCIcol] == 'YES':
counts["CI"][row[manuCol]] += 1
i += 1
''' CREATE SUMMARY OUTPUT '''
manufacturers = list(counts["OCI"].keys())
manufacturers.sort()
summary = [['Manufacturer','Count in OCI','Count in CI']]
for m in manufacturers:
newRow = [[m,counts["OCI"][m],counts["CI"][m]]]
summary += newRow
''' OUTPUT TO CSV '''
output = open('OCIvCIsummary.csv',mode='w',newline='')
writer = csv.writer(output, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
for row in summary: writer.writerow(row)
output.close()
print("Output: OCIvCIsummary.csv",end = "\r\n\n")
Comments
Post a Comment