NetApp OCI Annotation Import Utility (AIU): Creating AIU Lines for Qtree Cost Code Annotation

I was provided a spreadsheet with -

CLUSTERNAME, VOLUME, QTREE, "CODE CODE"

- and asked to update the annotations. This is fairly straightforward as we'll see below.

Firstly, I need to get the string for the Annotation Import Utility (AIU). This string needs to know the SVM (Storage Virtual Machine / vServer), so first this to do with the spreadsheet was turn it into a temporary data table inside an SQL query. We use concatenation in Excel (actually ...&...) to turn the data into lines of SELECT, for example:

SELECT 'CLUSTER1', 'VOLUME1', 'QTREE1', 'COST CODE' UNION

The SQL query I used to get the vServer and construct the lines for the AIU was:

SELECT
CONCAT('Qtree,', d.clu, '->', v.name, '->', d.qt, ",'", d.cc, "'") as 'AIU_line'
FROM(
  SELECT null as clu, null as vol, null as qt, null as cc UNION
  ...
... COPY AND PASTE LINES FOR EXCEL SHEET HERE ...
... REMOVE 'UNION' FROM FINAL LINE ...
  ...
) AS d
LEFT JOIN storage AS s ON s.name = d.clu
LEFT JOIN qtree AS q ON q.storageId = s.id AND q.name = d.qt
LEFT JOIN internal volume AS v ON v.id = q.internalVolumeId

Note: I use 'LEFT JOIN' because I want to see null lines where we get no match. I can compare this with the spreadsheet.

You can then get a CSV of this output (I'm using MySQL Workbench). Replace the ' with " (was getting some odd behaviour exporting "..." so used '...' instead). Add the output to a text file after the line:

,,"Cost Code"

Then simply run the AIU from the command prompt as:

java -jar rest-anno-import-utility.jar -u**** -p**** -a**** c:\temp\qtreeAnnotate.csv

u = username. p = password. a = server name/IP (address).

Image: Running NetApp OCI Annotation Import Utility