How to scan where multiple software with specific version is installed

[Starbase12]

Hello together,

I'm looking for an working report that lists all hostnames of computers that have installed a specific combination of 3 programs with specific versions.

Unfortunatly as soon as I add a second software as requirement, no clients will be listed anymore. I don't know what I'm doing wrong.

For the beginning I tried to list computers, who're having "Microsoft Visual C++ 2008 Redistributable - x86" and "Windows Internet Explorer 11" installed. Because I'm failing here, I didn't try to add a specific version as well. Howevery that's actually the goal: To list computers that have Software A with version 1.x installed + Software B with Version 3.2.x + Software C with Version 0.1.0.

My report so far:

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblassets.Lasttried,
  tblSoftwareUni.softwareName
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join lansweeperdb.dbo.tblSoftware On tblassets.AssetID =
      tblSoftware.AssetID
  Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
Where
  tblSoftwareUni.softwareName =
  'Microsoft Visual C++ 2008 Redistributable - x86' And
  tblSoftwareUni.softwareName = 'Windows Internet Explorer 11' And
  tblassetcustom.State = 1

Thanks in advanced.
Chris

[RCorbeil]

There are often many ways of phrasing your query, so if what you've got works, good on ya. Personally, I would have set up the JOINs a little differently that you.

You've set up to match against the software name but select any recorded version, then you're filtering for only the versions you want in the main SELECT. Since you're after specific versions, I would have included those in the sub-SELECTS that you're JOINing against.

From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype

  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%SAP Interactive Excel%'
      And tblSoftware.softwareVersion = '3.0.8.20036'
    ) As s1 On s1.AssetID = tblassets.AssetID

  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%SAP Business Explorer%'
      And tblSoftware.softwareVersion = '7.60'
    ) As s2 On s2.AssetID = tblassets.AssetID

  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where
      tblSoftwareUni.softwareName Like '%SAP GUI for Windows 7.60  (Patch 5)%'
      And tblSoftware.softwareVersion = '7.60 Compilation 1'
    ) As s3 On s3.AssetID = tblassets.AssetID

  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%SAP Business Client 7.0%'
      And tblSoftware.softwareVersion = '7.0 PL10'
    ) As s4 On s4.AssetID = tblassets.AssetID

  Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblassets.OScode
Where
  tblassetcustom.State = 1
Order By
  tblassets.AssetName

Among other things, that simplifies your new request. For each JOINed sub-SELECT, if there's a match for the software and version, then there is an AssetID returned. If there is no match, NULL is returned. You can use this in your WHERE clause:

Where
  tblassetcustom.State = 1
  AND s1.AssetID IS NOT NULL  -- match found
  AND s2.AssetID IS NOT NULL  -- match found
  AND s3.AssetID IS NOT NULL  -- match found
  AND s4.AssetID IS NULL      -- no match

[RCorbeil · Accepted solution]

Something like this? Just add a check for tblSoftware.SoftwareVersion on each software check.

[Starbase12]

The report works great. Is there any chance I can also find computers like: List all Computer which has Software 1, Software 2 and Software 3 installed but NOT Software 4?

[Starbase12]

RC62N wrote:
Something like this? Just add a check for tblSoftware.SoftwareVersion on each software check.

Many thanks. I was able to adapt that report:

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tsysOS.OSname As OS,
  tblassets.Version As Build,
  tblassets.Username,
  s1.SoftwarePublisher As [soft1 Publisher],
  s1.softwareName As [soft1 Name],
  s1.softwareVersion As [soft1 Version],
  s2.SoftwarePublisher As [soft2 Publisher],
  s2.softwareName As [soft2 Name],
  s2.softwareVersion As [soft2 Version],
  s3.SoftwarePublisher As [soft3 Publisher],
  s3.softwareName As [soft3 Name],
  s3.softwareVersion As [soft3 Version],
  s4.SoftwarePublisher As [soft4 Publisher],
  s4.softwareName As [soft4 Name],
  s4.softwareVersion As [soft4 Version],
  tblassets.Lastseen,
  tblassets.Lasttried
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%SAP Interactive Excel%') As s1 On
      s1.AssetID = tblassets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%SAP Business Explorer%') As s2 On
      s2.AssetID = tblassets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where
      tblSoftwareUni.softwareName Like '%SAP GUI for Windows 7.60  (Patch 5)%')
  As s3 On s3.AssetID = tblassets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.SoftwarePublisher,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%SAP Business Client 7.0%') As s4 On
      s4.AssetID = tblassets.AssetID
  Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblassets.OScode
Where s1.softwareName = 'SAP Interactive Excel' And s1.softwareVersion =
  '3.0.8.20036' And s2.softwareName = 'SAP Business Explorer' And
  s2.softwareVersion = '7.60' And
  s3.softwareName = 'SAP GUI for Windows 7.60  (Patch 5)' And
  s3.softwareVersion = '7.60 Compilation 1' And s4.softwareName =
  'SAP Business Client 7.0' And s4.softwareVersion = '7.0 PL10' And
  tblassetcustom.State = 1
Order By tblassets.AssetName