Effective December 3, NetApp adopts Microsoft’s Business-to-Customer (B2C) identity management to simplify and provide secure access to NetApp resources.
For accounts that did not pre-register (prior to Dec 3), access to your NetApp data may take up to 1 hour as your legacy NSS ID is synchronized to the new B2C identity.
To learn more, read the FAQ and watch the video.
Need assistance? Complete this form and select “Registration Issue” as the Feedback Category.

Active IQ Unified Manager Discussions

Report result shows abnormal behaviour

sunilyadav

I am using OCI 6.3.2-141.

I have created a report with below headers. Intension was to generate a report of storage system connected to server with its HBA details:

I used report studio and selected "allow" for "Cross product allowed" in query properties.

StorageName HostName Manufacturer Model Firmware Driver

I was shocked when I saw the result. I have a total of 43 storage arrays in OCI server. And in result it was showing that each server is connected to all 43 storage arrays and model value is null for most of the servers. For eg:

StorageName HostName Manufacturer Model Firmware Driver

System1            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System2            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System3            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System4            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System5            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System6            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System7            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System8            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System9            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

.

.

.

so on


Am I doing it correctly? Do I have to change any query properties?

3 REPLIES 3

ostiguy

I think the cross join is the problem. I am not a SQL guy by trade, so I am not the right guy to give you an explanation as to why that is the problem, but the behavior you are seeing is what happens when you cross join that which should not be cross joined.

Do you have a sql query tool? I think the following hideous sql will do what you want:

use dwh_inventory;

SELECT

storage.name AS 'Storage Name',

host.name AS 'Host Name',

host_adapter.model AS 'HBA Model',

host_adapter.manufacturer AS 'HBA Manufacturer',

host_adapter.driver AS 'HBA Driver',

host_adapter.firmware AS 'HBA Firmware'

FROM

STORAGE STORAGE,

HOST HOST,

physical_path ppath

LEFT JOIN host_adapter

ON

ppath.hostid = host_adapter.hostid

WHERE

storage.id = ppath.storageid  AND

host.id = ppath.hostid AND

ppath.hostid IS NOT NULL    

GROUP BY

ppath.storageId,ppath.hostid,host_adapter.Id

ORDER BY

Storage.name

sunilyadav

Hi Ostiguy,

The query you mentioned is working perfectly and gave perfect results. But I want same thing from OCI in automated reports.

You said that "the behavior you are seeing is what happens when you cross join that which should not be cross joined".

Why these should not be cross joined? I mean I can see that manual SQL query is working perfectly then how difficult is it to implement it in OCI reports.

And these kind of cross-joins like connectivity between storage, switch, and servers or between different datamarts is expected when we generate cumtom reports.

ostiguy

Hey Sunil,

I am travelling for the next few days, but I will try to get a member of the brain trust to elaborate on cross joins.

In the interim, here is a video on how to import a raw sql query into a Report Studio report

Announcements
NetApp on Discord Image

We're on Discord, are you?

Live Chat, Watch Parties, and More!

Explore Banner

Meet Explore, NetApp’s digital sales platform

Engage digitally throughout the sales process, from product discovery to configuration, and handle all your post-purchase needs.

NetApp Insights to Action
I2A Banner
Public