Created
August 4, 2020 05:25
-
-
Save michaelnoonan/1f02593280c3a2df81862bd4b27ec1ea to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH LatestUsage AS ( | |
SELECT LicenseSerial, MAX(LastUpdated) AS LastUpdated | |
FROM UsageRecord | |
GROUP BY LicenseSerial | |
), | |
TargetOperatingSystems As ( | |
SELECT usage.LicenseSerial, TargetOs.Os, TargetOs.[Count] | |
FROM UsageRecord usage | |
INNER JOIN LatestUsage latestUsage ON usage.LicenseSerial = latestUsage.LicenseSerial AND usage.LastUpdated = latestUsage.LastUpdated | |
CROSS APPLY OPENJSON(usage.[JSON]) WITH (TargetOperatingSystems NVARCHAR(MAX) AS JSON) AS UsageJson | |
CROSS APPLY OPENJSON(UsageJson.TargetOperatingSystems) WITH ( | |
OS NVARCHAR(MAX), | |
[Count] INT | |
) AS TargetOs | |
WHERE latestUsage.LastUpdated > DATEADD(DAY, -30, GETDATE()) | |
), | |
WindowsUsage AS ( | |
SELECT tos.OS, tos.[Count], tos.LicenseSerial, | |
(CASE | |
WHEN tos.OS LIKE '%2003%' THEN '2003' | |
WHEN tos.OS LIKE '%2008%' AND tos.OS NOT LIKE '%2008 R2%' THEN '2008' | |
WHEN tos.OS LIKE '%2008 R2%' THEN '2008 R2' | |
WHEN tos.OS LIKE '%2012%' AND tos.OS NOT LIKE '%2012 R2%' THEN '2012' | |
WHEN tos.OS LIKE '%2012 R2%' THEN '2012 R2' | |
WHEN tos.OS LIKE '%2016%' THEN '2016' | |
WHEN tos.OS LIKE '%2019%' THEN '2019' | |
END | |
) AS Version | |
FROM TargetOperatingSystems tos | |
WHERE tos.OS LIKE '%Windows%' | |
AND (tos.OS LIKE '%200%' OR tos.OS LIKE '%201%') | |
) | |
SELECT WindowsUsage.Version, SUM(windowsUsage.Count) AS [Total Number of Servers], COUNT(*) AS [Affected Customers (Unique License Keys)] | |
FROM WindowsUsage | |
INNER JOIN dbo.LicenseKey license ON license.LicenseSerialHash = windowsUsage.LicenseSerial | |
WHERE license.Type <> 'Trial' | |
GROUP BY WindowsUsage.Version |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment