cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dlaporte
Engaged Sweeper
Can somebody have an example of a report that will list every computer that doesnt have a specific software installed ? In my case, it would be 'Microsoft .NET Framework 3.5'

Thanks!
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tbldomainroles.Domainrolename
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
INNER JOIN tbldomainroles ON tbldomainroles.Domainrole = tblComputersystem.Domainrole
WHERE
tblAssetCustom.State = 1
AND NOT EXISTS( SELECT tblSoftware.AssetID
FROM tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftware.AssetID = tblAssets.AssetID
AND tblSoftwareUni.softwareName LIKE 'Microsoft .NET Framework 3.5%')
ORDER BY
tblComputersystem.Domainrole,
tblAssets.AssetName

You didn't ask to have servers distinguished, but I chose to include the domain role in just in case.

View solution in original post

2 REPLIES 2
dlaporte
Engaged Sweeper
Working, Thank you 🙂
RCorbeil
Honored Sweeper II
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tbldomainroles.Domainrolename
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
INNER JOIN tbldomainroles ON tbldomainroles.Domainrole = tblComputersystem.Domainrole
WHERE
tblAssetCustom.State = 1
AND NOT EXISTS( SELECT tblSoftware.AssetID
FROM tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE tblSoftware.AssetID = tblAssets.AssetID
AND tblSoftwareUni.softwareName LIKE 'Microsoft .NET Framework 3.5%')
ORDER BY
tblComputersystem.Domainrole,
tblAssets.AssetName

You didn't ask to have servers distinguished, but I chose to include the domain role in just in case.