Geneos - How to compare the values between 2 dataview columns?

The Gateway-SQL plugin can be used to find similar values, exact values or values not in another dataview's column.

Please note:

1-Depending on the number of table cells it needs to compare between the 2 columns, this type of query may be expensive in terms of performance and may take some time to run. 

2-Due to the above reason, you may consider configuring the sampler on purpose NOT to sample on startup and NOT to run on its own by setting the sampling interval to 0. To run the sampler manually, click on the "Sample now" or refresh icon in the dataview.

To illustrate, if I have the following Table1 and Table2 Toolkit dataviews:

Case 1: List the values in the NAME column of Table1 which contain the values from the name column of Table2.

The query can be configured as: 

select s.*, e.* from Table1 AS s, Table2 as e
where s.NAME like '%' || e.name || '%'

The matching values are shown in the below dataview:

The above dataview shows all table values from Table1 which the "NAME" column contains the value of "name" column from Table2. For example, NAME=adapterSF123abc contains name=adapterSF.

Case 2: List the values in the NAME column of Table1 which match exactly with the values from the name column of Table2.

The query can be configured as: 

select s.*, e.* from Table1 AS s, Table2 as e
where s.NAME = e.name

The matching values are shown in the below dataview:

Case 3:  List the values in the name column of Table2 which do not show up in the NAME column of Table1.

The query can be configured as: 

SELECT * from Table2
WHERE NOT EXISTS
(select * from Table1 
where NAME like '%' || Table2.name || '%')

The non-matching values are shown in the below dataview:

Further Reading

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