cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
javier
Engaged Sweeper II
Hi friend,

I ask for some help.

I need a report that show the computers that have more than 5 "non approved" software (deny and neutral).

Something like:

COMPUTER //// USER //// NO APPROVED TOTAL


Any help will be appreciated

Thanks
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
Please find a report below which shows you this count for both unrated and nonauthorized software.

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

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
Please find a report below which shows you this count for both unrated and nonauthorized software.

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