The output from this NetApp OCI DataWarehouse MySQL query, should bring back a lot of useful information for teams tasked with CIFS security hardening. It will tell them all the shares out there, and a wealth of useful information.
In the below query, some custom (but quite common) annotations are referenced, these would be:
- s.region
- s.country
- q.`cost_code`
- s.tier
The MySQL query (which you can turn into an IBM Cognos report or whatever else you'd like to do with it):
SELECT
s.region AS 'Region'
, s.country AS 'Country'
, s.name AS 'Storage Cluster'
, SUBSTRING_INDEX(p.name,':',1) AS 'Storage Controller'
, s.microcodeVersion AS 'Storage Software'
, i.virtualStorage AS 'SVM/vFiler'
, REPLACE(n.name,'/','') AS 'CIFS Share'
, CASE
WHEN s.microcodeVersion LIKE '%7-mode%' THEN SUBSTRING_INDEX(n.identifier,':',1)
WHEN s.microcodeVersion LIKE '%clustered%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(n.identifier,':',2),':',-1)
ELSE n.identifier
END AS 'Share Path'
, FLOOR(q.quotaUsedCapacityMB/1024) AS 'Qtree Used GB'
, FLOOR(i.dataUsedCapacityMB/1024) AS 'FlexVol Used GB'
, CAST(n.ipInterfaces AS CHAR(999)) AS 'ipInterfaces'
, GROUP_CONCAT(DISTINCT a.businessUnit) AS 'Qtree Business Unit'
, GROUP_CONCAT(DISTINCT a.name) AS 'Qtree Application Name'
, q.`cost_code` AS 'Qtree Cost Code'
FROM dwh_inventory.nas_share AS n
JOIN dwh_inventory.storage AS s ON s.id = n.storageId
JOIN dwh_inventory.nas_file_share AS f ON f.id = n.fileShareId
JOIN dwh_inventory.internal_volume AS i ON i.id = f.internalVolumeId
JOIN dwh_inventory.storage_pool AS p ON p.id = i.storagePoolId
JOIN dwh_inventory.qtree AS q ON q.id = f.qtreeId
LEFT JOIN dwh_inventory.qtree_to_application AS qa ON qa.qtreeId = f.qtreeId
LEFT JOIN dwh_inventory.application AS a ON a.id = qa.applicationId
WHERE n.protocol = 'CIFS'
AND s.manufacturer = 'NetApp'
AND s.tier = 'NAS'
GROUP by n.id
Comments
Post a Comment