cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
sbugler
Engaged Sweeper II
Apologies in advance if this has been answered already (I couldn't find anything with a search), and apologies also if it's quite simple, but I couldn't figure it out.

I wanted to know if there was a way to generate a report of the Software Authorization section, so simply a report which says
Software Name
Software Publisher
Total installations
Approved/Neutral/Denied

This feature is quite useful for us and we just wanted to find a way to export it. There are software authorised and unauthorised reports, but they include every asset with the said software, which makes the list quite long.

Thanks in advance!
1 ACCEPTED SOLUTION
Andy_Sismey
Champion Sweeper III
Hi,

Is this what you are after ?

Select Top 1000000 Count(tblAssets.AssetID) As Count,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Case
When tblSoftwareUni.Approved = 2 Then 'Unauthorised'
When tblSoftwareUni.Approved = 1 Then 'Approved'
When tblSoftwareUni.Approved = 0 Then 'No Status'
End As Status
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
Case
When tblSoftwareUni.Approved = 2 Then 'Unauthorised'
When tblSoftwareUni.Approved = 1 Then 'Approved'
When tblSoftwareUni.Approved = 0 Then 'No Status'
End
Order By Software

View solution in original post

2 REPLIES 2
Andy_Sismey
Champion Sweeper III
Hi,

Is this what you are after ?

Select Top 1000000 Count(tblAssets.AssetID) As Count,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Case
When tblSoftwareUni.Approved = 2 Then 'Unauthorised'
When tblSoftwareUni.Approved = 1 Then 'Approved'
When tblSoftwareUni.Approved = 0 Then 'No Status'
End As Status
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
Case
When tblSoftwareUni.Approved = 2 Then 'Unauthorised'
When tblSoftwareUni.Approved = 1 Then 'Approved'
When tblSoftwareUni.Approved = 0 Then 'No Status'
End
Order By Software
sbugler
Engaged Sweeper II
Andy.S wrote:
Hi,

Is this what you are after ?

Select Top 1000000 Count(tblAssets.AssetID) As Count,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Case
When tblSoftwareUni.Approved = 2 Then 'Unauthorised'
When tblSoftwareUni.Approved = 1 Then 'Approved'
When tblSoftwareUni.Approved = 0 Then 'No Status'
End As Status
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
Case
When tblSoftwareUni.Approved = 2 Then 'Unauthorised'
When tblSoftwareUni.Approved = 1 Then 'Approved'
When tblSoftwareUni.Approved = 0 Then 'No Status'
End
Order By Software


Exactly what I am after! Thank you very much! Sorry if that was relatively simple.. I am trying to improve my SQL skills!

I appreciate the quick response, thanks again.