PC with more than 5 programs installed (deny and neutral)

[javier]

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

[Daniel_B · Accepted solution]

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