Drift Report based on reference machine

I've been tasked with building a drift report based on reference machines. Specifically I need a list of applications and their versions that are on specific reference servers and then generate reports based on other servers in a group that are missing applications, have extra applications, or have their applications at different versions.

I found information on how to create reports based on specific variables or hard coded version numbers, but I want to populate the application list and version numbers from a reference machine.

To put it in code terms, I want a report similar to the following, but with the "Where" clause being populated with the list of applications and their versions installed on the reference server.

select
computers.Computerid,
Computers.Name as "Computer Name",
Computers.Description,
Applications.Name as "Application Name",
Applications.Version as "Application Version"
from Computers

INNER JOIN Applications USING (ComputerID)
WHERE (
(Applications.Version < "80" AND Applications.Name like 'Google Chrome%')
OR (Applications.Version < "4.0" AND Applications.Name like 'Duo Auth%')
OR (Applications.Version < $AppVerMozillaFirefox64 AND Applications.Name like 'Mozilla%')
OR (Applications.Version = $AppVerNotepad++64 AND Applications.Name = $AppNameNotepad++64 )
)
AND <ComputerFilter>
ORDER BY Computers.Name