[Troubleshooting] Is hv_data_store_to_internal_volume Working in CI?

This was a little bit of an interesting one. I might be wrong but I think something's not right with the mapping of VMware datastores to NetApp Flexvols (internal volumes in NetApp Cloud Insights) in the CI DataWarehouse - some VMs on NAS datastores were missing from our VM chargeback report. It will be fixed soon but the troubleshooting effort might be of interest.

Firstly, I found a VM on a NAS datastore that I was missing from the chargeback report. The VM existed in CI. The storage existed in CI. The datastore existed in CI. But the mapping from datastore to internal_volume didn't exist - this was apparent from the VM dropping out of the chargeback report when we try to join the table dwh_inventory.hv_data_store_to_internal_volume.

I did lots of steps to troubleshoot (below) but the thing that made me believe what I was seeing was the simplest thing which I did at the very end:

SELECT COUNT(*) FROM hv_data_store_to_internal_volume

In the old OCI world this was like 15 times bigger than what I was seeing in the CI world. Less than 10% of the internal_volume to storage mappings were coming through to the DataWarehouse.

And I later learnt I could see the same problem in the front-end too. Looking at a DataStores query and saw no mapped storage resource. But the underlying volumes (same name as the datastore) were there.

Mapping Datastore Name to Storage Name in OCI/CI DWH

SELECT `Datastore Name`,`Storage Name`
    ds.name AS 'Datastore Name'
  , s.name AS 'Storage Name'
  , count(*) AS 'Count'
FROM hv_data_store AS ds
JOIN hv_data_store_to_internal_volume AS iv2ds ON iv2ds.dataStoreId = ds.id
JOIN storage AS s ON s.id = iv2ds.storageId
GROUP BY ds.name
) AS t0
WHERE `Count` = 1 -- I'm only interested in distinct datastores

Trying Out the Above Data from OCI in CI

Firstly, the output of the above MySQL query was exported to CSV (from MySQL Workbench.)

We need to do a few things to the output to make a temporary (in MySQL query) table from SELECT and UNION SELECT statements:

  • Ignore anything with > 2 commas in (i.e. 7-mode storage systems have a comma in the HA-pair name.)
  • Remove all "
  • Replace , with ','
  • Add to start of first line SELECT '
  • Add to start of all subsequent lines UNION SELECT '
  • End of line '
This is fairly easy to do with a bit of PowerShell:

$file = Get-Content "DS to Storage Mapping.csv"
$new = @()
foreach ($r in $file){
  If($r.split(',').count -le 2){
    $r = $r.replace('"','')
    $r = $r.replace(",","','")
    if ($new.count -eq 0){ $r = "SELECT '" + $r }
    else { $r = "UNION SELECT '" + $r }
    $r = $r + "'"
    $new += $r
$new | Set-Content "Processed File.txt"

Finally, the output of the above is pasted into the MySQL query below (replacing the highlighted bit) and we can run it in the NetApp Cloud Insights Cognos to get an output, which tells us if the Datastore is in CI, the Datastore Mapping is in CI, the Storage is in CI. If we have Datastore in CI and Storage in CI, and no mapping, then something's up.

    t1.`Datastore Name` AS 'Datastore'
  , t1.`Storage Name` AS 'Storage Name'
  , CASE
      WHEN ds.name IS NULL THEN "Not in CI!"
      ELSE "In CI!"
    END AS 'Datastore in CI'
  , CASE
      WHEN iv2ds.storageId IS NULL THEN "No mapping in CI!"
      ELSE "Mapped!"
    END AS 'DS to IV Mapping'
  , CASE
      WHEN s2.name IS NULL THEN "Not in CI!"
      ELSE "In CI!"
    END AS 'Storage in CI'
SELECT 'Datastore Name','Storage Name'

LIMIT 1,999999999
) AS t1
LEFT JOIN hv_data_store AS ds ON ds.name = t1.`Datastore Name`
LEFT JOIN hv_data_store_to_internal_volume AS iv2ds ON iv2ds.dataStoreId = ds.id
LEFT JOIN storage AS s1 ON s1.id = iv2ds.storageId
LEFT JOIN storage AS s2 ON s2.name = t1.`Storage Name`

What was this issue?

Could it be that the default AU had been down for like 2 months!?

The default AU is used for device resolution.

To be continued...