SQL query for report

[tonyb99]

I'm trying to run a report to show machines that have 2 custom scanned file path outcomes (I MEAN BOTH OUTCOMES EXIST AND ARE FOUND VALUE IS 0), and some additional options. As you can see I'm not doing this right. Can you suggest how I can accomplish this. The subsequent conditions are the same for each filepath.

Thanks

Select Top 1000000 upgrade_tblComputers.Computername,
upgrade_tblComputers.ComputerUnique,
upgrade_tblFileVersions.FilePathfull,
upgrade_tblFileVersions.Found,
upgrade_tblFileVersions.FileVersion,
upgrade_tblFileVersions.CompanyName,
upgrade_tblFileVersions.Filesize,
upgrade_tblComputers.Computer,
upgrade_tblComputersystem.Domainrole,
upgrade_tblComputers.Lastseen,
upgrade_tblComputers.LastActiveScan,
upgrade_tblComputers.Domain
From upgrade_tblComputers
Inner Join upgrade_tblFileVersions On upgrade_tblComputers.Computername =
upgrade_tblFileVersions.Computername
Inner Join upgrade_tblComputersystem On upgrade_tblComputers.Computername =
upgrade_tblComputersystem.Computername
Where (upgrade_tblFileVersions.FilePathfull Like'%c:\windows\ccm\ccmexec.exe%' And upgrade_tblFileVersions.Found Like '0'And upgrade_tblComputers.Computer Not Like 'HP-TC%' And upgrade_tblComputersystem.Domainrole <= 1)
And (upgrade_tblFileVersions.FilePathfull Like'%c:\windows\system32\ccm\ccmexec.exe%' And upgrade_tblFileVersions.Found Like '0'And upgrade_tblComputers.Computer Not Like 'HP-TC%' And upgrade_tblComputersystem.Domainrole <= 1)
Order by Computer

[RCorbeil · Accepted solution]

From the tables you're referring to, I'm going to assume that you're running v5.

I'm going to suggest a two-step build. First, overbroad to make sure that you're seeing the filenames that you expect, then with the constraints you're looking for.

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Domain,
  tbldomainroles.Domainrolename,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.LastActiveScan,
  tblFileVersions.Found,
  tblFileVersions.FilePathfull,
  tblFileVersions.FileVersion,
  tblFileVersions.CompanyName,
  tblFileVersions.Filesize
FROM
  tblAssets
  INNER JOIN tblAssetCustom    ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes    ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblFileVersions   ON tblAssets.AssetID = tblFileVersions.AssetID
  INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
  INNER JOIN tbldomainroles    ON tbldomainroles.Domainrole = tblComputersystem.Domainrole
WHERE
  tblAssetCustom.State = 1

That should return all the custom files you've configured your LANSweeper to look for. You should see the files you're looking to not-find in there (Found=0).

Next, build on the WHERE clause to limit your result set.

WHERE
-- State = Active
  tblAssetCustom.State = 1
-- DomainRole = workstation
  AND tblComputersystem.Domainrole <= 1
-- Computer name doesn't start with 'HP-TC'
  AND tblAssets.AssetName NOT LIKE 'HP-TC%'
-- first path has been searched for
  AND Exists ( SELECT Top 1 tblFileVersions.Found
               FROM   tblFileVersions
               WHERE  tblFileVersions.AssetID = tblAssets.AssetID
                      AND tblFileVersions.FilePathfull = 'c:\windows\ccm\ccmexec.exe'
             )
-- first path has been not-found
  AND ( SELECT Count(*)
        FROM  tblFileVersions
        WHERE tblFileVersions.AssetID = tblAssets.AssetID
              AND tblFileVersions.FilePathfull = 'c:\windows\ccm\ccmexec.exe'
              AND tblFileVersions.Found = 0
      ) > 0

-- second path has been searched for
  AND Exists ( SELECT Top 1 tblFileVersions.Found
               FROM   tblFileVersions
               WHERE  tblFileVersions.AssetID = tblAssets.AssetID
                      AND tblFileVersions.FilePathfull = 'c:\windows\system32\ccm\ccmexec.exe'
             )
-- second path has been not-found
  AND ( SELECT Count(*)
        FROM  tblFileVersions
        WHERE tblFileVersions.AssetID = tblAssets.AssetID
              AND tblFileVersions.FilePathfull = 'c:\windows\system32\ccm\ccmexec.exe'
              AND tblFileVersions.Found = 0
     ) > 0

That will return all custom-file matches for machines that match the two not-found conditions. Assuming you only want to see results about those two matches, you'll want to add another WHERE condition:

WHERE
  tblAssetCustom.State = 1
  AND tblComputersystem.Domainrole <= 1
  AND tblAssets.AssetName NOT LIKE 'HP-TC%'
-- custom file is one of those we're testing for
  AND tblFileVersions.FilePathfull LIKE '%\ccm\ccmexec.exe'
...

The "Exists (SELECT Top 1..." returns true if any attempt has been made to find the file, whether it was found or not.

The "SELECT Count(*)..." counts the number of entries where Found=0, so a count total of >0 means that there is an entry in the table where Found=0.