SELECT (SELECT Computers.Name FROM Computers WHERE Computers.ComputerId = '~Applications'.ComputerID) as ComputerName,'~Applications'.Name, '~Applications'.Version AS InstalledVersion,(SELECT SystemVariables.Value from SystemVariables WHERE Name = 'AppVerAdobeReaderDC') As CurrentVersion
FROM
'~Applications'
where '~Applications'.ComputerId IN
(
	SELECT Computers.ComputerId
	FROM Computers
	WHERE Computers.ComputerId IN
		(SELECT CollectionComputers.ComputerId
		FROM CollectionComputers

		WHERE CollectionComputers.CollectionId IN
			(SELECT Collections.CollectionId
			FROM Collections
			WHERE Collections.Path = 'Collection Library\Applications\PDF Apps\PDF Readers\Adobe Reader\Adobe Reader DC\Adobe Reader DC (Old)'
			)
			AND CollectionComputers.IsMember = 1
		)
)
AND
LOWER('~Applications'.Name)  REGEXP (SELECT LOWER(Value) FROM SystemVariables WHERE Name= 'AppNameAdobeReaderDC')

UNION ALL

SELECT (SELECT Computers.Name FROM Computers WHERE Computers.ComputerId = '~Applications'.ComputerID) as ComputerName,'~Applications'.Name, '~Applications'.Version AS InstalledVersion,(SELECT SystemVariables.Value from SystemVariables WHERE Name = 'AppVerAdobeReaderXI') As CurrentVersion
FROM
'~Applications'
where '~Applications'.ComputerId IN
(
	SELECT Computers.ComputerId
	FROM Computers
	WHERE Computers.ComputerId IN
		(SELECT CollectionComputers.ComputerId
		FROM CollectionComputers

		WHERE CollectionComputers.CollectionId IN
			(SELECT Collections.CollectionId
			FROM Collections
			WHERE Collections.Path = 'Collection Library\Applications\PDF Apps\PDF Readers\Adobe Reader\Adobe Reader XI\Adobe Reader XI (Old)'
			)
			AND CollectionComputers.IsMember = 1
		)
)
AND
LOWER('~Applications'.Name)  REGEXP (SELECT LOWER(Value) FROM SystemVariables WHERE Name= 'AppNameAdobeReader')

UNION ALL

SELECT (SELECT Computers.Name FROM Computers WHERE Computers.ComputerId = '~Applications'.ComputerID) as ComputerName,'~Applications'.Name, '~Applications'.Version AS InstalledVersion,(SELECT SystemVariables.Value from SystemVariables WHERE Name = 'AppVerMozillaFirefox') As CurrentVersion
FROM
'~Applications'
where '~Applications'.ComputerId IN
(
	SELECT Computers.ComputerId
	FROM Computers
	WHERE Computers.ComputerId IN
		(SELECT CollectionComputers.ComputerId
		FROM CollectionComputers

		WHERE CollectionComputers.CollectionId IN
			(SELECT Collections.CollectionId
			FROM Collections
			WHERE Collections.Path = 'Collection Library\Applications\Internet Browsers\Mozilla Firefox\Mozilla Firefox 64-bit\Mozilla Firefox 64-bit (Old)'
			)
			AND CollectionComputers.IsMember = 1
		)
)
AND
LOWER('~Applications'.Name)  REGEXP (SELECT LOWER(Value) FROM SystemVariables WHERE Name= 'AppNameMozillaFirefox')

UNION ALL

SELECT (SELECT Computers.Name FROM Computers WHERE Computers.ComputerId = '~Applications'.ComputerID) as ComputerName,'~Applications'.Name, '~Applications'.Version AS InstalledVersion,(SELECT SystemVariables.Value from SystemVariables WHERE Name = 'AppVerGoogleChromeEnterprise') As CurrentVersion
FROM
'~Applications'
where '~Applications'.ComputerId IN
(
	SELECT Computers.ComputerId
	FROM Computers
	WHERE Computers.ComputerId IN
		(SELECT CollectionComputers.ComputerId
		FROM CollectionComputers

		WHERE CollectionComputers.CollectionId IN
			(SELECT Collections.CollectionId
			FROM Collections
			WHERE Collections.Path = 'Collection Library\Applications\Internet Browsers\Chrome Enterprise\Chrome Enterprise (Old)'
			)
			AND CollectionComputers.IsMember = 1
		)
)
AND
LOWER('~Applications'.Name)  REGEXP (SELECT LOWER(Value) FROM SystemVariables WHERE Name= 'AppNameGoogleChrome')

UNION ALL

SELECT (SELECT Computers.Name FROM Computers WHERE Computers.ComputerId = '~Applications'.ComputerID) as ComputerName,'~Applications'.Name, '~Applications'.Version AS InstalledVersion,(SELECT SystemVariables.Value from SystemVariables WHERE Name = 'AppVer7Zip') As CurrentVersion
FROM
'~Applications'
where '~Applications'.ComputerId IN
(
	SELECT Computers.ComputerId
	FROM Computers
	WHERE Computers.ComputerId IN
		(SELECT CollectionComputers.ComputerId
		FROM CollectionComputers

		WHERE CollectionComputers.CollectionId IN
			(SELECT Collections.CollectionId
			FROM Collections
			WHERE Collections.Path = 'Collection Library\Applications\Utilities\Compression Utilities\7-Zip\7-Zip (Old)'
			)
			AND CollectionComputers.IsMember = 1
		)
)
AND
LOWER('~Applications'.Name)  REGEXP (SELECT LOWER(Value) FROM SystemVariables WHERE Name= 'AppName7Zip')

UNION ALL

SELECT (SELECT Computers.Name FROM Computers WHERE Computers.ComputerId = '~Applications'.ComputerID) as ComputerName,'~Applications'.Name, '~Applications'.Version AS InstalledVersion,(SELECT SystemVariables.Value from SystemVariables WHERE Name = 'RTVerJava8') As CurrentVersion
FROM
'~Applications'
where '~Applications'.ComputerId IN
(
	SELECT Computers.ComputerId
	FROM Computers
	WHERE Computers.ComputerId IN
		(SELECT CollectionComputers.ComputerId
		FROM CollectionComputers

		WHERE CollectionComputers.CollectionId IN
			(SELECT Collections.CollectionId
			FROM Collections
			WHERE Collections.Path = 'Collection Library\Runtimes\Java (JRE)\Java 8\Java 8 32-bit\Java 8 32-bit (Old)'
			)
			AND CollectionComputers.IsMember = 1
		)
)
AND
LOWER('~Applications'.Name)  REGEXP (SELECT LOWER(Value) FROM SystemVariables WHERE Name= 'RTNameJava832RegEx')

UNION ALL

SELECT (SELECT Computers.Name FROM Computers WHERE Computers.ComputerId = '~Applications'.ComputerID) as ComputerName,'~Applications'.Name, '~Applications'.Version AS InstalledVersion,(SELECT SystemVariables.Value from SystemVariables WHERE Name = 'RTVerJava8') As CurrentVersion
FROM
'~Applications'
where '~Applications'.ComputerId IN
(
	SELECT Computers.ComputerId
	FROM Computers
	WHERE Computers.ComputerId IN
		(SELECT CollectionComputers.ComputerId
		FROM CollectionComputers

		WHERE CollectionComputers.CollectionId IN
			(SELECT Collections.CollectionId
			FROM Collections
			WHERE Collections.Path = 'Collection Library\Runtimes\Java (JRE)\Java 8\Java 8 64-bit\Java 8 64-bit (Old)'
			)
			AND CollectionComputers.IsMember = 1
		)
)
AND
LOWER('~Applications'.Name)  REGEXP (SELECT LOWER(Value) FROM SystemVariables WHERE Name= 'RTNameJava864RegEx')

UNION ALL

SELECT (SELECT Computers.Name FROM Computers WHERE Computers.ComputerId = '~Applications'.ComputerID) as ComputerName,'~Applications'.Name, '~Applications'.Version AS InstalledVersion,(SELECT SystemVariables.Value from SystemVariables WHERE Name = 'RTVerJava7') As CurrentVersion
FROM
'~Applications'
where '~Applications'.ComputerId IN
(
	SELECT Computers.ComputerId
	FROM Computers
	WHERE Computers.ComputerId IN
		(SELECT CollectionComputers.ComputerId
		FROM CollectionComputers

		WHERE CollectionComputers.CollectionId IN
			(SELECT Collections.CollectionId
			FROM Collections
			WHERE Collections.Path = 'Collection Library\Runtimes\Java (JRE)\Java 7\Java 7 32-bit\Java 7 32-bit (Old)'
			)
			AND CollectionComputers.IsMember = 1
		)
)
AND
LOWER('~Applications'.Name)  REGEXP (SELECT LOWER(Value) FROM SystemVariables WHERE Name= 'RTNameJava732RegEx')

UNION ALL

SELECT (SELECT Computers.Name FROM Computers WHERE Computers.ComputerId = '~Applications'.ComputerID) as ComputerName,'~Applications'.Name, '~Applications'.Version AS InstalledVersion,(SELECT SystemVariables.Value from SystemVariables WHERE Name = 'RTVerJava7') As CurrentVersion
FROM
'~Applications'
where '~Applications'.ComputerId IN
(
	SELECT Computers.ComputerId
	FROM Computers
	WHERE Computers.ComputerId IN
		(SELECT CollectionComputers.ComputerId
		FROM CollectionComputers

		WHERE CollectionComputers.CollectionId IN
			(SELECT Collections.CollectionId
			FROM Collections
			WHERE Collections.Path = 'Collection Library\Runtimes\Java (JRE)\Java 7\Java 7 64-bit\Java 7 64-bit (Old)'
			)
			AND CollectionComputers.IsMember = 1
		)
)
AND
LOWER('~Applications'.Name)  REGEXP (SELECT LOWER(Value) FROM SystemVariables WHERE Name= 'RTNameJava764RegEx')