text file found and/or application installed

[glesax]

Hi,
We are in the process of rolling out an application. For some reason the installer is unstable and often fails. Because if this we would need a way to find these computers.
As the last step in the installation process a .txt file is created in the installation folder (I have created a custom scanning for the file already). I would like to create a report that lists all the computers that has the specific .txt file but does not get listed as having the application installed, list all computers that have the application installed but the file not found and computer that have both of them.
I would like to have them all in the same report. Something like the one below.

Comp. Name	| File	| Application
ABC	        | Found	| Installed
CDE	        |       | Installed
FGH	        | Found	|
IJK	        |       | Installed
WED	        | Found	| Installed

How would we go about doing this?

edit:fixed the code part...

[glesax]

Cheers Kris!

was the case I got cought up on. Was way overthinking it. Nice simple solution!
TNX! 🙂

[KrisNelson · Accepted solution]

Something like this should work. This is looking for Chrome being installed and the chrome.exe file, so you'll have to change that part of it.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblFileVersions.Found,
  Case
    When tblAssets.AssetID In (Select Top 1000000 tblSoftware.AssetID
          From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
              tblSoftware.softID
          Where tblSoftwareUni.softwareName Like '%Chrome%') Then 'Installed'
    Else 'Not Installed'
  End As [Awesome Software],
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
  Left Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Left Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
  tblFileVersions.FilePathfull =
  'C:\Program Files (x86)\Google\Chrome\Application\chrome.exe' And
  tblAssetCustom.State = 1

-Kris