How to do RIGHT OUTER JOIN with Lookup table?

[pm771]

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?

[renjith_nair]

@pm771,

One of the approaches is

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

|inputlookup ID|eval count=0, source="lookup"
|append [search index="your index" | stats count by ids|eval source="events" ]
|stats sum(count) as count,values(source) as source by 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

[pm771]

@renjith_nair ,

Thank you very much for your reply.

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