Geneos - How do I reformat existing dataviews and group multiple rows of data together?

We have picked the Gateway-Managed Entities Data plugin as an example for the source data.

We are interested in rows that are reported as Warning or Critical severity. In this example, there are 2 in Warning severity and 1 in Critical severity as highlighted above.

We will create a new dataview using the Gateway-SQL plugin. At the Tables option, it takes the XPath of the original dataview like a database table. We only need to define 1 table here and we use T1 as the table name.

At the Views option, it accepts SQL syntax to manipulate data from the source. The GROUP BY clause is used to combine the data based on severity. The group_concat() function merges the ME names to a single row, and we use ', ' as separator.

SELECT severity, group_concat(name, ', ') AS me_names, count(*) AS me_count
FROM T1
GROUP BY severity

The resulting dataview looks like the below. As you can see, the Managed Entities are grouped nicely based on the severity. Additional gateway rules can be applied based on this new view.

Further Reading

Users can check out the Gateway-SQL section in gateway documentations. The SQL statements are based on SQLite, which is described at https://www.sqlite.org/lang.html.