Unaproved software / computer

[piotr_brzezicki]

Hello

Could you help me create a custom report. Later I will try do it on my own but now i need one as fast as I can.
I need a list of software installed on a computers in Asset Group “Komputery” that is not approved and undefined. Without the approved software.
Under each of the software I need a list of users on whose computer this software is installed.

[RCorbeil · Accepted solution]

List of all software and the active machines on which the software is installed, with restrictions.

SELECT Top 1000000
  tblAssetGroups.AssetGroup,
  Case tblSoftwareUni.Approved
    When 0 Then 'neutral'
    When 1 Then 'Approved'
    When 2 Then 'Unapproved'
    Else '(undefined)'
  End AS Approval,
  tblSoftwareUni.softwareName,
  tblSoftwareUni.SoftwarePublisher,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
FROM
  tblSoftware
  INNER JOIN tblSoftwareUni    ON tblSoftwareUni.SoftID = tblSoftware.softID
  INNER JOIN tblAssets         ON tblAssets.AssetID = tblSoftware.AssetID
  INNER JOIN tblAssetCustom    ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes    ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblAssetGroupLink ON tblAssets.AssetID = tblAssetGroupLink.AssetID
  INNER JOIN tblAssetGroups    ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
WHERE
  tblAssetGroups.AssetGroup = 'Komputery'
  AND tblAssetCustom.State = 1
  AND (tblSoftwareUni.Approved = 0 OR tblSoftwareUni.Approved = 2)
ORDER BY
  Approval,
  tblSoftwareUni.softwareName,
  tblAssets.AssetName