topic Re: Report result shows abnormal behaviour in Active IQ Unified Manager Discussions

Report result shows abnormal behaviour

sunilyadav — Thu, 05 Jun 2025 06:11:19 GMT

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?

Re: Report result shows abnormal behaviour

ostiguy — Mon, 28 Jan 2013 16:31:55 GMT

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

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

Re: Report result shows abnormal behaviour

sunilyadav — Tue, 29 Jan 2013 06:26:47 GMT

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.

Re: Report result shows abnormal behaviour

ostiguy — Tue, 29 Jan 2013 11:24:55 GMT

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