It may be worthwhile to look a bit more at that last query, to understand how data flows.

This diagram shows what happens:

Figure 12. Data Flows

With groupBy(),

But say you're not interested in a simple aggregate value. You're interested in whole events, with some fields added from some other event, the two kinds of events linked by some key field.

To make it concrete: Occasionally in some user session, we get an InterestingEvent and would like to know some context — say, the IP address of the client— but that context is only logged in connection with the Login event.

To a certain extent, we can still use the same technique. For the aggregator for the InterestingEvent case, we use tail() — which collects the last N events.

groupBy(sessionID, function=[
  { eventType="InterestingEvent" 
| tail(100) },
  { eventType="Login" 
| selectLast(clientIP) }
])
| eventType="InterestingEvent"

Here we collect up to 100 InterestingEvents per session. When using groupBy() to correlate events, there must be some limit to what we gather; whether that's a problem depends on the use case. If you must always find all relevant events, you may need an actual join query.

By the way, what's up with the final filter on eventType?

Well, what groupBy() constructs is an outer join, so it would still output rows for entities which were spawned but which never emitted any InterestingEvents — that is, where the first (tail()) aggregator had found nothing, but the second (selectLast()) had. Such rows would therefore only contain the fields sessionID and clientIP.

A more general way to ensure we get an inner join — or left or right, for that matter — is to simply count each kind of event, then filter on the counts:

groupBy(sessionID, function=[
  { eventType="InterestingEvent" 
| [tail(100), count(as=c1)] },
  { eventType="Login" 
| [selectLast(clientIP), count(as=c2)] }
])
| c1 > 0 
| c2 > 0

This can also be used to answer questions like "has event type X happened more than once in a single session?".

Let's try a slight variation: there is some ID we can correlate by, but groupBy() does not seem applicable because the ID happens to be in different fields in the two events we're interested in connecting.

More concretely, let's say our domain includes some tree-like structure where entities have parent-child relations; let's say the parent logs some Spawn event with myID and childID fields; and that the child logs some InterestingEvent event with a myID field.

We'd like to know, for any InterestingEvent event, what the parent ID was.

Obviously we can't simply group by myID, because the link we're interested in is between a childID in one event and a myID in another event.

We will still use groupBy(), but we need to establish a usable key first:

eventType match {
  Spawn => key := childID;
  InterestingEvent => key := myID;
}
| groupBy(key, function=[
  { eventType="InterestingEvent" 
| tail(100) },
  { eventType="Spawn" 
| selectLast(myID) 
| rename(myID, as=parentID) }
])
| eventType="InterestingEvent"

Experienced users of LogScale may know that there is a qualitative difference between the first part of a query and the last part. And that the boundary goes exactly at the first aggregating function.

Before that function, we have the Stream.

From that stream, the first aggregator catches the State. (You can think of it as a kind of fish.)

And after that aggregator, we take the State and do further stuff.

The key point is that we start with something unmanageable, and we can't take all of it with us further. We need to distill it somehow, into something of a manageable size.

Now what bearing does this have on join queries?

It's what we could call the Ghostbuster Rule of log searching:

Ghostbuster Rule:You Can't Cross Streams.

That is: you can join a state and a state — all of the queries so far have done exactly that — and you can join a stream and a state. But you can't join two streams.

Now, back to joins.

The way join functions work in LogScale is remarkably simple: First you do one thing, then you do another.

More concretely: first you visit one Stream, to eventually leave with some State. Then you visit another Stream (obviously another one, because you can't visit the same stream twice), bringing your acquired State to guide your search there.

This already tells us something about using join(): whichever part-query we do first must result in a State of sufficiently small size (and smaller is usually better).

Thus, if we want to correlate a rare kind of event with a common kind of event, we're better off searching for the rarer kind first.

And if neither of the two things we want to correlate are rare in themselves — say, we are a provider of a streaming video service and want to find users who suspiciously have watched both Star Trek and Star Wars, neither of which would in itself be a rare event as such — then things get further interesting.

By the way, the "first do one thing, then do another" also tells us that live join queries are not generally possible — those are examples of trying to join two streams (unless special circumstances apply, at least).

Let's now revisit another query: the one where we found response times higher than a certain threshold.

Suppose we'd like to know exactly how many such instances there are — then our method of just finding the N worst won't suffice.

Starting with the variant where we have separate thresholds per actionType, a join query could look like this:

join(
  { groupBy(actionType, function=[avg(responsetime), stddev(responsetime)])
    
| threshold := _avg + 2 * _stddev
  }, // The output of this is a table with "actiontype" and "threshold" columns
  key=actionType,
  include=[threshold]
]
| test(responsetime > threshold)
| groupBy(actionType, function=count())

If we don't want separate thresholds, just one global one, we run into a problem: join() does not (at the moment at least) allow for there to be zero join keys.

That's not too much of a problem though — we just add a dummy key field. It may look a bit silly, but it gets the job done:

dummyKey := ""
| join(
  { [avg(responsetime), stddev(responsetime)]
    
| threshold := _avg + 2 * _stddev
    
| dummyKey := ""
  },
  key=dummyKey,
  include=[threshold]
]
| test(responsetime > threshold)
| count()

Now that we know a bit about what to do when one of the things to join is a) rare, b) has a fixed set of fields we're interested in carrying over to the join result, and c) is something it's OK we only get to see if it's correlated with one or more events in the other join party — how, then, to proceed when that isn't the case?

To illustrate one tool which may come to the rescue, let's look at a question where the problem is that neither party to the join is a sufficiently small result set that it fits into State constraints: finding streaming-video users which, suspiciously, have been watching both Star Wars and Star Trek.

What we can do in this case is to use two passes over the data (like in a join query), but to use the first pass merely to get a good idea about which join keys are in fact relevant.

Then, in the second pass, we gather the data with a groupBy() as shown earlier — with the flexibility that entails — but because we know a bit about the key set, we won't need to keep track of as many groups.

The new tool here is a function called selfJoinFilter(). Let's see it in action — in its idiomatic place, as a filter before a groupBy():

selfJoinFilter(userID,
  where=[{videoID="Star Trek"}, {videoID="Star Wars"}],
  prefilter=true
)
| groupBy(userID, function=[
  { videoID="Star Trek" 
| [count(as=c1), max(@timestamp, as=lastWatched1)] },
  { videoID="Star Wars" 
| [count(as=c2), max(@timestamp, as=lastWatched2)] }
  ])
| c1 > 0 
| c2 > 0

It should be noted that even this method begins to struggle if, for both types of events, there are many keys (here, users) which have that event type.