Summary

In generic SQL, there is a need to join results generated by multiple queries in sql_queries to create a single event. This will enable creating a single event for cases, where multiple individual queries are executed to extract the specific metrics.

For example:

sql_queries:
  - query: "SELECT cntr_value As 'user_connections' FROM sys.dm_os_performance_counters WHERE counter_name= 'User Connections'"
    response_format: table

  - query: "SELECT cntr_value As 'buffer_cache_hit_ratio' FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio' AND object_name like '%Buffer Manager%'"
    response_format: table

The above two queries will create two separate events one for each query, currently each with a single metric. It is desired to create single event with 2 of the metrics together.

Before diving into the solution space, let’s first summarise the current behaviour of SQL module.

Current behaviour

sql_queries have two response formats table and variables.

• In table response format, for results with X rows and Y columns as below, where Cy is column name and Vxy is the value:

C1, C2, …..  CY
V11, V12,….. V1Y
V21, V22,….. V2Y
.....
.....
VX1,VX21….. VXY

Here, X events are generated one for each row. The event is of the format: ( “C1: V11”, “C2:V12”, …. “CY:V1Y”)

• In the variable response format, results with 2 columns and X rows:

N1,V1
N2,V2
…
...
NX,VX

Here single event is generated of the format: (“N1:V1”, “N2:V2”…..”NX:VX”)

Solution feasibility

1. It may be possible to merge multiple table queries together if each query produce X rows. In such a case X events can be created by merging each column of the corresponding table together.
2. It may NOT be possible to merge multiple table queries together if each query produce different number of rows.
3. It may be tricky to merge table and variable results together, but need exploration.
4. Merging poses a potential challenge if two results sets have overlapping column name.
5. Merging can be seamless if two results sets have divergent set of column names.

Solution details

TBD