Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-08-2013 10:07 PM
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
OS Compliance
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.
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!
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!
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-11-2013 05:50 PM
Here are the simple reports:
Operating System
Application/Software
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
1 REPLY 1
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-11-2013 05:50 PM
Here are the simple reports:
Operating System
Application/Software
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