Cross-Search Table Join

Overview

Use this option to combine and compare the data from two or more separate tables that share a common field.

What data do I need for this widget?

In order to generate the cross-search table join, you need to run at least two queries with at least three columns, one of them numerical and one of them in common. Furthermore, those queries need to have the data grouped for the diagram to show meaningful data.

Creating a Cross-Search Table Join

1. Go to Data Search, open at least two queries and perform the necessary operations.

2. Click the gear icon on the toolbar and select Graphical Correlation → Cross-search table join.

3. Click the window symbol next to the unknown table name to add the variables for a second table. Be aware that after the first column is added, the unknown name will become the name of the table that contains the column.

4. Here is the set of variables the diagram needs to be constructed. The Target node of the first table is linked to the Source node of the following. This is designed to add the columns that are shared by both tables so Devo can use them as the central variable to merge the two tables. They need to have the same name or have the same data type and structure, otherwise they will not be accepted.
   Click and drag the columns from the table and drop them onto the corresponding variables. Then, select the other query in the navigation pane and repeat the process.
   
   

   

   • If you accidentally added to your diagram workspace more tables than you needed or you do not want them for whatever reason, you can remove them. To do that, click the cross next to the table name.
   • If you added a column by mistake or simply want to substitute it by a different one, you can either drag another column onto it or remove it first by clicking the cross inside the box next to the name.
   • If you try to add an incompatible column, an error message will appear below the table variables before releasing the mouse. This error message explains the reason for the incompatibility.
5. The new table appears. The rows from different tables whose data can be correlated through a common column are merged together into a single row.
   
   
6. If you click the stats switch, the legend explaining the rows contained in the table will be hidden.
7. Clicking the inner join switch will split the merged rows and show their data separately instead of correlated.
8. To modify the columns used to construct the diagram, click the settings button at the top right corner of the table window .

9. You can download your cross-search table join as a CSV file by clicking the Export data as CSV button at the top right corner of the table window .
   

Query example

You can use the following queries to recreate the example shown in the images above:

from siem.logtrust.web.activity
  group every 5m by responseLength, username, correlationId
  every 5m
  select count() as count

from siem.logtrust.web.navigation
  group every 5m by userEmail, action, srcPort
  every 5m
  select count() as count