‎01-30-2015 01:30 PM
Solved! Go to Solution.
‎01-30-2015 03:17 PM
Select Top 1000000 *
From (Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
Case tblSoftwareUni.Approved When 2 Then 'unauthorized'
When 0 Then 'not rated' End As approval,
Count(tblSoftwareUni.softwareName) As [Count Software],
tsysOS.Image As icon
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
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblSoftwareUni.Approved In (0, 2) And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.Image,
tblSoftwareUni.Approved) CountSW
Where CountSW.[Count Software] > 5
Order By CountSW.AssetName,
CountSW.Username
‎01-30-2015 03:17 PM
Select Top 1000000 *
From (Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
Case tblSoftwareUni.Approved When 2 Then 'unauthorized'
When 0 Then 'not rated' End As approval,
Count(tblSoftwareUni.softwareName) As [Count Software],
tsysOS.Image As icon
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
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblSoftwareUni.Approved In (0, 2) And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.Image,
tblSoftwareUni.Approved) CountSW
Where CountSW.[Count Software] > 5
Order By CountSW.AssetName,
CountSW.Username
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now