Automatically update AIQUM data in an Excel spreadsheet

[Jim_Robertson]

I have a spreadsheet that uses information I have downloaded from multiple reports in AIQUM 9.9 (i.e. Volume Capacity, Volume IOPs, Node IOPS, etc).  Is there a way to automate the export of this data to Excel and avoid manually going to the report in AIQUM and clicking the "Reports > Download Excel"?

I am aware of the feature to upload from an Excel file, which is cool, but that just seems to upload the format of the report, it doesn't continue to update the data in the spreadsheet once it's been uploaded.  I also know that you can schedule the reports, but the only option there is to send them via email, which isn't much better doing the manual download.  It would be nice if you could schedule it to save the export to a shared drive.

IIs there maybe some way to use PowerShell and/or APIs to automatically extract the report data from AIQUM?

[RossC]

Hi Jim,

I don't know if I can give you a good detailed answer here, but 100% AIQ UM has APIs you can be leveraging - https://docs.netapp.com/us-en/active-iq-unified-manager/api-automation/concept_get_started_with_um_apis.html 

Also, if you're an Office 365 user, you might have access to a service called Microsoft Automate (previously called Microsoft Flows). You might be able to take a look at that and use that to pull data from spreadsheets that are attached to the email reports sent from UM. 

Another consideration is the ActiveIQ Digital Advisor tool (https://aiq.netapp.com) also has an API, so potentially you can pull data from there as well if you can't get it from Unified Manager. You may even potentially be able to make some API calls to AIQ Digital Advisor using Microsoft Automate. 

So that might be a good place to start.

[Jim_Robertson]

Thanks, @RossC .  I've been trying to use the APIs, but I can't quite wrap my head around an easy way to pull the data from AIQUM like I have in the reports.  I have downloaded Matt Beattie's AIQUM module for PowerShell (https://github.com/matthew-beattie/AIQUM) and it works, but so far I've only been able to pull really basic information, i.e. a list of volumes, a list of clusters, etc.  And even that is returned as a property of a PowerShell object that you have to parse through.  I can't figure out how to get a list of volumes with their various attributes (capacity, used capacity, IOPs, etc).

I was thinking that I may be able to use Excel's Power Query to connect to AIQUM's MySQL database, but it requires me to install ODBC Connecters for MySQL, and I don't have the rights to install that at my customer.