Need Report for Two Specific Software Programs

[pwalach]

I need to compile a report of a of our clients that are running both Symantec Endpoint Protection and System Center Endpoint Protection, however I can't seem to get it to work, here is what I have:

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.AssetName,
tblAssets.AssetID
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblSoftwareUni.softwareName = 'Symantec Endpoint Protection' And
tblSoftwareUni.softwareName = 'System Center Endpoint Protection' And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
Version

If I replace the And with Or, I get a report that shows any computer that has either of the Software Packages. But when using the And in order to find clients that are running both packages at the same time, I get no results, even though I know that I have a lot of clients running both. What am I doing wrong in the report above?

[pwalach]

That's perfect, exactly what I was looking for. Many thanks!

[RCorbeil · Accepted solution]

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE
  tblAssetCustom.State = 1

  -- Software 1 is installed on the asset
  AND Exists (SELECT
                tblSoftware.AssetID
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                tblSoftware.AssetID = tblAssets.AssetID
                AND tblSoftwareUni.SoftwareName = 'Symantec Endpoint Protection'
             )

  -- Software 2 is installed on the asset
  AND Exists (SELECT
                tblSoftware.AssetID
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                tblSoftware.AssetID = tblAssets.AssetID
                AND tblSoftwareUni.SoftwareName = 'System Center Endpoint Protection'
             )
ORDER BY
  tblAssets.AssetName

[pwalach]

Thanks for getting back to me so quickly. I actually don't care about seeing the software listed, just want to see a list of computers that have both software packages on them.

I tried modifying it as per your instructions, but can't seem to figure it out. Could you please post the updated one for me?

Many thanks!

[RCorbeil]

I assume from your code that you're wanting to see the software details in the output. Something like this should work.

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblSoftwareUni.SoftwareName AS Software,
  tblSoftware.softwareVersion AS Version,
  tblSoftwareUni.SoftwarePublisher AS Publisher,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblSoftware    ON tblSoftware.AssetID = tblAssets.AssetID
  INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE
  tblAssetCustom.State = 1

-- Software 1 is installed on the asset
  AND Exists (SELECT
                tblSoftware.AssetID
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                tblSoftware.AssetID = tblAssets.AssetID
                AND tblSoftwareUni.SoftwareName = 'Symantec Endpoint Protection'
             )

-- Software 2 is installed on the asset
  AND Exists (SELECT
                tblSoftware.AssetID
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
              WHERE
                tblSoftware.AssetID = tblAssets.AssetID
                AND tblSoftwareUni.SoftwareName = 'System Center Endpoint Protection'
             )

-- Only list either software 1 or software 2; not interested in seeing EVERYTHING installed on the asset
  AND ( (tblSoftwareUni.SoftwareName = 'Symantec Endpoint Protection')
        OR (tblSoftwareUni.SoftwareName = 'System Center Endpoint Protection')
      )

ORDER BY
  tblAssets.AssetName,
  tblSoftwareUni.SoftwareName

If you're only interested in the list of machine and don't care about seeing the software, just knowing that it's there, you can remove the joins to the two software tables and the final AND condition from the WHERE clause.