→ 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: 
jaysands
Engaged Sweeper
I am trying to create a report which will allow me to run a Software license report for different departments. The built-in reports works well for ALL of the license and associated software but I need to have a different report for each department.

I have created a report which shows a certain department's licenses but can not figure out a way to show how many pieces of software are actually in existence. I am what can only be described as clueless when it comes to SQL...I guess the question is which tables are you pulling data from for the built-in SW Lic. Compliance?

Any suggestions?

Thanks

--Jason
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
Below is the underlying query of the software license compliance page. As you can see, this is a big report, which is why we do not currently provide support for modifying it. If your goal is to list the number of software installations per department, it would be easier to modify one of the other built-in reports in the Reports tab instead, e.g. "Software: All installed software".
Select Top 1000000 licenseview.LicenseidID,
licenseview.License,
licenseview.Nrlicenses,
licenseview.Priceperlicense,
licenseview.Missing,
licenseview.Price,
licenseview.[number in use],
usedsoftware.softwareName,
usedsoftware.SoftwarePublisher,
usedsoftware.softwareVersion,
usedsoftware.total As subtotal,
licenseview.LicenseTypeName,
licenseview.LicenseContact,
licenseview.LicenseOwner
From tblLicenses
Inner Join (Select Purchased.LicenseidID,
Purchased.softwareName As License,
Purchased.nrlicenses As Nrlicenses,
Purchased.Priceperlicense,
Purchased.LicenseTypeName,
Purchased.LicenseContact,
Purchased.LicenseOwner,
Sum(softwaretotal.Total) - Coalesce(Purchased.nrlicenses, 0) As Missing,
Case
When (Sum(softwaretotal.Total) > Coalesce(Purchased.nrlicenses,
0)) Then (Sum(softwaretotal.Total) - Coalesce(Purchased.nrlicenses, 0)) *
Purchased.Priceperlicense Else Null End As Price,
Sum(softwaretotal.Total) As [number in use]
From tblSublicenses
Inner Join (Select tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
Count(tblSoftware.AssetID) As Total
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblSoftware.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher) softwaretotal
On softwaretotal.softwareName Like tblSublicenses.softwareName And
softwaretotal.softwareVersion Like tblSublicenses.softwareVersion
Right Outer Join (Select tblLicenses.softwareName,
tblLicenses.Priceperlicense,
tsyslicensetype.LicenseTypeName,
tblLicenses.LicenseContact,
tblLicenses.LicenseOwner,
Sum(tblSublicensesOrders.Nrlicenses) As nrlicenses,
tblLicenses.LicenseidID
From tblLicenses
Left Outer Join tsyslicensetype On tblLicenses.LicenseType =
tsyslicensetype.LicenseType
Left Outer Join tblSublicensesOrders On tblLicenses.LicenseidID =
tblSublicensesOrders.LicenseidID
Group By tblLicenses.softwareName,
tblLicenses.Priceperlicense,
tsyslicensetype.LicenseTypeName,
tblLicenses.LicenseContact,
tblLicenses.LicenseOwner,
tblLicenses.LicenseidID) Purchased On tblSublicenses.LicenseidID =
Purchased.LicenseidID
Group By Purchased.LicenseidID,
Purchased.softwareName,
Purchased.nrlicenses,
Purchased.Priceperlicense,
Purchased.LicenseTypeName,
Purchased.LicenseContact,
Purchased.LicenseOwner) licenseview On tblLicenses.LicenseidID =
licenseview.LicenseidID
Left Outer Join (tblSublicenses
Inner Join (Select tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion,
Count(tblSoftware.AssetID) As total
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblSoftware.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion) usedsoftware On usedsoftware.softwareName Like
tblSublicenses.softwareName And usedsoftware.softwareVersion Like
tblSublicenses.softwareVersion) On tblLicenses.LicenseidID =
tblSublicenses.LicenseidID
Order By licenseview.License,
usedsoftware.softwareName,
usedsoftware.softwareVersion

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
Below is the underlying query of the software license compliance page. As you can see, this is a big report, which is why we do not currently provide support for modifying it. If your goal is to list the number of software installations per department, it would be easier to modify one of the other built-in reports in the Reports tab instead, e.g. "Software: All installed software".
Select Top 1000000 licenseview.LicenseidID,
licenseview.License,
licenseview.Nrlicenses,
licenseview.Priceperlicense,
licenseview.Missing,
licenseview.Price,
licenseview.[number in use],
usedsoftware.softwareName,
usedsoftware.SoftwarePublisher,
usedsoftware.softwareVersion,
usedsoftware.total As subtotal,
licenseview.LicenseTypeName,
licenseview.LicenseContact,
licenseview.LicenseOwner
From tblLicenses
Inner Join (Select Purchased.LicenseidID,
Purchased.softwareName As License,
Purchased.nrlicenses As Nrlicenses,
Purchased.Priceperlicense,
Purchased.LicenseTypeName,
Purchased.LicenseContact,
Purchased.LicenseOwner,
Sum(softwaretotal.Total) - Coalesce(Purchased.nrlicenses, 0) As Missing,
Case
When (Sum(softwaretotal.Total) > Coalesce(Purchased.nrlicenses,
0)) Then (Sum(softwaretotal.Total) - Coalesce(Purchased.nrlicenses, 0)) *
Purchased.Priceperlicense Else Null End As Price,
Sum(softwaretotal.Total) As [number in use]
From tblSublicenses
Inner Join (Select tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
Count(tblSoftware.AssetID) As Total
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblSoftware.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher) softwaretotal
On softwaretotal.softwareName Like tblSublicenses.softwareName And
softwaretotal.softwareVersion Like tblSublicenses.softwareVersion
Right Outer Join (Select tblLicenses.softwareName,
tblLicenses.Priceperlicense,
tsyslicensetype.LicenseTypeName,
tblLicenses.LicenseContact,
tblLicenses.LicenseOwner,
Sum(tblSublicensesOrders.Nrlicenses) As nrlicenses,
tblLicenses.LicenseidID
From tblLicenses
Left Outer Join tsyslicensetype On tblLicenses.LicenseType =
tsyslicensetype.LicenseType
Left Outer Join tblSublicensesOrders On tblLicenses.LicenseidID =
tblSublicensesOrders.LicenseidID
Group By tblLicenses.softwareName,
tblLicenses.Priceperlicense,
tsyslicensetype.LicenseTypeName,
tblLicenses.LicenseContact,
tblLicenses.LicenseOwner,
tblLicenses.LicenseidID) Purchased On tblSublicenses.LicenseidID =
Purchased.LicenseidID
Group By Purchased.LicenseidID,
Purchased.softwareName,
Purchased.nrlicenses,
Purchased.Priceperlicense,
Purchased.LicenseTypeName,
Purchased.LicenseContact,
Purchased.LicenseOwner) licenseview On tblLicenses.LicenseidID =
licenseview.LicenseidID
Left Outer Join (tblSublicenses
Inner Join (Select tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion,
Count(tblSoftware.AssetID) As total
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblSoftware.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion) usedsoftware On usedsoftware.softwareName Like
tblSublicenses.softwareName And usedsoftware.softwareVersion Like
tblSublicenses.softwareVersion) On tblLicenses.LicenseidID =
tblSublicenses.LicenseidID
Order By licenseview.License,
usedsoftware.softwareName,
usedsoftware.softwareVersion