→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ceezax
Engaged Sweeper

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 !
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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%'
)

View solution in original post

1 REPLY 1
RCorbeil
Honored Sweeper II
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%'
)