STEHelp
asked on
Combine IP addresses from multiple rows in SQL Server 2005
We use System Center Configuration Manager (SCCM) in our environment. I'm trying to utilize the data in the SCCM database to automate our server inventory. I have come a long way, but there is one piece that I cannot figure out. I am a server guy, not a SQL guy. I"m hoping there is a simple way to do this!
Basically I have a SQL script that can be turned in to a report in SCCM. The script works perfect for the data that I have currently, but I want to add some additional data, such as IP address for each server.
Attached is my SQL after I added the IP address information.
The issue with the IP address information is that a server can have multiple IP addresses. I want ALL IP addresses for a server to be listed on a single row and separated by commas.
For example, This is how I need the output:
Server Processor OS IP Address
-------------------------- ---------- ---------- ---------- ---------- ---------- -
Server1 Intel Xeon 2003 10.9.83.1, 10.9.83.2,10.9.83.3
This is how my output actually appears
Server Processor OS IP Address
-------------------------- ---------- ---------- ---------- ---------- ---------- -
Server1 Intel Xeon 2003 10.9.83.1
Server1 Intel Xeon 2003 10.9.83.2
Server1 Intel Xeon 2003 10.9.83.3
The IP address information can be found in the v_RA_System_IPAddresses view. The view only contains to columns,
ResourceID IP_Addresses0
The ResourceID is unique to each server, so I can use it to join with my current script. +
Is there an easy way to make the IP addresses all list on one line with the server? Thanks to anyone who takes the time to respond!
Basically I have a SQL script that can be turned in to a report in SCCM. The script works perfect for the data that I have currently, but I want to add some additional data, such as IP address for each server.
Attached is my SQL after I added the IP address information.
The issue with the IP address information is that a server can have multiple IP addresses. I want ALL IP addresses for a server to be listed on a single row and separated by commas.
For example, This is how I need the output:
Server Processor OS IP Address
--------------------------
Server1 Intel Xeon 2003 10.9.83.1, 10.9.83.2,10.9.83.3
This is how my output actually appears
Server Processor OS IP Address
--------------------------
Server1 Intel Xeon 2003 10.9.83.1
Server1 Intel Xeon 2003 10.9.83.2
Server1 Intel Xeon 2003 10.9.83.3
The IP address information can be found in the v_RA_System_IPAddresses view. The view only contains to columns,
ResourceID IP_Addresses0
The ResourceID is unique to each server, so I can use it to join with my current script. +
Is there an easy way to make the IP addresses all list on one line with the server? Thanks to anyone who takes the time to respond!
select distinct v_R_System.Name0,
v_GS_COMPUTER_SYSTEM.Manufacturer0,v_GS_COMPUTER_SYSTEM.Model0,v_GS_PC_BIOS.SerialNumber0,
v_GS_COMPUTER_SYSTEM.Domain0,v_R_SYSTEM.location0, v_R_SYSTEM.description0,v_GS_COMPUTER_SYSTEM.NumberOfProcessors0,
v_GS_PROCESSOR.AddressWidth0,v_GS_PROCESSOR.name0,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,v_GS_OPERATING_SYSTEM.Caption0,v_GS_OPERATING_SYSTEM.CSDVersion0,
v_RA_System_IPAddresses.ip_addresses0
from v_R_System
LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_PROCESSOR ON v_GS_PROCESSOR.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_X86_PC_MEMORY ON v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_PC_BIOS ON v_GS_PC_BIOS.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_RA_System_IPAddresses ON v_RA_System_IPAddresses.ResourceID = v_R_System.ResourceID
where v_R_SYSTEM.operating_system_name_and0 LIKE '%Server%'
order by v_R_SYSTEM.name0
ASKER
i'm sorry, I am not very knowledgeable of SQL. Can you help me with the exact query? I tried adding from your comment, but received an error.
Attached is what the SQL looks like after I added your statement.
Here is the error:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'ORDER'.
Attached is what the SQL looks like after I added your statement.
Here is the error:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'ORDER'.
select distinct v_R_System.Name0,
v_GS_COMPUTER_SYSTEM.Manufacturer0,v_GS_COMPUTER_SYSTEM.Model0,v_GS_PC_BIOS.SerialNumber0,
v_GS_COMPUTER_SYSTEM.Domain0,v_R_SYSTEM.location0, v_R_SYSTEM.description0,v_GS_COMPUTER_SYSTEM.NumberOfProcessors0,
v_GS_PROCESSOR.AddressWidth0,v_GS_PROCESSOR.name0,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,v_GS_OPERATING_SYSTEM.Caption0,v_GS_OPERATING_SYSTEM.CSDVersion0,
v_RA_System_IPAddresses.ip_addresses0 = REPLACE(
(
SELECT ip_addresses0 AS [data()]
FROM v_RA_System_IPAddresses
where v_RA_System_IPAddresses.resourceid = v_R_System.ResourceID
ORDER BY v_RA_System_IPAddresses.ip_addresses0 FOR XML PATH ('')
), ' ', ',' )
from v_R_System
LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_PROCESSOR ON v_GS_PROCESSOR.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_X86_PC_MEMORY ON v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_PC_BIOS ON v_GS_PC_BIOS.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_RA_System_IPAddresses ON v_RA_System_IPAddresses.ResourceID = v_R_System.ResourceID
where v_R_SYSTEM.operating_system_name_and0 LIKE '%Server%'
order by v_R_SYSTEM.name0
ASKER
I was able to get this working when it was not a part of my full SQL statement. I am trying to manipulate my original with this piece of code but still receiving the same error.
SELECT Distinct v_RA_System_IPAddresses.resourceid, ip_addresses0 = REPLACE(
(
SELECT ip_addresses0 AS [data()] FROM v_RA_System_IPAddresses t WHERE t.resourceid = v_R_System.ResourceID ORDER BY ip_addresses0 FOR XML PATH ('')
), ' ', ', ' )
from v_RA_System_IPAddresses
LEFT OUTER JOIN v_R_System ON v_R_System.ResourceID = v_RA_System_IPAddresses.resourceid
where v_RA_System_IPAddresses.resourceID = v_R_System.ResourceID
are you running this in sql server 2000 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check this
;with CTE as (
select distinct ResourceID,
rtrim(substring(isnull((select ','+IP_Addresses0
from v_RA_System_IPAddresses t1 where t1.ResourceID = t2.ResourceID for xml path('')),' '),2,2000)) as IP_Addresses0
from v_RA_System_IPAddresses t2)
select distinct v_R_System.Name0,
v_GS_COMPUTER_SYSTEM.Manufacturer0,v_GS_COMPUTER_SYSTEM.Model0,v_GS_PC_BIOS.SerialNumber0,
v_GS_COMPUTER_SYSTEM.Domain0,v_R_SYSTEM.location0, v_R_SYSTEM.description0,v_GS_COMPUTER_SYSTEM.NumberOfProcessors0,
v_GS_PROCESSOR.AddressWidth0,v_GS_PROCESSOR.name0,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,v_GS_OPERATING_SYSTEM.Caption0,v_GS_OPERATING_SYSTEM.CSDVersion0,
v_RA_System_IPAddresses.ip_addresses0
from v_R_System
LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_PROCESSOR ON v_GS_PROCESSOR.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_X86_PC_MEMORY ON v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_PC_BIOS ON v_GS_PC_BIOS.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN CTE ON v_RA_System_IPAddresses.ResourceID = v_R_System.ResourceID
where v_R_SYSTEM.operating_system_name_and0 LIKE '%Server%'
order by v_R_SYSTEM.name0
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
aneeshattingal and Sharath_123 both provided workable solutions. Thank you!
ip = REPLACE(
(
SELECT IP AS [data()] FROM Table1 t1 WHERE t1.Server = t.server ORDER BY ip FOR XML PATH ('')
), ' ', ',' )
FROM Table1 t