Performing an ETL of Data from NetApp Cloud Insights

In this blog post, I go over using the REST API ODATA interface to acquire one table from the NetApp Cloud Insights DataWarehouse. Not really an ETL, but I couldn't think of a better title...

API Access

Firstly, we must generate an API Key. To do that, login to your NetApp Cloud Insights portal, and select:

ADMIN > API Access

Then click + API Access Token

And choose your options. For the sake of performing an ETL, we just need “Data Warehouse” and Read Only.

• Name: YOUR-TOKEN-NAME
• Description: A-DESCRIPTION
• What type of APIs will this token be used to call? All Categories / Acquisition Unit / Alerts / Assets / Data Collection / Data Ingestion / Data Warehouse
• Permissions: Read Only / Read/Write / Write Only
• Token Expires In: 1 Week / 1 Month / 3 Months / 6 Months / 1 Year / Custom
• No. of valid days:

For example:

Make sure to copy and store the token in a safe place -
Copy API Access Token
- your token will only be available once!

Performing an ETL of one DWH Table

- from Postman

2 Headers are required:

Content-type = application/json
X-CloudInsights-ApiKey = {The API Access Token you obtained above}

All requests will start with the url:

https://{tenant}.cloudinsights.netapp.com/rest/v1/dwh-management/odata/{schema}

The below image is an example where we pull dwh_inventory.storage. We have to add an additional key:

Accept = */*

https://{tenant}.cloudinsights.netapp.com/rest/v1/dwh-management/odata/dwh_inventory/storage

Image: Postman Acquisition of dwh_inventory.storage from NetApp Cloud Insights

Performing an ETL of one DWH Table

- using a Linux Client

demo@DESKTOP:~$

curl -X GET "https://YOURTENANT.cloudinsights.netapp.com/rest/v1/dwh-management/odata/dwh_inventory/storage" -H "accept: */*" -H "Content-Type: application/json" -H "X-CloudInsights-ApiKey: YOUR_VERY_LONG_API_TOKEN"

Image: Example

Adding a New Cloud Insights (CI) Acquisition Unit (AU)

 Official NetApp documentation is here: Configuring Acquisition Units (netapp.com)

In order to add a new Cloud Insights Acquisition Unit, you will need to have the following:

1. A login to the NetApp Cloud Insights tenant (via invite)
2. Monitor & Optimize Role: Administrator permission

And will have to have completed all your proxy/firewall requests as necessary.

1) Log into your NetApp Cloud Insights tenant:

https://YOURTENANT.cloudinsights.netapp.com/

2) In the right-hand panel click:

ADMIN > Data Collectors

3) Choose the option at the top for Acquisition Units and click + Acquisition Unit

4) Choose the Operating System of the Acquisition Unit server (here we are using Linux), and follow the Installation instructions as in the example below:

Troubleshooting Acquisition Unit Installation Problems on Linux

NetApp Documentation: Troubleshooting Acquisition Unit Problems on Linux (netapp.com)

Installation fails on SELinux

When the AU is installed on SELinux, SELinux should be either disabled or set to permissive mode. Once the installation is complete, enforcing mode can be enabled.

... but - potentially - when you reboot the CI AU, it may then fail to start (with SELinux enabled) and enterprises will not let you permanently disable SELinux (and nor should they.) Read on for what you should do in this case...

Note: I take no credit for the following section. This useful information comes from a Linux guru. Sharing here under the principle that good technical knowledge deserves to be disseminated for the benefit of seekers of help.

Installation of NetApp Cloud Insights AU on RHEL v8.4 (Without needing to permanently disable SELinux so the CI AU can survive a server reboot)

1) Check if noexec is set on tmpfs and set it to exec:

# mount |grep /tmp
# mount -o remount,exec /tmp

2) Check if enforcing is active and deactivate it:

# getenforce
# setenforce 0

3) Export proxy configuration (if required):

# export https_proxy='PROXY-SERVER:PORT' proxy_auth_scheme=''

4) Run the installer snippet from NetApp Cloud Insights WebUI to install the aquisition unit.

5) Reactivate noexec on tmpfs:

# mount -o remount,noexec /tmp

6) Reactivate Enforcing:

# setenforce 1

7) After installation change context for the symbolic link otherwise the CI AU service will not start after reboot because of active SELinux enforcing:

# chcon -h -u system_u -t usr_t /opt/netapp/cloudinsights/acq
# chcon -h -u system_u -t usr_t /opt/netapp/cloudinsights/acq/scripts/acquisition.server

After change the context should look like the following:

# ls -lZ /opt/netapp/cloudinsights/acq

• Before: lrwxrwxrwx. 1 cisys cisys unconfined_u:object_r:unlabeled_t:s0
• After: lrwxrwxrwx. 1 cisys cisys system_u:object_r:usr_t:s0

8) Check CI AU service status on server:

# cloudinsights-service.sh status

9) Reboot CI AU server and verify the service came up after the restart:

# cloudinsights-service.sh status

Additional Notes

A restorecon command tries to restore the “original” context, and this usually only changes something when there was a previously defined context (which there is not in /opt/netapp). But we could have run these two commands instead of the commands in step 7:

# semanage fcontext -a -t usr_t "/opt/netapp/cloudinsights(/.*)?"
# restorecon –R /opt/netapp/cloudinsights

After a future upgrade of the NetApp CI software, it may be required to run the restorecon command again due to new files being copied to the system without the proper SELinux context.

Updating a MySQL Table That's Missing FQDN DNS Information

I had to add a new column to a MySQL table for FQDN DNS data. We already have the IP addresses in the table. This table has nearly 10000 rows, so we need a relatively quick way to do a one off bulk update. This is the method I used, which didn't take long.

1) Acquire id and IP address from the table:

select id,trim(ip) from YOUR_TABLE where ip != '' order by id;

2) Export that to a CSV format.

If you're using MySQL workbench or something similar, it is very straightforward to do this.

3) We then turn to PowerShell and load the CSV:

[System.Array]$IPs = Import-CSV 'IPs.csv'

4) Run this one liner to acquire FQDNs for your IPs:

$IPs | Foreach{ $DNS = Resolve-DnsName $_.ip; IF($DNS){ ($_.id + "," + $_.ip + "," + $DNS.namehost) >> DNSs.csv} }

5) Load this CSV into Excel, and add the following formula into the 4th column:

="UPDATE YOUR_TABLE SET dns = '" &C1& "' WHERE id = "&A1&";"

6) Run the UPDATE commands and job done!

Upgrading Mellanox SN2010 Master/Standby Pair from 1*10GbE Uplink Per Switch to 2*25GbE Uplinks

Scenario

We start with 2 x Mellanox SN2010 switches in a Master/Standby Pair (paired together with an ISL.) We currently have 1 port per SN2010 with 10GbE SFPs in, used to uplink to customers edge switches. The uplink switches are configured with an LACP VPC (both uplinks are active.)

We want to upgrade the uplinks to 25GbE, and also have 2 * 25GbE uplinks per switch (i.e. 4 in total.)

The current relevant configuration (same on both switches) is:

interface mlag-port-channel 101
interface mlag-port-channel 101 description "Uplink"
interface ethernet 1/18 description "Network Uplink"
interface ethernet 1/18 speed 10G force
interface ethernet 1/18 mlag-channel-group 101 mode active
interface mlag-port-channel 101 switchport mode trunk
interface mlag-port-channel 101 no shutdown

Currently port 18 is used as an uplink. We'll add port 17 as an additional uplink.

Walkthrough

Note: It is expected that the relevant upstream switch configuration (4 port LACP VPC across 2 switches) has been completed.

First, verify the config/status of the relevant ports:

Switch# show interface ethernet 1/17
Switch# show interface ethernet 1/18
Switch# show interface mlag-port-channel 101

Switch1 – Upgrade uplink to 25GB

Switch1> enable
Switch1# write mem
Switch1# config t
Switch1(config)# interface ethernet 1/18 shutdown
Switch1(config)# interface ethernet 1/18 no mlag-channel-group
Switch1(config)# interface ethernet 1/18 speed 25G force
Switch1(config)# interface ethernet 1/18 mtu 9216
Switch1(config)# interface ethernet 1/18 switchport mode trunk
Switch1(config)# interface ethernet 1/18 mlag-channel-group 101 mode active
Switch1(config)# interface ethernet 1/18 no shutdown
Switch1(config)# exit
Switch1# show interface ethernet 1/18

Verify port 1/18 is up. If all is good, run a “write mem”.

Switch1# write mem

Switch2 – Upgrade uplink to 25GB

Switch2> enable
Switch2# write mem
Switch2# config t
Switch2(config)# interface ethernet 1/18 shutdown
Switch2(config)# interface ethernet 1/18 no mlag-channel-group
Switch2(config)# interface ethernet 1/18 speed 25G force
Switch2(config)# interface ethernet 1/18 mtu 9216
Switch2(config)# interface ethernet 1/18 switchport mode trunk
Switch2(config)# interface ethernet 1/18 mlag-channel-group 101 mode active
Switch2(config)# interface ethernet 1/18 no shutdown
Switch2(config)# exit
Switch2# show interface ethernet 1/18

Verify port 1/18 is up. If all is good, run a “write mem”.

Switch2# write mem

Switch1 – Configure second 25GB uplink

Switch1# config t
Switch1(config)# interface ethernet 1/17 shutdown
Switch1(config)# interface ethernet 1/17 speed 25G force
Switch1(config)# interface ethernet 1/17 mtu 9216
Switch1(config)# interface ethernet 1/17 switchport mode trunk
Switch1(config)# interface ethernet 1/17 mlag-channel-group 101 mode active
Switch1(config)# interface ethernet 1/17 no shutdown
Switch1(config)# exit
Switch1# show interface ethernet 1/17

Verify port 1/17 is up. If all is good, run a “write mem”.

Switch1# write mem

Switch2 – Configure second 25GB uplink

Switch2# config t
Switch2(config)# interface ethernet 1/17 shutdown
Switch2(config)# interface ethernet 1/17 speed 25G force
Switch2(config)# interface ethernet 1/17 mtu 9216
Switch2(config)# interface ethernet 1/17 switchport mode trunk
Switch2(config)# interface ethernet 1/17 mlag-channel-group 101 mode active
Switch2(config)# interface ethernet 1/17 no shutdown
Switch2(config)# exit
Switch2# show interface ethernet 1/17

Verify port 1/17 is up. If all is good, run a “write mem”.

Switch2# write mem

THE END

Python 3: OCI REST API - Set Annotation On Switch

 A very similar post to this September 2020 post:

Cosonok's IT Blog: Python 3: OCI REST API - Set Annotation On Storage Array

- which just goes to show that if you have a good framework (not mine, these were from the out-the-box OCI Python examples) then it's very easy to adapt that framework for other use cases (in this case switch and not storage.) To use the python script below:

python set_annotation_switch.py --url https://YOUR_OCI_SERVER --user YOUR_REST_USER --password YOUR_PASSWORD --switch SWITCH_NAME --annotation SOME_ANNOTATION --value ANNOTATION_VALUE

The Script

Save as 'set_annotation_switch.py':

#!/usr/bin/env python 

"""
Set annotation for a specific switch
"""

from __future__ import print_function
import json
from oci_rest import OciRest, configure_command_line_parser

def find_switch(oci, switch_name):
  for switch in oci.get('assets/switches'):
    if switch['name'] == switch_name:
      return switch['self']
  return None

def annotate_switch(oci, switch_url, annotation_name, annotation_value):
  return oci.put('{}/annotations'.format(switch_url), data=json.dumps(
    [
      {
        "rawValue": annotation_value,
        "definition": {"name": annotation_name}
      },
    ]
  ))

if __name__ == "__main__":

  # Get the default command-line arguments (url, user, password)
  parser = configure_command_line_parser(usage='Set switch annotation')

  # Add additional arguments to configure the search criteria
  parser.add_argument('--switch', required=True, help="Switch name")
  parser.add_argument('--annotation', required=True, help="Annotation name (do NOT use label!)")
  parser.add_argument('--value', required=True, help="Annotation value to set")

  options = parser.parse_args()
  url, user, password = options.url, options.user, options.password

  with OciRest(url, user, password) as oci:
    switch_url = find_switch(oci, switch_name=options.switch)

    if switch_url is not None:
      new_annotation = annotate_switch(oci,
          switch_url,
          annotation_name=options.annotation,
          annotation_value=options.value)

      print('Created new annotation:\n', json.dumps(new_annotation))

    else:
      print("Could not find switch {}".format(options.switch))

Image: OCI REST API documentation > API samples

Cisco 3232C: Install NX-OS and NetApp RCF via USB

Posting this useful bit of knowledge with permission from the author Timm Shark.

There are two parts to this post:

1. NX-OS Install via USB
2. RCF Install via USB

If you're installing a new Cisco 3232C in the datacenter, it can be quicker to use USB instead of using http/https/ftp/sftp server from a laptop, to install the NX-OS and NetApp RCF configuration file.

1) NX-OS Install via USB

1. In Windows, format a USB memory stick with FAT32.

2. From Windows, copy nxos.9.3.7.bin, n9000-epld.9.3.7.img and NX3232_RCF_v1.0_24p10g_26p100g.txt files to the memory stick.

3. Connect a serial cable to the console port on the switch.

4. Insert the USB memory stick into the switch. The switch will recognise the memory stick has been inserted and a console message will report 'USB1: online':

2021 Jun  2 09:37:11 sw2 ... USB insertion or removal detected - usbhsd

2021 Jun  2 09:37:11 sw2 ... USB1: online  - usbhsd

5. From the switch, check the contents of the memory stick:

switch# dir usb1:

... NX3232_RCF_v1.0_24p10g_26p100g.txt

... System Volume Information/

... n9000-epld.9.3.7.img

... nxos.9.3.7.bin

6. Copy the required files from usb1: to bootflash:

switch# copy usb1:nxos.9.3.7.bin bootflash:

switch# copy usb1:n9000-epld.9.3.7.img bootflash:

switch# copy usb1:NX3232_RCF_v1.0_24p10g_26p100g.txt bootflash:

7. Check bootflash: includes the required files

switch# dir bootflash:

8.Check current version of NX-OS

switch# show version

Software

  BIOS: version 08.38

  NXOS: version 7.0(3)I4(1)

  BIOS compile time:  05/29/2020

  NXOS image file is: bootflash:///nxos.7.0.3.I4.1.bin

  NXOS compile time:  5/15/2016 20:00:00 [05/16/2016 03:24:30]

9. Install NX-OS image. Switch will be rebooted at the end of the process.  When prompted, verify the versions being upgraded, then reply 'y' to continue. After continuing, the install will complete and the switch will reboot automatically.

switch# install all nxos bootflash:nxos.9.3.7.bin

Images will be upgraded according to following table:

Module Image  Running-Version(pri:alt)              New-Version

------ -----  ------------------------------------- ------------------

     1 nxos   7.0(3)I4(1)                           9.3(7)I9(1)

     1 bios   v08.38(05/29/2020):v08.38(05/29/2020) v08.38(05/29/2020)

Switch will be reloaded for disruptive upgrade.

Do you want to continue with the installation (y/n)?  [n] y

10. Once switch has rebooted, verify switch version is 9.3.7

sw2# show version

11. Check current EPLD version, upgrade the EPLD (and reboot if required)

sw2# show version module 1 epld

EPLD Device Version

-------------------

MI FPGA     0x12

IO FPGA     0x12

0x12 is current and the next command will check, then do nothing as no upgrade is required. Your switch may have an older EPLD version. 

switch# install epld bootflash:n9000-epld.9.3.7.img module 1

Digital signature verification is successful

Compatibility check:

Module  Type Upgradable Impact     Reason

------  ---- ---------- ---------- ------

     1  SUP  Yes        disruptive Module Upgradable

Retrieving EPLD versions.... Please wait.

Images will be upgraded according to following table:

Module Type  EPLD    Running-Version New-Version Upg-Required

------ ----  ------- --------------- ----------- ------------

     1  SUP  MI FPGA 0x12            0x12        No

     1  SUP  IO FPGA 0x12            0x12        No

All Modules are up to date.

12. If upgrade had been required, to check EPLD version after switch reboot: 

switch# show version module 1 epld

2) RCF Install via USB

1. Connect via the console

2. Erase the existing config.

IMPORTANT NOTE: These instructions are for a new install.

switch# write erase

Warning: This command will erase the startup-configuration.

Do you wish to proceed anyway? (y/n)  [n] y

switch# reload

This command will reboot the system. (y/n)?  [n] y

3. After the switch reboot, reply Y perform a basic setup of the switch.

4. Copy RCF to switch from USB (should have been done previously if you followed the instructions above.)

switch# copy usb1:NX3232_RCF_v1.0_24p10g_26p100g.txt bootflash:

5. Load RCF into running config

switch# copy NX3232_RCF_v1.0_24p10g_26p100g.txt running-config echo-commands

6. Check output to ensure you have the correct RCF file loaded

switch# show running-config

 7. Save and reload:

switch# copy running-config startup-config

switch# reload

Second Switch 

Apply RCF to the other switch

switch# copy NX3232_RCF_v1.0_24p10g_26p100g.txt running-config echo-commands

switch# copy running-config startup-config

switch# reload`

Check ISL port channel. After configuring first switch ports 1/31 and 1/32 show as suspended. After second switch is complete, both ports appear up in port channel

switch#: show port-channel summary

Check that you can SSH to the management port on each switch.

[ONTAP] Comparing LDAP Group Memberships on LDAP Server with ONTAP Cache

If you've added a new LDAP group membership to a user, but the user still cannot access the resources permissioned by that LDAP group, it's very likely that the cache on ONTAP is stale and needs to be cleared.

You can see the LDAP group memberships for the user - as on the LDAP server - using this clustershell command:

set adv
vserver services name-server getxxbyyy getgrlist -node NODE -vserver SVMNAME -username USERNAME

Then to see what is in ONTAP's cache, run this command:

vserver services name-server getxxbyyy getgrlist -node NODE -vserver SVMNAME -username USERNAME -use-cache true

If the group memberships as recorded in cache are not matching what is on the LDAP server, then to delete the cached group membership for the user:

vserver services name-service cache group-membership show -vserver SVMNAME -user USERNAME
vserver services name-service cache group-membership delete -vserver SVMNAME -user USERNAME -group GID

FabricPool Data in the NetApp OnCommand Insight 7.3.x DWH - Storage Pool Calculation

In this post - 
http://www.cosonok.com/2021/02/fabricpool-data-in-netapp-oncommand.html 
- we showed how to get rough fabricpool data out of the OCI DWH dwh_inventory.extended_data by using the objectStoreUsedSpace on INTERNAL_VOLUME. 
 
A patch a few months ago brought in a whole load of new objectStore attributes at the STORAGE_POOL level, which is actually a better way to get this data. 
 
If you run - 
 
SELECT distinct `fieldName` FROM dwh_inventory.extended_data WHERE objectType = 'STORAGE_POOL' AND fieldName LIKE 'object%'; 
 
- you see all the aggregate-level FabricPool (object) related statistics: 
 
objectStoreMetadataSpace 
objectStoreName 
objectStorePhysicalUsedSpace 
objectStoreProviderType 
objectStoreReferencedSpace 
objectStoreS3Name 
objectStoreServer 
objectStoreSisSavedSpace 
objectStoreTieringFullnessThreshold 
objectStoreUnreclaimedSpace 
objectStoreUsedSpace 
 
In order to get the FabricPool data from the OnCommand Insight DataWarehouse, you can use these MySQL queries: 
 
FabricPool Savings Per Aggregate 
 
-- TO GET FABRIC POOL PER AGGREGATE INFORMATION -- 
SELECT s.name AS 'Storage',t.aggregate, 
  MAX(CASE WHEN t.fieldname = 'objectStoreServer' THEN t.fieldvalue ELSE NULL END) AS 'objectStoreServer', 
  MAX(CASE WHEN t.fieldname = 'objectStoreName' THEN t.fieldvalue ELSE NULL END) AS 'objectStoreName', 
  MAX(CASE WHEN t.fieldname = 'objectStoreUsedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreUsedSpace TB', 
  MAX(CASE WHEN t.fieldname = 'objectStorePhysicalUsedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStorePhysicalUsedSpace TB', 
  MAX(CASE WHEN t.fieldname = 'objectStoreReferencedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreReferencedSpace TB', 
  MAX(CASE WHEN t.fieldname = 'objectStoreSisSavedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreSisSavedSpace TB', 
  MAX(CASE WHEN t.fieldname = 'objectStoreUnreclaimedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreUnreclaimedSpace TB' 
FROM ( 
 
  -- TO GET EXTENDED DATA INFORMATION -- 
  SELECT sp.name AS 'aggregate',sp.id,sp.storageId,ed.* 
  FROM dwh_inventory.storage_pool AS sp 
  JOIN dwh_inventory.extended_data AS ed ON sp.id = ed.objectId 
  WHERE objectType = 'STORAGE_POOL' AND fieldName LIKE 'object%' 
 
) AS t 
JOIN dwh_inventory.storage AS s ON s.id = t.storageId 
GROUP BY t.id 
 
FabricPool Savings Per ONTAP Cluster 
 
SELECT t2.Storage AS 'Cluster', 
  FLOOR(SUM(t2.`objectStoreUsedSpace TB`)) AS 'objectStoreUsedSpace TB', 
  FLOOR(SUM(t2.`objectStorePhysicalUsedSpace TB`)) AS 'objectStorePhysicalUsedSpace TB', 
  FLOOR(SUM(t2.`objectStoreReferencedSpace TB`)) AS 'objectStoreReferencedSpace TB', 
  FLOOR(SUM(t2.`objectStoreSisSavedSpace TB`)) AS 'objectStoreSisSavedSpace TB', 
  FLOOR(SUM(t2.`objectStoreUnreclaimedSpace TB`)) AS 'objectStoreUnreclaimedSpace TB' 
FROM( 
   
  -- TO GET FABRIC POOL PER AGGREGATE INFORMATION -- 
  SELECT s.name AS 'Storage',t.aggregate, 
    MAX(CASE WHEN t.fieldname = 'objectStoreServer' THEN t.fieldvalue ELSE NULL END) AS 'objectStoreServer', 
    MAX(CASE WHEN t.fieldname = 'objectStoreName' THEN t.fieldvalue ELSE NULL END) AS 'objectStoreName', 
    MAX(CASE WHEN t.fieldname = 'objectStoreUsedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreUsedSpace TB', 
    MAX(CASE WHEN t.fieldname = 'objectStorePhysicalUsedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStorePhysicalUsedSpace TB', 
    MAX(CASE WHEN t.fieldname = 'objectStoreReferencedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreReferencedSpace TB', 
    MAX(CASE WHEN t.fieldname = 'objectStoreSisSavedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreSisSavedSpace TB', 
    MAX(CASE WHEN t.fieldname = 'objectStoreUnreclaimedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreUnreclaimedSpace TB' 
  FROM ( 
   
    -- TO GET EXTENDED DATA INFORMATION -- 
    SELECT sp.name AS 'aggregate',sp.id,sp.storageId,ed.* 
    FROM dwh_inventory.storage_pool AS sp 
    JOIN dwh_inventory.extended_data AS ed ON sp.id = ed.objectId 
    WHERE objectType = 'STORAGE_POOL' AND fieldName LIKE 'object%' 
   
  ) AS t 
  JOIN dwh_inventory.storage AS s ON s.id = t.storageId 
  GROUP BY t.id 
 
) AS t2 
GROUP BY t2.Storage