Application and OS Compliance Reports

[cwatsonii]

In version 4.x, I used the following queries to create monthly reports (which were auto emailed to managers) of the compliance reports that were built into the webpage.

Software Compliance

Select Top 1000000 web40licenseview.License, web40licenseview.[number in use] As [In use], web40licenseview.Nrlicenses As Purchased, web40licenseview.Priceperlicense As Price, web40licenseview.Missing As Missing, tblsublicenses.softwareName As Sublicense, web40usedsoftware.[number in use] As [In use sublicense] From web40licenseview Inner Join tblLicenses On web40licenseview.LicenseidID = tblLicenses.LicenseidID Left Outer Join (web40usedsoftware Inner Join tblsublicenses On web40usedsoftware.softwareName = tblsublicenses.softwareName And IsNull(web40usedsoftware.softwareVersion, '') Like IsNull(tblsublicenses.softwareVersion, '')) On tblLicenses.LicenseidID = tblsublicenses.LicenseidID Order By web40licenseview.License, tblsublicenses.softwareName

OS Compliance

Select web40OStotal.OS, web40OStotal.[Number in use],
IsNull(tblOsLicenses.Nrlicenses, 0) As Purchased,
IsNull(tblOsLicenses.Priceperlicense, 0) As PriceperLicense, Case
When (web40OStotal.[Number in use] > IsNull(tblOsLicenses.Nrlicenses,
0)) Then (web40OStotal.[Number in use] - IsNull(tblOsLicenses.Nrlicenses,
0)) Else Null End As Missing, Case
When (web40OStotal.[Number in use] > IsNull(tblOsLicenses.Nrlicenses,
0)) Then (web40OStotal.[Number in use] - IsNull(tblOsLicenses.Nrlicenses,
0)) * IsNull(tblOsLicenses.Priceperlicense, 0) Else Null End As Total
From web40OStotal Left Outer Join
tblOsLicenses On web40OStotal.OS = tblOsLicenses.OS
Order By Case
When (web40OStotal.[Number in use] > IsNull(tblOsLicenses.Nrlicenses,
0)) Then (web40OStotal.[Number in use] - IsNull(tblOsLicenses.Nrlicenses,
0)) * IsNull(tblOsLicenses.Priceperlicense, 0) Else Null End Desc

In version 5, it seems the tables have changed quite a bit. I've started creating a new report for the new version, but now I'm stuck. Here's what I have for the Software Compliance.

Select Distinct Top 1000000 tblLicenses.softwareName As Name,
tblSublicensesOrders.Nrlicenses As Purchased
From tblLicenses
Left Join tblsublicenses On tblLicenses.LicenseidID =
tblsublicenses.LicenseidID
Inner Join tblSublicensesOrders On tblLicenses.LicenseidID =
tblSublicensesOrders.LicenseidID

Could anyone assist please? I'm going to continue to keep working on it.

BTW, the new version is way cool! Thanks for all the awesome additions and changes!

[cwatsonii · Accepted solution]

Here are the simple reports:

Operating System

Select Distinct Top 1000000 tblOsLicenses.OS As Name,
upgrade_web40OStotal.[Number in use] As [In Use],
tblOssubLicensesOrders.Nrlicenses As Purchased,
upgrade_web40OStotal.[Number in use] - tblOssubLicensesOrders.Nrlicenses As
Missing
From tblOssubLicenses
Inner Join tblOsLicenses On tblOsLicenses.OSLicenseidID =
tblOssubLicenses.OSLicenseidID
Inner Join tblOssubLicensesOrders On tblOsLicenses.OSLicenseidID =
tblOssubLicensesOrders.OSLicenseidID
Inner Join upgrade_web40OStotal On tblOsLicenses.OS = upgrade_web40OStotal.OS

Application/Software

Select Distinct Top 1000000 tblLicenses.softwareName As Name,
tblSublicensesOrders.Nrlicenses As Purchased,
upgrade_web40usedsoftware.[number in use] As [In Use],
upgrade_web40usedsoftware.[number in use] - tblSublicensesOrders.Nrlicenses As
Missing
From tblLicenses
Left Join tblsublicenses On tblLicenses.LicenseidID =
tblsublicenses.LicenseidID
Inner Join tblSublicensesOrders On tblLicenses.LicenseidID =
tblSublicensesOrders.LicenseidID
Left Join upgrade_web40usedsoftware On tblLicenses.softwareName =
upgrade_web40usedsoftware.softwareName