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
Store

Inclusion of STRING_AGG into SWQL

It would be useful to include the STRING_AGG function supported since SQL 2016 (I think) into the accepted set of functions. Using STRING_AGG makes it a lot easier to aggregate the values of multiple rows into one cell (useful in an alert that lists out all of the dependencies of a node in a comma-separated fashion, as a standard SWQL query variable just lists the result from the first row).

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16

It converts a list of rows into a single cell.

Example without String_agg(), single column, multi-row:

Syed
Catherine
Kim
Hazem
...

Example with String_agg(), it converts it into a single cell:

Syed,Catherine,Kim,Hazem,...

This would allow for some interesting and complex Widgets, Reports, and Alerts.

Examples:

a Query for Nodes with a column showing group membership (comma delimited). This could then be used to filter with group membership CONTAINS X.

Find more posts tagged with

string_agg

aggregation

SWQL

Status: None

Comments

adam.beedell

YESSSSS

adam.beedell

Alerting is the main use case for this that comes to mind, but I think this would be valuable for modern dashboards too, I may come back to that on a other day

In alerting you are locked per macro to one cell per result, if you need to produce complex output you either need to produce a large series of pre-programmed (and potentially synced) macros (all of which cannot return null, which means potentially CASE or UNION clauses each time), or you need to use SQL Macros with FOR XML PATH('') which feels very much like a workaround, and involves all the SWQLoverSQL risks unless you lay things out very carefully.

The FOR XML PATH stuff is laid out on the forum, but it's also a dark art and not well understood, and hard to read. I spent a while this year trying to produce a clear learning example and I struggled to do so

String_Agg is a major step toward making rich output accessible to a larger proportion of your user base (and making it easier for us few who know how to do it the hard way, but need to give sessions about it every time the team changes!)

Abstract example:

- Any useful 1D array of unknown size cannot be easily output at the moment.
- Any useful 2D array of known size would be much easier to output as a series of concatenated string_aggs

Less abstract example

- Please give me a list of other servers impacted by this outage in the email
- Please list off the drives (vmdisk) on that vm
- Please give me the support contacts for everything impacted by this outage
- Please give me all the varbinds for that trap
- Please list the interfaces that might be related on that nexus

etc etc

Over time the ability to output natively 1D, 2D arrays of unknown size easily and also things like images (as images) would allow the users to modernize and enrich comms significantly without needing SW engineers to build all of that themselves

tobyw_loop1