[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 881
  • Last Modified:

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!



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

Open in new window

0
STEHelp
Asked:
STEHelp
  • 3
  • 3
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT SERVER, Processor ,   OS ,
       ip = REPLACE(
            (
              SELECT IP AS [data()]  FROM Table1 t1 WHERE t1.Server = t.server ORDER BY ip FOR XML PATH ('')
            ), ' ', ',' )
FROM Table1 t
0
 
STEHelpAuthor Commented:
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'.

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

Open in new window

0
 
STEHelpAuthor Commented:
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

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Aneesh RetnakaranDatabase AdministratorCommented:
are you running this in sql server 2000 ?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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,
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
0
 
SharathData EngineerCommented:
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

Open in new window

0
 
SharathData EngineerCommented:
corrected the aliases.
;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, 
CTE.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 CTE.ResourceID = v_R_System.ResourceID   

where v_R_SYSTEM.operating_system_name_and0 LIKE '%Server%'
order by v_R_SYSTEM.name0

Open in new window

0
 
STEHelpAuthor Commented:
aneeshattingal and Sharath_123 both provided workable solutions.  Thank you!

0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now