All XP,7,8 and 8.1 systems that do not have software1 and software 2

[ceezax]

Hello,

I need to run a query to get all machines that have windows xp, 7 , 8 and 8.1

and

do not have software1 and software2 installed.

Thanks !

[RCorbeil · Accepted solution]

I'm guessing about the Win8 names as I don't have any on my network, but you should be able to use something like this:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysOS.OSname
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tsysOS On tblAssets.OScode = tsysOS.OScode
WHERE
  tblAssetCustom.State = 1
  AND tblAssets.Assettype = -1
  AND tSysOS.OSName IN ('Win XP', 'Win 7', 'Win 8', 'Win 8.1')

  AND tblAssets.AssetID     IN ( SELECT
                                   tblSoftware.AssetID
                                 FROM
                                   tblSoftware
                                   INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
                                 WHERE
                                   tblSoftwareUni.softwareName LIKE '%your_software_title%'
                               )

  AND tblAssets.AssetID NOT IN ( SELECT
                                   tblSoftware.AssetID
                                 FROM
                                   tblSoftware
                                   INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
                                 WHERE
                                   tblSoftwareUni.softwareName LIKE '%your_software_title%'
                               )