Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Trying to Smash Multiple Values (of the same field) into One Output Field

I am trying to add additional information about KBs installed on Servers into our ISM import (all of the nodes, their status, etc).

I can see that I get this information from Orion.AssetInventory.OSUpdates
However, it lists each KB on a row per nodeid and I want to instead join this query into an existing one to be a single field.

Here is the basic SWQL query:

SELECT top 100
NodeID
,Name
--,Concat(osu.Name, ';') as [Installed_KBs]
FROM Orion.AssetInventory.OSUpdates osu
group by NodeID, osu.Name

The query returns:

NodeID	Name
12345	KB3199986
12345	KB4013418
12345	KB4049065

What I want for an output from the query is:

NodeID	Name
12345	KB3199986;KB4013418;KB4049065;

I would expect that Concat is the way to go but I don't know how (or if) I can get all values for each node and put them together.

Alternatively, I guess I could run this as a Powershell query and try to append that to a CSV with the rest of the inventory but I feel that has a higher chance of causing issues.

Find more posts tagged with

Accepted answers

All comments

Seashore

Hi, I don't think that is possible in SWQL but in SQL it's doable. I would try to use a "FOR XML PATH" to solve this. Think that will work.

adam.beedell

in SQL it's FOR XML PATH

In SWQL this is a right pain, you can use a combination of JOIN, subqueries, and CONCAT. I call it the poor man's Transpose @KMSigma.SWI put some content together on this topic recently.

You turn it from

NodeID	Name
12345	KB3199986
12345	KB4013418
12345	KB4049065

to (can kinda skip the joiner bit given the same nodeID i think, this stuff is hard to think about and display as different. It's in the section of my mind that's like 11pm-mad-science-mode-only)

NodeID	Name	Joiner
12345	KB3199986	1
12345	KB4013418	1
12345	KB4049065	1

NodeID	Name1	Name2	Name3
12345	KB3199986	KB4013418	KB4049065

Then CONCAT(Name1,', ',Name2,', ',Name3') AS combined

tporritt

As I intend to use this in a report, SQL would work however I am not familiar with TSQL, I asked ChatGPT but it doesn't have a good handle on SWQL to convert it. Here is what I got for the joined field but it doesn't work and I don't know where to start with it.

SELECT
NodeID,
Name,
Installed_KBs = STUFF((
SELECT ', ' + osu.Name
FROM [dbo].[AssetInventory.OSUpdates] osu
WHERE osu.NodeID = main.NodeID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM
[dbo].[Nodes] AS main
GROUP BY
NodeID, Name