HP SIM 7.3 - Change in Database-Views not reflecting in the WebGUI?

[Andreas-BTC]

Hello,,

A few days ago we have updated one of our SIM from Version 7.2 to 7.3. Afterwards we experienced that some reports didn’t work anymore. We have analyzed the corresponding database-views and found changes in Version 7.3 which doesn’t reflect the sql query used for the corresponding report.

Problematic 7.3-Database-Views with missing attributes (found so far):

R_CPU - DeviceID & NumberOfCPUCores

R_OperatingSystem - Number Of Licensed Users

R_NetworkInterface - IPv6Address

R_PowerSupply - Type

R_DIMMSlots - RedundancyName

R_UnixSensors – CurrentReading

Questions:

Are the changed database-views in 7.3 correct, but are not reflected in the (unchanged) sql queries of the reports (in our SIM) OR are there in reality no changes in the database-views between SIM versions 7.2 and 7.3?

Is there a standardized way to correct the issues (we have generated new views based on SIM 7.2)?

SQL query used to generate the data in the table: CPU

select R_CPU.DeviceName, R_CPU.CPUType, R_CPU.DeviceID, R_CPU.CPUSpeed, R_CPU.SlotNumber, R_CPU.FirmwareID, R_CPU.ProcessorLoad, R_CPU.ProcessorAllocated, R_CPU.Location, R_CPU.CellNumber, R_CPU.ArchitectureRevision, R_CPU.FirmwareRevision, R_CPU.DataWidth, R_CPU.Manufacturer, R_CPU.NumberOfCPUCores, R_CPU.PowerManagementSuppotred from R_CPU  WHERE ((R_CPU.SnapShotID=-1) OR (R_CPU.SnapShotID is NULL)) AND DeviceKey in (select devices.deviceKey from devices  LEFT OUTER JOIN DB_deviceInfoEx ON devices.deviceKey = DB_deviceInfoEx.deviceKey LEFT OUTER JOIN IPAddress ON devices.deviceKey = IPAddress.deviceKey and IPAddress.ipIndex = 0 LEFT OUTER JOIN associated_device_data ON devices.deviceKey = associated_device_data.deviceKey  LEFT OUTER JOIN deviceProtocolInfo ON devices.deviceKey = deviceProtocolInfo.deviceKey LEFT OUTER JOIN cwStatusView ON devices.deviceKey = cwStatusView.deviceKey  LEFT OUTER JOIN simAggrStatus ON devices.deviceKey = simAggrStatus.deviceKey  LEFT OUTER JOIN DeviceStatusValue ON devices.deviceKey = DeviceStatusValue.deviceKey and DeviceStatusValue.StatusSourceId= 6 JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '0000002655ff58f80000000400000007'   WHERE (  devices.Name = 'atla9004'  )  )  order by R_CPU.DeviceName ASC 

View R_CPU SIM 7.3

SELECT     dbo.devices.DeviceKey, dbo.devices.Name AS DeviceName, dbo.CIM_Processor.Name AS CPUType,  dbo.CIM_Processor.CurrentClockSpeed AS CPUSpeed,

                      dbo.CIM_Processor.OtherIdentifyingInfo AS SlotNumber, dbo.CIM_Processor.SnapshotID,  dbo.CIM_Processor.Family AS FirmwareID,

                      dbo.CIM_Processor.LoadPercentage AS ProcessorLoad, dbo.CIM_Processor.R_CPUStatus AS ProcessorAllocated,

                     dbo.CIM_Processor.dc_HWLocation AS Location,  dbo.CIM_Processor.ProcessorLocation_CellNumber AS CellNumber,

                     dbo.CIM_Processor.ArchitectureRevision, dbo.CIM_Processor.FirmwareRevision,  dbo.CIM_Processor.DataWidth

FROM         dbo.CIM_Processor LEFT OUTER JOIN

                      dbo.devices ON dbo.CIM_Processor.NodeID = dbo.devices.DeviceKey

View R_CPU SIM 7.2

SELECT     dbo.devices.DeviceKey, dbo.devices.Name AS DeviceName, dbo.CIM_Processor.Name AS CPUType, dbo.CIM_Processor.DeviceID,

                      dbo.CIM_Processor.CurrentClockSpeed AS CPUSpeed, dbo.CIM_Processor.OtherIdentifyingInfo AS SlotNumber, dbo.CIM_Processor.SnapshotID,

                      dbo.CIM_Processor.Family AS FirmwareID, dbo.CIM_Processor.LoadPercentage AS ProcessorLoad, dbo.CIM_Processor.R_CPUStatus AS ProcessorAllocated,

                      dbo.CIM_Processor.dc_HWLocation AS Location, dbo.CIM_Processor.ProcessorLocation_CellNumber AS CellNumber, dbo.CIM_Processor.ArchitectureRevision,

                      dbo.CIM_Processor.NumberOfCPUCores, dbo.CIM_Processor.FirmwareRevision, dbo.CIM_Processor.DataWidth, dbo.CIM_Processor.Manufacturer,

                      dbo.CIM_Processor.PowerManagementSuppotred

FROM         dbo.CIM_Processor LEFT OUTER JOIN

                      dbo.devices ON dbo.CIM_Processor.NodeID = dbo.devices.DeviceKey

SQL query used to generate the data in the table: Operating System Information

select R_OperatingSystem.DeviceName, R_OperatingSystem.Description, R_OperatingSystem.Version, R_OperatingSystem.OSType, R_OperatingSystem.SubDesc, R_OperatingSystem.OSVendor, R_OperatingSystem.NumberOfLicensedUsers, R_OperatingSystem.InstallDate from R_OperatingSystem  WHERE ((R_OperatingSystem.SnapShotID=-1) OR (R_OperatingSystem.SnapShotID is NULL)) AND DeviceKey in (select devices.deviceKey from devices  LEFT OUTER JOIN DB_deviceInfoEx ON devices.deviceKey = DB_deviceInfoEx.deviceKey LEFT OUTER JOIN IPAddress ON devices.deviceKey = IPAddress.deviceKey and IPAddress.ipIndex = 0 LEFT OUTER JOIN associated_device_data ON devices.deviceKey = associated_device_data.deviceKey  LEFT OUTER JOIN deviceProtocolInfo ON devices.deviceKey = deviceProtocolInfo.deviceKey LEFT OUTER JOIN cwStatusView ON devices.deviceKey = cwStatusView.deviceKey  LEFT OUTER JOIN simAggrStatus ON devices.deviceKey = simAggrStatus.deviceKey  LEFT OUTER JOIN DeviceStatusValue ON devices.deviceKey = DeviceStatusValue.deviceKey and DeviceStatusValue.StatusSourceId= 6 JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '0000002655ff58f80000000400000007'   WHERE (  devices.Name = 'atla9004'  )  )  order by R_OperatingSystem.DeviceName ASC

View R_OperatingSystem SIM 7.3

SELECT     dbo.devices.DeviceKey, dbo.devices.Name AS DeviceName, dbo.CIM_OperatingSystem.Name AS Description, dbo.CIM_OperatingSystem.Version,

                      dbo.CIM_OperatingSystem.Description AS SubDesc, dbo.CIM_OperatingSystem.dc_OSType AS OSType, dbo.CIM_OperatingSystem.SnapshotID,

                      dbo.DB_DeviceInfoEx.OSVendor

FROM         dbo.DB_DeviceInfoEx INNER JOIN

                      dbo.devices ON dbo.DB_DeviceInfoEx.DeviceKey = dbo.devices.DeviceKey LEFT OUTER JOIN

                      dbo.CIM_OperatingSystem ON dbo.devices.DeviceKey = dbo.CIM_OperatingSystem.NodeID

View R_OperatingSystem SIM 7.2

SELECT     dbo.devices.DeviceKey, dbo.devices.Name AS DeviceName, ISNULL(dbo.CIM_OperatingSystem.Name, dbo.DB_DeviceInfoEx.OSName) AS Description,

                      ISNULL(dbo.CIM_OperatingSystem.Version, dbo.DB_DeviceInfoEx.OSVersion) AS Version, ISNULL(dbo.CIM_OperatingSystem.Description,

                      dbo.DB_DeviceInfoEx.OSDescription) AS SubDesc, ISNULL(dbo.DB_DeviceInfoEx.OSType, dbo.CIM_OperatingSystem.dc_OSType) AS OSType,

                      dbo.CIM_OperatingSystem.SnapshotID, dbo.DB_DeviceInfoEx.OSVendor, dbo.CIM_OperatingSystem.NumberOfLicensedUsers,

                      dbo.CIM_OperatingSystem.InstallDate

FROM         dbo.DB_DeviceInfoEx INNER JOIN

                      dbo.devices ON dbo.DB_DeviceInfoEx.DeviceKey = dbo.devices.DeviceKey LEFT OUTER JOIN

                      dbo.CIM_OperatingSystem ON dbo.devices.DeviceKey = dbo.CIM_OperatingSystem.NodeID

SQL query used to generate the data in the table: Network Interface

select R_NetworkInterface.DeviceName, R_NetworkInterface.Description, R_NetworkInterface.MacAddress, R_NetworkInterface.IPAddress, R_NetworkInterface.InputErrors, R_NetworkInterface.OutputErrors, R_NetworkInterface.Speed, R_NetworkInterface.Duplex, R_NetworkInterface.InterfaceName, R_NetworkInterface.IPv6Address, R_NetworkInterface.SubnetMask, R_NetworkInterface.Manufacturer, R_NetworkInterface.BroadcastAddress, R_NetworkInterface.InterfaceState, R_NetworkInterface.DHCPEnabled, R_NetworkInterface.ProtocolType, R_NetworkInterface.OperationalStatus, R_NetworkInterface.PortType, R_NetworkInterface.MaxDataSize from R_NetworkInterface  WHERE ((R_NetworkInterface.SnapShotID=-1) OR (R_NetworkInterface.SnapShotID is NULL)) AND DeviceKey in (select devices.deviceKey from devices  LEFT OUTER JOIN DB_deviceInfoEx ON devices.deviceKey = DB_deviceInfoEx.deviceKey LEFT OUTER JOIN IPAddress ON devices.deviceKey = IPAddress.deviceKey and IPAddress.ipIndex = 0 LEFT OUTER JOIN associated_device_data ON devices.deviceKey = associated_device_data.deviceKey  LEFT OUTER JOIN deviceProtocolInfo ON devices.deviceKey = deviceProtocolInfo.deviceKey LEFT OUTER JOIN cwStatusView ON devices.deviceKey = cwStatusView.deviceKey  LEFT OUTER JOIN simAggrStatus ON devices.deviceKey = simAggrStatus.deviceKey  LEFT OUTER JOIN DeviceStatusValue ON devices.deviceKey = DeviceStatusValue.deviceKey and DeviceStatusValue.StatusSourceId= 6 JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '0000002655ff58f80000000400000007'   WHERE (  devices.Name = 'atla9004'  )  )  order by R_NetworkInterface.DeviceName ASC

View R_NetworkInterface SIM 7.3

SELECT     dbo.CIM_NetworkAdapter.NodeID AS DeviceKey, dbo.CIM_NetworkAdapter.SystemName AS DeviceName, dbo.CIM_NetworkAdapter.Caption AS Description,

                      dbo.CIM_NetworkAdapter.PermanentAddress AS MacAddress, dbo.IPAddress.IPAddress, dbo.CIM_NetworkAdapter.EthernetAdp_InternalMACRcvErr AS InputErrors,

                      dbo.CIM_NetworkAdapter.EthernetAdp_InternalMACTranErr AS OutputErrors, dbo.CIM_NetworkAdapter.MaxSpeed AS Speed,

                      dbo.CIM_NetworkAdapter.R_Duplex AS Duplex, dbo.CIM_NetworkAdapter.FullDuplex, dbo.CIM_NetworkAdapter.Name AS InterfaceName,

                      dbo.CIM_NetworkAdapter.IPProtocolEndpoint_SubnetMask AS SubnetMask, dbo.CIM_NetworkAdapter.dc_BroadcastAddress AS BroadcastAddress,

                      dbo.CIM_NetworkAdapter.StatusInfo AS InterfaceState, dbo.CIM_NetworkAdapter.dc_DHCPEnabled AS DHCPEnabled, dbo.IPAddress.IPAddressNumber AS IPLongValue,

                       dbo.CIM_NetworkAdapter.SnapshotID, dbo.CIM_NetworkAdapter.LANEndpoint_OperationalStatus AS OperationalStatus,

                      dbo.CIM_NetworkAdapter.LANEndpoint_ProtocolType AS ProtocolType, dbo.CIM_NetworkAdapter.EthernetPort_MaxDataSize AS MaxDataSize,

                      dbo.CIM_NetworkAdapter.EthernetPort_PortType AS PortType

FROM         dbo.IPAddress RIGHT OUTER JOIN

                      dbo.CIM_NetworkAdapter ON dbo.IPAddress.DeviceKey = dbo.CIM_NetworkAdapter.NodeID AND

                      dbo.IPAddress.MACaddr = dbo.CIM_NetworkAdapter.R_MacAddress

WHERE     (NOT (dbo.CIM_NetworkAdapter.Caption LIKE N'%ppp%')) AND (NOT (dbo.CIM_NetworkAdapter.Caption LIKE N'%Loopback%')) OR

                      (dbo.CIM_NetworkAdapter.Caption IS NULL)

View R_NetworkInterface SIM 7.2

SELECT     dbo.CIM_NetworkAdapter.NodeID AS Devicekey, dbo.devices.Name AS DeviceName, dbo.CIM_NetworkAdapter.Caption AS Description,

                      dbo.CIM_NetworkAdapter.NetworkAddresses AS IPAddress, dbo.CIM_NetworkAdapter.PermanentAddress AS MacAddress,

                      dbo.CIM_NetworkAdapter.EthernetAdp_InternalMACRcvErr AS InputErrors, dbo.CIM_NetworkAdapter.EthernetAdp_InternalMACTranErr AS OutputErrors,

                      dbo.CIM_NetworkAdapter.MaxSpeed AS Speed, dbo.CIM_NetworkAdapter.IPv6Address, dbo.CIM_NetworkAdapter.R_Duplex AS Duplex,

                      dbo.CIM_NetworkAdapter.FullDuplex, dbo.CIM_NetworkAdapter.Name AS InterfaceName, dbo.CIM_NetworkAdapter.IPProtocolEndpoint_SubnetMask AS SubnetMask,

                      dbo.CIM_NetworkAdapter.dc_BroadcastAddress AS BroadcastAddress, dbo.CIM_NetworkAdapter.StatusInfo AS InterfaceState,

                      dbo.CIM_NetworkAdapter.dc_DHCPEnabled AS DHCPEnabled, dbo.IPAddress.IPAddressNumber AS IPLongValue, dbo.CIM_NetworkAdapter.SnapshotID,

                      dbo.CIM_NetworkAdapter.LANEndpoint_OperationalStatus AS OperationalStatus, dbo.CIM_NetworkAdapter.LANEndpoint_ProtocolType AS ProtocolType,

                      dbo.CIM_NetworkAdapter.EthernetPort_MaxDataSize AS MaxDataSize, dbo.CIM_NetworkAdapter.EthernetPort_PortType AS PortType,

                      dbo.CIM_NetworkAdapter.Manufacturer

FROM         dbo.devices RIGHT OUTER JOIN

                      dbo.CIM_NetworkAdapter ON dbo.devices.DeviceKey = dbo.CIM_NetworkAdapter.NodeID LEFT OUTER JOIN

                      dbo.IPAddress ON dbo.CIM_NetworkAdapter.NodeID = dbo.IPAddress.DeviceKey AND dbo.CIM_NetworkAdapter.R_MacAddress = dbo.IPAddress.MACaddr

WHERE     (NOT (dbo.CIM_NetworkAdapter.Caption LIKE N'%ppp%')) AND (NOT (dbo.CIM_NetworkAdapter.Caption LIKE N'%Loopback%')) OR

                      (dbo.CIM_NetworkAdapter.Caption IS NULL)

SQL query used to generate the data in the table: Power Supply

select R_PowerSupply.DeviceName, R_PowerSupply.ModelName, R_PowerSupply.SerialNumber, R_PowerSupply.FirmwareRev, R_PowerSupply.Status, R_PowerSupply.Condition, R_PowerSupply.MaxCapacity, R_PowerSupply.UsedCapacity, R_PowerSupply.RedundancyState, R_PowerSupply.Description, R_PowerSupply.Type, R_PowerSupply.PhysicalLocation, R_PowerSupply.Manufacturer, R_PowerSupply.PowerSupplyIdentifier, R_PowerSupply.RedundancyName, R_PowerSupply.RedundancyStatus from R_PowerSupply  WHERE ((R_PowerSupply.SnapShotID=-1) OR (R_PowerSupply.SnapShotID is NULL)) AND DeviceKey in (select devices.deviceKey from devices  LEFT OUTER JOIN DB_deviceInfoEx ON devices.deviceKey = DB_deviceInfoEx.deviceKey LEFT OUTER JOIN IPAddress ON devices.deviceKey = IPAddress.deviceKey and IPAddress.ipIndex = 0 LEFT OUTER JOIN associated_device_data ON devices.deviceKey = associated_device_data.deviceKey  LEFT OUTER JOIN deviceProtocolInfo ON devices.deviceKey = deviceProtocolInfo.deviceKey LEFT OUTER JOIN cwStatusView ON devices.deviceKey = cwStatusView.deviceKey  LEFT OUTER JOIN simAggrStatus ON devices.deviceKey = simAggrStatus.deviceKey  LEFT OUTER JOIN DeviceStatusValue ON devices.deviceKey = DeviceStatusValue.deviceKey and DeviceStatusValue.StatusSourceId= 6 JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '0000002655ff58f80000000400000007'   WHERE (  devices.Name = 'atla9004'  )  )  order by R_PowerSupply.DeviceName ASC

View R_PowerSupply SIM 7.3

SELECT     dbo.CIM_PowerSupply.NodeID AS DeviceKey, dbo.CIM_PowerSupply.SystemName AS DeviceName, dbo.CIM_PowerSupply.DeviceID,

                      dbo.CIM_PhysicalElement.Model AS ModelName, dbo.CIM_PhysicalElement.SerialNumber, dbo.CIM_PhysicalElement.dc_FirmwareRevision AS FirmwareRev,

                      dbo.CIM_PowerSupply.Availability AS ConditionVal, dbo.CIM_PowerSupply.TotalOutputPower / 1000 AS MaxCapacity,

                      dbo.CIM_PowerSupply.dc_CurrentOutputPower / 1000 AS UsedCapacity, dbo.CIM_PowerSupply.dc_RedundancyState AS RedundancyState,

                      dbo.CIM_PowerSupply.dc_PowerSupplyStatus AS Status, dbo.CIM_PowerSupply.R_Condition AS Condition, dbo.CIM_PowerSupply.SnapshotID,

                      dbo.CIM_PowerSupply.OtherIdentifyingInfo AS Description

FROM         dbo.CIM_PhysicalElement RIGHT OUTER JOIN

                      dbo.CIM_PowerSupply ON dbo.CIM_PhysicalElement.NodeID = dbo.CIM_PowerSupply.NodeID AND

                      dbo.CIM_PhysicalElement.SnapshotID = dbo.CIM_PowerSupply.SnapshotID AND dbo.CIM_PhysicalElement.Tag = dbo.CIM_PowerSupply.DeviceID

View R_PowerSupply SIM 7.2

SELECT     dbo.CIM_PowerSupply.NodeID AS DeviceKey, dbo.devices.Name AS DeviceName, dbo.CIM_PowerSupply.DeviceID, dbo.CIM_PhysicalElement.Model AS ModelName,

                      dbo.CIM_PhysicalElement.SerialNumber, dbo.CIM_PhysicalElement.dc_FirmwareRevision AS FirmwareRev, dbo.CIM_PowerSupply.Availability AS ConditionVal,

                      dbo.CIM_PowerSupply.TotalOutputPower / 1000 AS MaxCapacity, dbo.CIM_PowerSupply.dc_CurrentOutputPower / 1000 AS UsedCapacity,

                      dbo.CIM_PowerSupply.dc_RedundancyState AS RedundancyState, dbo.CIM_PowerSupply.dc_PowerSupplyStatus AS Status, dbo.CIM_PowerSupply.RedundancyName,

                      dbo.CIM_PowerSupply.RedundancyStatus, dbo.CIM_PowerSupply.R_Condition AS Condition, dbo.CIM_PowerSupply.SnapshotID,

                      dbo.CIM_PowerSupply.OtherIdentifyingInfo AS Description, dbo.CIM_PowerSupply.Type, dbo.CIM_PowerSupply.Location AS PhysicalLocation,

                      dbo.CIM_PowerSupply.Manufacturer, dbo.CIM_PowerSupply.Name AS PowerSupplyIdentifier

FROM         dbo.devices RIGHT OUTER JOIN

                      dbo.CIM_PowerSupply ON dbo.devices.DeviceKey = dbo.CIM_PowerSupply.NodeID LEFT OUTER JOIN

                      dbo.CIM_PhysicalElement ON dbo.CIM_PowerSupply.NodeID = dbo.CIM_PhysicalElement.NodeID AND

                      dbo.CIM_PowerSupply.SnapshotID = dbo.CIM_PhysicalElement.SnapshotID AND dbo.CIM_PowerSupply.DeviceID = dbo.CIM_PhysicalElement.Tag

Best regards

Andreas von der Heide

Team Windows Support

BTC  IT Services GmbH

[c_ce]

Hi,

Is there any advice provided by HP for this kind of issues ??

I have the same problem and didn't found any other similar case on the web.

Would be great if HP could handle this issue and provide a fix or workaround.

Thanks.
Regards

[ServerT]

Hello

Is there still no fix from HP? Then we have the same problem with the DB, that the tables are not really filled like the WebGUI.

\Stefan

[Andrew_Haak]

Hello,

We have had the same problem with the update from 7.1 to 7.2. I think that the database fields don't get migrated in the right way for the queries to work. If you run a clean install i think the queries should work wich proves the update of the database model did not go as planned. Could you do a clean inststall on some virtual server with the express SQL?

The last time i spoke HP about this problem the final 'solution'  was a clean install, not what you would wish...