‎10-21-2015 04:53 PM
Solved! Go to Solution.
‎10-22-2015 01:09 PM
Select Top 1000000 tblAssets.AssetName,
tblAssets.AssetID,
tsysAssetTypes.AssetTypename,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.Username,
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 tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblSoftware.AssetID Not In (Select tUnsuccessfulCount.AssetID
From (Select tsysPackageLogs.AssetID,
Count(tsysPackageLogs.PackageLogID) As [number unsuccessful]
From tsysPackageLogs
Inner Join tsysPackages On tsysPackageLogs.PackageID =
tsysPackages.PackageID
Where tsysPackageLogs.Success = 0 And tsysPackages.PackageName Like
'YourExamplePackage '
Group By tsysPackageLogs.AssetID
Having Count(tsysPackageLogs.PackageLogID) >3 ) tUnsuccessfulCount) And
tblSoftwareUni.softwareName Like '%Reader%' And tblSoftware.softwareVersion =
'9.3.0' And tblAssetCustom.State = 1
‎10-22-2015 03:19 PM
‎10-22-2015 01:09 PM
Select Top 1000000 tblAssets.AssetName,
tblAssets.AssetID,
tsysAssetTypes.AssetTypename,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.Username,
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 tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblSoftware.AssetID Not In (Select tUnsuccessfulCount.AssetID
From (Select tsysPackageLogs.AssetID,
Count(tsysPackageLogs.PackageLogID) As [number unsuccessful]
From tsysPackageLogs
Inner Join tsysPackages On tsysPackageLogs.PackageID =
tsysPackages.PackageID
Where tsysPackageLogs.Success = 0 And tsysPackages.PackageName Like
'YourExamplePackage '
Group By tsysPackageLogs.AssetID
Having Count(tsysPackageLogs.PackageLogID) >3 ) tUnsuccessfulCount) And
tblSoftwareUni.softwareName Like '%Reader%' And tblSoftware.softwareVersion =
'9.3.0' And tblAssetCustom.State = 1
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now