innerjoin

Prerequisite

  1. Download the function from here (javascript file)

  2. Save it you XPOLOG_HOME/conf/ext/scripts/complex/user/ 
    Note1: XPOLOG_HOME refers to the installation directory of your XpoLog or the External-Configuration directory if used in the environment.
    Note2: XPOLOG_HOME In case the directory 'user' does not exist in your environment in the above path, create it.

  3. Restart XpoLog to load the function.  

 

Synopsis

The innerjoin function is used to combine events from two logs, based on related column(s) between them.
The "keys" are the column(s) name(s) that are used to join the logs data. The function returns events that have matching values in both logs.

Syntax

innerjoin "Sources=[SOURCE_LOG_NAME];SourceKeys=[SOURCE_KEY_COLUMN_NAME_1],[SOURCE_KEY_COLUMN_NAME_2],...,[SOURCE_KEY_COLUMN_NAME_N];SourceColumns=[SOURCE_COLUMN_NAME_1],[SOURCE_COLUMN_NAME_2],...,[SOURCE_COLUMN_NAME_N];Targets=[TARGET_LOG_NAME];TargetKeys=[TARGET_KEY_COLUMN_NAME_1],[TARGET_KEY_COLUMN_NAME_2],...,[TARGET_KEY_COLUMN_NAME_N];TargetColumns=[TARGET_COLUMN_NAME_1],[TARGET_COLUMN_NAME_2],...,[TARGET_COLUMN_NAME_N]"

Required Arguments

[SOURCE_LOG_NAME] - the first log that will be used for join

[SOURCE_KEY_COLUMN_NAME_1],[SOURCE_KEY_COLUMN_NAME_2],...,[SOURCE_KEY_COLUMN_NAME_N] - the column(s) name(s) from the [SOURCE_LOG_NAME] that are used to join the events from the [TARGET_LOG_NAME]

[SOURCE_KEY_COLUMN_NAME_1],[SOURCE_KEY_COLUMN_NAME_2],...,[SOURCE_KEY_COLUMN_NAME_N] - the column(s) name(s) from the [SOURCE_LOG_NAME] that will be displayed in the joined result

[TARGET_LOG_NAME] - the second log that will be used for join

[TARGET_KEY_COLUMN_NAME_1],[TARGET_KEY_COLUMN_NAME_2],...,[TARGET_KEY_COLUMN_NAME_N] - the column(s) name(s) from the [TARGET_LOG_NAME] that are used to join the events from the [SOURCE_LOG_NAME]

[TARGET_COLUMN_NAME_1],[TARGET_COLUMN_NAME_2],...,[TARGET_COLUMN_NAME_N] - the column(s) name(s) from the [TARGET_LOG_NAME] that will be displayed in the joined result

 

  • Note: All the required parameters are case sensitive, make sure to specify the exact names as defined in the logs.

 

Example

Let's look at a selection from the "Orders" log:

 

OrderID

CustomerID

OrderDate

OrderID

CustomerID

OrderDate

10308

2

2019-09-18

10309

3

2019-09-19

10310

1

2019-09-20

 

Then, look at a selection from the "Customers" log:

 

CustomerID

CustomerName

ContactName

Country

CustomerID

CustomerName

ContactName

Country

1

Alfreds Futterkiste

Maria Anders

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

France

3

Antonio Moreno Taquería

Antonio Moreno

USA

 

Notice that the "CustomerID" column in the "Orders" log refers to the "CustomerID" in the "Customers" log. The relationship between the two log samples above is the "CustomerID" column.

Then, we can create the XpoLog query that selects records that have matching values in both tables:



*  in log.orders,customers | innerjoin "Sources=Orders;SourceKeys=CustomerId;SourceColumns=OrderId,OrderDate;Targets=Customers;TargetKeys=CustomerId;TargetColumns=CustomerName,ContactName,Country"

 

Â