→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
piotr_brzezicki
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

1 REPLY 1
RCorbeil
Honored Sweeper II
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