Skip to main content

V1 - Processing - except

except​

  • except {variable}=rightTable

The command except means removing rows from the current table that match the rightTable with the specific columns. The target of the command is the current table.

Example:

function previouslySeen()
  search {from="-28d<d",to="-21d<d"} sStartswith("@cloudtrail.eventName","Run") or sStartswith("@cloudtrail.eventName","Create")
  let {sourceIPAddress}=f("@cloudtrail")
  aggregate old=count() by sourceIPAddress
end

function recentlySeen()
  search {from="-21d<d",to="-14<d"} sStartswith("@cloudtrail.eventName", "Run") or sStartswith("@cloudtrail.eventName","Create")
  let {eventName, awsRegion, sourceIPAddress, eventTime } = f("@cloudtrail")
  let {city,country} = f("@cloudtrail._ip")
  aggregate firstSeen=min(eventTime), lastSeen=max(eventTime), records=count() by sourceIPAddress, city, country, eventName
  sort 100 records
end

stream previous=previouslySeen()
stream recent=recentlySeen()

except {sourceIPAddress}=previous

The result table "previous", "recent" and the last (current) table are shown above. Obviously, both of "previous" and "recent" have a column (variable) named "SourceIPAddress" but the members of this variable are different between the two tables. Other columns are also different. The last command except {sourceIPAddress}=previous looks up each element of "SourceIPAddress" of "previous" (rightTable) in the same column of the current table ("recent"), and removes the whole row of "recent" where the element of "SourceIPAddress" of "SourceIPAddress" matches the one of "recent".