Table Functions - join

join

• join A on B //A and B are the names of columns

OR

• join {c1=”col1”}=rightTable on leftTable(,col2…) // rightTable and leftTable are the names of tables

inner join

Taking the first expression as example, the command join is used to merge a field A to the current table, when B is part of both A and current table (the order may be different). The order of each columns of A in the new table is according to the order of B.

function  loadDevices()
    load resource FEHxDevice
    let {uuid="_id", ad_common_names, hostname,last_poll_ip,last_poll_timestamp,primary_ip_address, osName="os.product_name"}=f("@FEHxDevice")
  end
  
  function loadUniqueDevices()
    load resource FEHxDevice
    let {uuid="_id", ad_common_names, hostname,last_poll_ip,last_poll_timestamp,primary_ip_address, osName="os.product_name"}=f("@FEHxDevice")
    aggregate last_poll_timestamp=max(last_poll_timestamp) by hostname
  end
  
  function  loadAgents()
    load resource sentinelOneAgent
    let {uuid, computerName} = f("@sentinelOneAgent")
  end
  
  stream  devices=loadDevices()
  stream  uniqueDevices=loadUniqueDevices()
  join devices on hostname, last_poll_timestamp

In the example above, the table “devices” contains many basic information. In the current table(“uniqueDevices”), the original data, “hostname” and “last_poll_timestamp” from “devices”, are reordered by aggregate. The command join devices on hostname, last_poll_timestamp adds the other columns of “devices” to the right of “hostname” and “last_poll_timestamp” of the current table, and the added columns are reordered according to “hostname” and “last_poll_timestamp” of the current table.

outer join

Take the union of A and B.