- Community Home
- >
- Servers and Operating Systems
- >
- HPE ProLiant
- >
- Server Management - Systems Insight Manager
- >
- Re: Need help with SQL query
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-01-2016 08:09 AM - edited 11-01-2016 08:16 AM
11-01-2016 08:09 AM - edited 11-01-2016 08:16 AM
Need help with SQL query
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-08-2016 07:40 AM - edited 11-08-2016 07:41 AM
11-08-2016 07:40 AM - edited 11-08-2016 07:41 AM
Re: Need help with SQL query
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
NK