[OCI][MySQL] Get A Count of NAS IPs Grouped By Storage...

on

This was a bit of a curiosity. I might take it further later on. It is filtered to find just NetApp Clustered ONTAP systems.

  • SELECT name, id, (LENGTH(IPs) - LENGTH(REPLACE(IPs, ',', '')) + 1) AS 'Count of IPs' FROM(
  •   SELECT s.name,s.id,group_concat(distinct n.ipInterfaces) AS IPs
  •   FROM dwh_inventory.nas_share AS n
  •   JOIN dwh_inventory.storage AS s ON s.id = n.storageId
  •   WHERE s.microcodeVersion LIKE '%ONTAP%'
  •   GROUP BY n.storageId
  • ) AS t0
  • ORDER BY 2 DESC


Note 1: To find all the table columns like IP (say you're looking for IP information in the OCI DWH) then you can use:

SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE TABLE_SCHEMA = 'dwh_inventory' AND COLUMN NAME LIKE '%IP%'

Note 2: To find all tables like application (say you were trying to analyse the usage of applications ... this is unrelated to the original post but goes together with note 1)

SELECT table_schema,table_name FROM information_schema.tables WHERE table_name LIKE '%application%' AND table_schema = 'dwh_inventory' ORDER BY table_name

Comments

Post a Comment