topic Re: How to do RIGHT OUTER JOIN with Lookup table? in Splunk Search

How to do RIGHT OUTER JOIN with Lookup table?

pm771 — Tue, 07 Jul 2020 01:14:39 GMT

Events stream has ID field in every record.

There is a lookup table with a small subset of IDs.

The task is to calculate the total number of occurrences for each ID from the lookup table for every 15 min.

It is possible that certain IDs from the table will not be found. In such cases they should still be included in the result with the count of zero.

SQL version:

SELECT ID, COUNT(ID)
FROM Events e
RIGHT JOIN Lookup l ON l.ID=e.ID
GROUP BY I.ID

What would be a good Splunk way to achieve the same?

Re: How to do RIGHT OUTER JOIN with Lookup table?

renjith_nair — Tue, 07 Jul 2020 02:32:55 GMT

@pm771,

One of the approaches is

Assuming you have a lookup "ID" with set of "ids"

Source is added to distinguish between the sources , you may remove it

If the result looks good, we shall further filter it by using the source

Re: How to do RIGHT OUTER JOIN with Lookup table?

pm771 — Tue, 07 Jul 2020 13:24:37 GMT

@renjith_nair ,

Thank you very much for your reply.

I was considering an`append` approach but did not come with `count()` and `sum()` combination.