Some OCI queries

[sunilyadav]

1. How to add  custom annotations in chargeback datamart or in FC path under inventory?

     I know how to add them in Inventory or storage capacity but same method not working with above data marts.

2. How to get number of discs/luns connected to servers?

     I know how to get total capacity allocated to servers but I want count of discs/luns assigned to each server.

3. How to apply the logic that if report doesn't have any data, no mail sent or vice-versa?

     I want to send report by email to specific users only when it contains some data satisfy the filters used in it. Don't want to send empty emails.

4. Remove partial hitachi systems from a datasource. HDS datasource contain 20 arrays, but I want to remove some from that, cannot configure separate data source for each system.

     I tried this method mentioned in this article, but not working.

https://kb.netapp.com/support/index?page=content&id=1011093&actp=search&viewlocale=en_US&searchid=1374579504256

[ostiguy]

#2. The inventory mart has all the path to host relationships. I would build the report out of inventory mart to answer this question - note that the results are subject to double counting, meaning:

sqlnode A has a 10GB boot lun, and a 150GB shared lun

sqlnode B has a 10GB boot lun, and a 150GB shared lun

On the back end, there are 3 luns - 10GB, 10GB, 150GB = 170GB allocated

This host centric report would have 160GB for each host, creating the impression that 320GB has been allocated.

So, the inventory mart is great for visibility, but there is a risk for certain capacity reporting use cases.

[ostiguy]

#4. HDS exclusions.

ARRAY.HDS9570V.65010524

is an example of one. You should have the data in the Storage Arrays view - the middle string is the Model value, and the last string is the serial number.

We haven't been getting OCI ASUP from your site for a few weeks, so I cannot see your extended logs to see your HDS recording

[moechnig]

1a.  Custom annotations in Chargeback data mart:  To the best of my knowledge, custom annotations do not propagate beyond the Inventory data mart.  To access them, you will need to use SQL to join tables from Capacity and Inventory. 

1b.  Custom annotations on FC Path in the Inventory mart:  I don't think this is possible; Path does not seem to be an object to which annotations can be applied in the GUI.  You will probably want to work out a way to record the necessary data in an annotation on either the host/vm or the volume. 

2.  What Matt said.  Depending on what you're trying to do, it may be as simple as

select host.name, count(logical.id)

from host, logical

where host.id = logical.hostid

group by name

This will get you a count of the number of paths associated with each host.  Where a volume is shared among hosts, it will be counted with each host.  You may want to make sure that iSCSI and NFS are included or excluded in the manner you expect. 

3.  I do not know.  If you figure out a process, please share!  You may want to investigate Cognos triggers; a Google search for "cognos report trigger" turns up a few hopeful-looking results. 

4.  I don't have an HDS array in my current OCI environment, but if I recall correctly the string you need to put in the Exclude box appears in the Data Sources view, Devices micro-view, either Device or Originator column.  Make sure you're getting it entered correctly.  Once you have configured the exclusion, force acquisition on the data source and let it run to completion.  Then return to the Data Sources, Devices micro-view, right-click on the line for the array you've excluded, and do "Inactivate Originator".  If you're inactivating a large array, this will run for a long time.  Let it finish.  Then, assuming you do not intend to re-discover the array with another data source, go to the Storage Arrays view, right-click the array, and "Delete Inactive Device".  Assuming all of that works (I'm reciting this from memory), please submit feedback on the KB article requesting that the more complete process be added. 

[sunilyadav]

Hi James and Matt,

1. Not doable so drop it.

2. I understand the double counting concept when doing capacity report using inventory, but I still finding a way to do it (if possible). I tried to use count of logical.id as well as logical.volumeId, none of them providing satisfactory results.

3. I am also finding a best possible solution. But as of now I am send report data in email body instead of attachment which saves time in opening them everyday. Will do further testing on it later

4. I tried the same method. Device is deleted from OCI, but even after multiple ETL processes I still find those arrays in DWH some reports even if the storage name is picked from "Simple datamart" under storage capacity datamart.

[moechnig]

1. Ok.
2. How do you want capacity that is shared among several hosts to be counted?  There are several options that I can think of; others may be possible:
   1. Show the capacity on each host that has access.  This is the default.  Good if you want to know what each host has; not good if you want to sum the numbers and end up with the amount of capacity allocated to hosts collectively. 
   2. Show the capacity on one arbitrary host that has access.  Avoids double counting, but doesn't accurately reflect what any given host has. 
   3. Divide the capacity shared among a group of hosts evenly among the sharing  hosts.  Also doesn't really tell you anything about individual hosts, but it results in the total reflecting the total amount of storage.  Difficult to implement, but the "host group" objects in the Capacity Mart will probably help. 
3. Ok.
4. References to arrays that are no longer present in OCI are historical, so the marts you're using must be including historical data.  Use tables with "current" in the name, or include the dateTk field and filter for the current dateTk. 

[sunilyadav]

Hi James,

I understand the challenges in point 2.

Regarding point 4, not all datamarts have current facts. For example Storage and Storage pool capacity datamart.

[moechnig]

That is correct.  For those marts, join your other data to the date_dimension table where date_dimension.latest = 1.