innerjoin
Prerequisite
Download the function from here (javascript file)
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.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 |
---|---|---|
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 |
---|---|---|---|
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"
Â
Â