Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Version Information:

Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
HP Server Automation v9.14

I am creating a report of all my HP-SA managed servers. The report includes data from multiple tables. With the exception of Networking information everything is included on one row. I'm trying to come up with a method to concatenate the NIC NAME, MAC, IP, DNS so that I don't get multiple rows for each active NIC.

I ran into roadblocks with sub select queries not knowing how to pass the D.DVC_ID from the outer query into the sub query. I briefly tried using WM_CONCAT and LISTAGG without success. Any help would be greatly appreciated

Posting the full SQL in case any other HPSA users out there could benefit from knowing where in the database to look for some basic hardware information:

SELECT D.SYSTEM_NAME AS "HOSTNAME",
    D.DVC_DESC AS "OPSWARE NAME",
    I.HW_ADDR AS "MAC ADDRESS",
    I.SLOT AS "INTERFACE",
    I.IP_ADDRESS AS "IP ADDRESS",
    I.NETMASK AS "SUBNET",
    D.DEFAULT_GW AS "GATEWAY",
    D.DVC_MFG AS "SERVER VENDOR",
    D.DVC_MODEL AS "SERVER MODEL",
    CONCAT (
        ROUND(MEM.QUANTITY / 1024 / 1024, 1),
        ' GB'
        ) AS "RAM",
    CPU.CPU_MODEL AS "CPU TYPE",
    COUNT(CPU.CPU_SLOT) AS "CPU QUANTITY"
FROM TRUTH.DEVICES D
INNER JOIN TRUTH.MEMORY_COMPONENTS MEM ON (D.DVC_ID = MEM.DVC_ID)
INNER JOIN TRUTH.CPU_COMPONENTS CPU ON (D.DVC_ID = CPU.DVC_ID)
INNER JOIN TRUTH.DEVICE_ROLES DR ON (D.DVC_ID = DR.DVC_ID)
INNER JOIN TRUTH.INTERFACES I ON (D.DVC_ID = I.DVC_ID)
INNER JOIN TRUTH.CUSTOMER_CLOUDS CC ON (DR.CUST_CLD_ID = CC.CUST_CLD_ID)
INNER JOIN TRUTH.DATA_CENTERS DC ON (DC.DATA_CENTER_ID = CC.DATA_CENTER_ID)
WHERE MEM.MEMORY_TYPE != 'SWAP'
    AND I.ENABLED_FLG = 'Y'
GROUP BY D.SYSTEM_NAME,
    D.DVC_DESC,
    I.HW_ADDR,
    I.SLOT,
    I.IP_ADDRESS,
    D.DVC_MFG,
    D.DVC_MODEL,
    MEM.QUANTITY,
    CPU.CPU_MODEL,
    I.NETMASK,
    D.DEFAULT_GW
ORDER BY HOSTNAME ASC,
    INTERFACE ASC

With a sample result of:

HOSTNAME         OPSWARE NAME  MAC ADDRESS        INTERFACE                IP ADDRESS      SUBNET         GATEWAY       SERVER VENDOR  SERVER MODEL             RAM   CPU TYPE                                CPU QUANTITY  
---------------  ------------  -----------------  -----------------------  --------------  -------------  ------------  -------------  -----------------------  ----  --------------------------------------  ------------  
WIN-6V18DDNQRL4  Server1       00:50:56:BB:66:A2  Local Area Connection    172.31.0.149    255.255.255.0  172.31.0.10   VMWARE, INC.   VMWARE VIRTUAL PLATFORM  4 GB  Intel(R) Xeon(R) CPU  E5345  @ 2.33GHz  2             
WIN-6V18DDNQRL4  Server1       00:50:56:BB:03:D3  Local Area Connection 2  169.254.246.97  255.255.0.0    172.31.0.10   VMWARE, INC.   VMWARE VIRTUAL PLATFORM  4 GB  Intel(R) Xeon(R) CPU  E5345  @ 2.33GHz  2             

Desired Output would have everything to do with the server on one row similar to the following:

HOSTNAME         OPSWARE NAME  (MAC ADDRESS/INTERFACE/IP ADDRESS/SUBNET/GATEWAY)                                                                                                                 SERVER VENDOR  SERVER MODEL             RAM   CPU TYPE                                CPU QUANTITY  
---------------  ------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------------------  ----  --------------------------------------  ------------  
WIN-6V18DDNQRL4  Server1       (00:50:56:BB:66:A2/Local Area Connection/172.31.0.149/255.255.255.0/172.31.0.10,00:50:56:BB:03:D3/Local Area Connection 2/169.254.246.97/255.255.0.0/172.31.0.10)  VMWARE, INC.   VMWARE VIRTUAL PLATFORM  4 GB  Intel(R) Xeon(R) CPU  E5345  @ 2.33GHz  2             

Thank you, Lars

share|improve this question

migrated from serverfault.com Sep 30 '13 at 21:41

This question came from our site for system and network administrators.

2  
The listagg is what you need. This built-in function is able to aggregate rows to columns : docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm –  Nicolas Durand Oct 1 '13 at 6:59
    
Nicolas, Thanks for your suggestion. Unfortunately we are on an older version of 11g which doesn't have LISTAGG. 'ORA-00904: "LISTAGG": invalid identifier' –  Lars Oct 1 '13 at 13:27
    
Your tag was Oracle-11G, so i believe it was 11GR2... No matter, another function exist since 10G : COLLECT docs.oracle.com/cd/B19306_01/server.102/b14200/functions024.htm . There is also a good references of several technics to convert rows to lines : oracle-base.com/articles/misc/string-aggregation-techniques.php –  Nicolas Durand Oct 1 '13 at 14:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.