Need help with SQL query

bbarz — Tue, 01 Nov 2016 15:16:44 GMT

I have a report that's run daily and is used to keep an inventory list updated. I'm tying to add hyperlinks in the inventory that will link directly to the device pages of our servers in HP SIM. The url of the page is https://server:port/mxportal/DevicePage/devicepage.jsp?nodeId=<MxGUID>.

I thought I'd be able to just include the MxGuid in the report but it doesn't look like this value can be included in a report. My next thought was to modify the SQL query that generates the report. The only problem - I only know the most basic of T-SQL and haven't been able to figure it out. Any SQL gurus out there that can modify the query below to include the MxGUID value from the dbo.devices table in the output?

select R_Inventory.DeviceName, R_Inventory.IPAddress, R_Inventory.DeviceStatus, R_Inventory.ProductName, R_Inventory.SerialNumber, R_Inventory.MemorySize, R_Inventory.OSName, R_Inventory.Location, R_Inventory.DeviceOwner, R_Inventory.DeviceBootTime, R_Inventory.numberOfCPU
from R_Inventory
WHERE ((R_Inventory.SnapShotID=-1) OR (R_Inventory.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 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 = '00000022649872840000000400000014'
WHERE ( ( devices.productType = 1) ) ) order by R_Inventory.DeviceName ASC

Re: Need help with SQL query

NJK-Work — Tue, 08 Nov 2016 15:41:41 GMT

If you can figure out the table/column that holds the MxGUID, you can do something like this:

...R_Inventory.numberOfCPU,'https://yoursimserver:50000/mxportal/DevicePage/devicepage.jsp?nodeId=' + MxGUIDColumnName AS SMHPage

But it might be easier to just go directly the SMH URL if you can't figure out the MxGUID field:

...R_Inventory.numberOfCPU, 'https://' + R_Inventory.DeviceName + ':2381' AS SMHPage

topic Need help with SQL query in Server Management - Systems Insight Manager

Need help with SQL query

Re: Need help with SQL query