Which tables for the Software License Compliance?

[jaysands]

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

[Susan_A · Accepted solution]

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