How do you join two Sentinel auxiliary tables for six months of data?

Why the Answer Is Correct

Microsoft Sentinel auxiliary (and Basic) tables are optimized for low-cost, high-volume logs, but their queries are limited to a single table; JOIN, find, search, and externaldata are not supported directly. Because Workspace1 stores Table1 and Table2 as custom auxiliary tables, the only low-effort path is to run a search job for each table to materialize the last six months into new Analytics tables with the _SRCH suffix. After both search jobs finish, a normal KQL JOIN can be run against the resulting Log Analytics tables, and the temporary _SRCH tables should then be deleted to avoid unnecessary storage and retention charges. The 12-year retention period does not remove the auxiliary-table query limitation, so retention alone is not a reason to change table plans.

Why Other Candidate Answers Are Wrong

A single query that joins Table1 and Table2 directly is invalid because the auxiliary table restriction blocks cross-table operators. Permanently promoting both tables to Analytics would enable joins but increases ingestion and retention cost and administrative overhead, which violates the minimize-cost and effort requirement. Using the search operator or externaldata as a substitute does not bypass the restriction for auxiliary tables and adds complexity. Leaving the _SRCH tables in place after correlation may seem harmless, but those tables consume workspace storage and can add cost, so cleanup is part of the correct sequence.

Community Comment Notes

Frippy's sequence — "Run two search jobs", query the Log Analytics tables, then delete the _SRCH tables — matches the documented workaround. Staim reinforces the key rule with "Basic or Auxiliary table queries are limited to a single table", and explains that the _SRCH tables become the Analytics-side inputs for the JOIN. The community discussion also points to Azure Monitor documentation on basic logs, supporting the idea that this is a platform limitation rather than a KQL syntax issue. No commenter suggested a direct JOIN or permanent table promotion as a cost-effective solution.