question

JS-0730 avatar image
0 Votes"
JS-0730 asked saldana-msft edited

SCCM SQL Query multiple columns aggregation

Hi,
I have the SCCM Following SQL query that returns 1 IP address for each column, I wanted to collect all IPs for a single column (separated by semicolon ) so I don't have machine name duplicates.
Any suggestions?

 SELECT
    DISTINCT(CPU.SystemName0) AS [ServerName],
    IPA.IP_Addresses0 AS [IPAddress],
    CS.Manufacturer0 AS [Manufacturer],
    CS.Model0 AS [Model],
    PCB.SerialNumber0 AS [SerialNumber],
    PM.TotalPhysicalMemory0 AS [Memory]
 FROM [dbo].[v_GS_PROCESSOR] CPU
    LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM CS
 ON CS.ResourceID = CPU.ResourceID
    LEFT OUTER JOIN dbo.v_GS_PC_BIOS PCB
 ON CS.ResourceID = PCB.ResourceID
    LEFT OUTER JOIN dbo.v_GS_X86_PC_MEMORY PM
 ON CS.ResourceID = PM.ResourceID
    LEFT OUTER JOIN dbo.v_RA_System_IPAddresses IPA
 ON CS.ResourceID = IPA.ResourceID
 GROUP BY
 CPU.SystemName0,
     IPA.IP_Addresses0,
     CS.Manufacturer0,
     CS.Model0,
     PCB.SerialNumber0,
     PM.TotalPhysicalMemory0
 ORDER BY [ServerName] ASC



sql-server-generalmem-cm-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AllenLiu-MSFT avatar image
0 Votes"
AllenLiu-MSFT answered AllenLiu-MSFT commented

@JS-0730
Thank you for posting in Microsoft Q&A forum.
Try this query:

 SELECT
     DISTINCT(CPU.SystemName0) AS [ServerName],
  STUFF((SELECT ', ' + IPA.IP_Addresses0
           FROM v_RA_System_IPAddresses  IPA
           WHERE IPA.ResourceID = CPU.ResourceID
           FOR XML PATH('')), 1, 1, '') [IPAddress],
     CS.Manufacturer0 AS [Manufacturer],
     CS.Model0 AS [Model],
     PCB.SerialNumber0 AS [SerialNumber],
     PM.TotalPhysicalMemory0 AS [Memory]
  FROM [dbo].[v_GS_PROCESSOR] CPU
     LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM CS
  ON CS.ResourceID = CPU.ResourceID
     LEFT OUTER JOIN dbo.v_GS_PC_BIOS PCB
  ON CS.ResourceID = PCB.ResourceID
     LEFT OUTER JOIN dbo.v_GS_X86_PC_MEMORY PM
  ON CS.ResourceID = PM.ResourceID
     LEFT OUTER JOIN dbo.v_RA_System_IPAddresses IPA
  ON CS.ResourceID = IPA.ResourceID
  ORDER BY [ServerName] ASC



If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, worked great.

0 Votes 0 ·

I have a problem with the SSIS package when exporting data to flat file using the Query, I get: "Info_csv.Inputs[Flat File Destination Input].Columns[IPAddress]" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component." - Can you help?




0 Votes 0 ·

Sorry I'm not very familiar with SSIS package, it's recommended to post a thread in SQL Server forum to get a dedicated support.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

If you want a comma-separated list of distinct IP addresses, then try something like this:

 select string_agg([IPAddress], ', ') as IPAddresses
 from
 (
     select distinct [IPAddress]
     from
     (
         Your query without ORDER BY
     ) d
 ) d

Or give details if you need something else. (Maybe show the current and desired results).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.