[CI][Python] Getting the Output of Cloud Insights Data Lake Query to CSV

on

A NetApp Cloud Insights Data Lake based query doesn't have an export to CSV option in the WebUI.

Click to enlarge.

In order to get the data, you can use the REST API.

The following script will output to CSV.

Specify:
  • Tenant URL
  • API key
  • Query Number (in the example above it is 227)
  • Output File Name

The Script

I've not parameterized it, so change the highlighted parameters as suits you.

Save the script as say QUERY_TO_CSV.PY or whatever name you like and run as:

python QUERY_TO_CSV.PY


import csv
import datetime
import requests
import time
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
o = "STARTING CI_DATA_LAKE_QUERY_TO_CSV.PY"; print(str(datetime.datetime.now()).split('.',1)[0] + ": " + o); time.sleep(1)

''' PARAMETERS '''

tenant = "YOURTENANT"
apiKey = "YOURAPIKEY"
query = QUERYID
outputFile = "query.csv"

headers = {'X-CloudInsights-ApiKey': '{key}'.format(key=apiKey)}

''' GETTING THE QUERY OUTPUT '''
# Note 1: Data Lake uses ['rows']. Standard CI uses ['results'].
# Note 2: Data Lake queries are dependent on the columns in the query (unlike Standard CI.)

o = "COLLECTING QUERY"; print(str(datetime.datetime.now()).split('.',1)[0] + ": " + o); time.sleep(1)
batch = 1000
rows = []
api = tenant + "/rest/v1/queries/" + str(query) + "/result"
collect = True; skip = 0
while collect:
  apiExtra = ('?limit=' + str(batch) + '&offset=' + str(skip))
  j = requests.get(api+apiExtra,headers=headers,verify=False).json()
  if len(j['rows']) == 0: collect = False
  else: rows += j['rows']
  skip += batch

''' GETTING THE COLUMNS FOR THE CSV (EXPANDS KEYS) '''
# Note: We base the columns on rows[0], so if this didn't have all columns...

o = "CONSTRUCTING THE CSV"; print(str(datetime.datetime.now()).split('.',1)[0] + ": " + o); time.sleep(1)
columns = []
for k in rows[0].keys():
  if type(rows[0][k]) is dict:
    for k2 in rows[0][k].keys():
      columns += [(k + ":" + k2)]
  else:
    columns += [k]

''' CONSTRUCT THE OUTPUT '''
# Note: We handle issues where rows might not have the same keys as rows[0]
output = []
for row in rows:
  newLine = []
  for k in rows[0].keys():
    if type(rows[0][k]) is dict:
      for k2 in rows[0][k].keys():
        try:
          newLine += [row[k][k2]]
        except KeyError:
  # Note: Assuming string field ''
          newLine += ['']
    else:
      newLine += [row[k]]
  output += [newLine]

''' OUTPUT TO CSV '''
o = "OUTPUT TO CSV"; print(str(datetime.datetime.now()).split('.',1)[0] + ": " + o); time.sleep(1)
outCSV = open(outputFile,mode='w',newline='')
writer = csv.writer(outCSV, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
v = writer.writerow(columns)
for o in output:
  v = writer.writerow(o)

outCSV.close()
o = "THE END!"; print(str(datetime.datetime.now()).split('.',1)[0] + ": " + o); time.sleep(1)


Comments

Post a Comment