‎07-31-2013 07:48 PM
Solved! Go to Solution.
‎08-01-2013 06:25 PM
Select Top 1000000 tblAssetGroups.AssetGroup As Site,
tblSoftwareUni.softwareName As Software,
tblSoftwareUni.SoftwarePublisher As Publisher,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'authorized'
When 2 Then 'unauthorized' End As [Authorization],
Count(tblSoftware.AssetID) As Total
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 tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetCustom.State = 1
Group By tblAssetGroups.AssetGroup,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'authorized'
When 2 Then 'unauthorized' End
Order By Site,
Total Desc
‎03-10-2015 11:53 AM
‎08-01-2013 07:31 PM
‎08-01-2013 07:14 PM
‎08-01-2013 07:10 PM
‎08-01-2013 07:08 PM
‎08-01-2013 06:55 PM
‎08-01-2013 06:25 PM
Select Top 1000000 tblAssetGroups.AssetGroup As Site,
tblSoftwareUni.softwareName As Software,
tblSoftwareUni.SoftwarePublisher As Publisher,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'authorized'
When 2 Then 'unauthorized' End As [Authorization],
Count(tblSoftware.AssetID) As Total
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 tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetCustom.State = 1
Group By tblAssetGroups.AssetGroup,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'authorized'
When 2 Then 'unauthorized' End
Order By Site,
Total Desc
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now