Windows 10 version overview shows other Win versions

[marcolips]

Hi,
after upgrading to the latest release and migrating to ms-sql the widget "Windows 10 Version Overview" does not only show Windows 10 versions, but all other Windows versions as well. Deleting/re-assigning the widget does not solve the problem.

Is this a known bug?

Regards
Marco

[Esben_D]

Yep, we are aware.

[RobertBasil]

Any update on when this bug will be fixed?

Esben.D wrote:
Yep, we are aware.

[njordur]

RobertB wrote:
Any update on when this bug will be fixed?

Esben.D wrote:
Yep, we are aware.

Here you go! Works better than the default one in my opinion. You can strip out the lower section in the aspx file from line 34 - 52 to have only Win 10 and comment out line 10 with dsAssetsWinX in it.

Run this first on the database

insert into [tsysDBobjects] ([DBobjName],[Query],[Comment])
values ('Web50GetOSVersion_Custom','
SELECT COALESCE (tsysOS.Image, ''notscanned.png'') AS Icon, tblAssets.AssetID, tblAssets.AssetName, COALESCE (tsysOS.OSname, ''Not scanned'') AS OS,tblOperatingsystem.Version,
COALESCE (tblAssets.OScode, '''') + ''.'' + COALESCE(tblAssets.BuildNumber, '''') AS Build, tblAssets.Version AS [OS Version], tblAssets.Domain, tblAssets.Username, tblAssets.Userdomain, tblAssets.IPAddress AS [IP Address], 
tblAssets.Description, tblAssetCustom.Manufacturer, tblAssetCustom.Model, tblAssetCustom.Location, tsysIPLocations.IPLocation, 
tblAssets.Firstseen, tblAssets.Lastseen  FROM tblAssets 
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID 
Inner Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
LEFT OUTER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode  
Left Join tsysIPLocations ON tblAssets.LocationID = tsysIPLocations.LocationID
WHERE (tblAssetCustom.State = 1) AND (tblAssets.Assettype = - 1) AND (COALESCE (tsysOS.OSname, ''Not scanned'') = @OS) AND COALESCE (tblOperatingsystem.Version, '''') = @version 
ORDER BY tblAssets.AssetName','Windows versions - Custom')

Create this view

create view [dbo].[vwWindowsVersions] as
select top 100000 icon,OSname,WinVersion,OSVersionBuild,OSVersion,SUM(Total) as Total
from
(Select
    tsysOS.Image AS Icon,
    tsysOS.OSname,
    Case 
        When tblOperatingsystem.Version = '10.0.10240'                                             Then 'Win 10 (1507)'
        When tblOperatingsystem.Version = '10.0.10586'                                             Then 'Win 10 (1511)'
        when tblOperatingsystem.Version = '10.0.14393' and tblOperatingsystem.ProductType = 1      Then 'Win 10 (1607)'
        When tblOperatingsystem.Version = '10.0.14393' and tblOperatingsystem.ProductType in (2,3) Then 'Win 2016 (1607)'
        When tblOperatingsystem.Version = '10.0.15063'                                             Then 'Win 10 (1703)'
        When tblOperatingsystem.Version = '10.0.16299'                                             Then 'Win 10 (1709)'
        When tblOperatingsystem.Version = '10.0.17134'                                             Then 'Win 10 (1803)'
        when tblOperatingsystem.Version = '10.0.17763' and tblOperatingsystem.ProductType = 1      Then 'Win 10 (1809)'
        When tblOperatingsystem.Version = '10.0.17763' and tblOperatingsystem.ProductType in (2,3) Then 'Win 2019 (1809)'
        When tblOperatingsystem.Version = '10.0.18362' and tblOperatingsystem.ProductType = 1      Then 'Win 10 (1903)'
        When tblOperatingsystem.Version = '10.0.18362' and tblOperatingsystem.ProductType in (2,3) Then 'Win 2019 (1903)' --Server Core
        When tblOperatingsystem.Version = '10.0.18363' and tblOperatingsystem.ProductType = 1      Then 'Win 10 (1909)'
        When tblOperatingsystem.Version = '10.0.18363' and tblOperatingsystem.ProductType in (2,3) Then 'Win 2019 (1909)' --Server Core
        When tblOperatingsystem.Version = '10.0.19041'                                             Then 'Win 10 (2004)' -- TBA
        Else tsysos.Osname 
    End as WinVersion,
    Case tblOperatingsystem.Version
        When '10.0.10240' Then '1507'
        When '10.0.10586' Then '1511'
        When '10.0.14393' Then '1607'
        When '10.0.15063' Then '1703'
        When '10.0.16299' Then '1709'
        When '10.0.17134' Then '1803'
        When '10.0.17763' Then '1809'
        When '10.0.18362' Then '1903'
        When '10.0.18363' Then '1909'
        When '10.0.19041' Then '2004'
        Else tblOperatingsystem.Version
    End as OSVersionBuild,
    tblOperatingsystem.Version as OSVersion,
    COUNT(1) as 'Total'
    From tblAssets
    Inner Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
    Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
    Where
     tblAssetCustom.State = 1
    AND (tblAssets.Assettype = - 1) 
    group by tblOperatingsystem.Version,tsysOS.Image,tsysOS.OSname,ProductType
) Win
group by icon,OSname,WinVersion,OSVersionBuild,OSVersion

Save this to an aspx file and put in the WidgetsCustom folder

<%@ Page Language="C#" AutoEventWireup="true" Inherits="LS.BaseControl" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="LS" %>
<% Response.CacheControl = "no-cache";%>
<% Response.AddHeader("Pragma", "no-cache"); %>
<% Response.Expires = -1; %>

<%
var dsAssetsWin10 = DB.ExecuteDataset("select icon,OSName,WinVersion,OSVersionBuild,OSVersion,Total from vwWindowsVersions where OSname =  'Win 10' Order By OSVersion desc");
var dsAssetsWinX  = DB.ExecuteDataset("select icon,OSName,WinVersion,OSVersionBuild,OSVersion,Total from vwWindowsVersions where OSname <> 'Win 10' Order By OSName desc");
{%> 

<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
	<td>
		<table width="100%" border="0" cellpadding="0" cellspacing="0">
		<% foreach (DataRow myrow in dsAssetsWin10.Rows)
		{%>
		<tr>
			<td>
				<div style="width: 160px; float:left; white-space: nowrap; margin-right: 10px;">
					<img src="<%= ResolveUrl("~/") %>images/<%: myrow["icon"] %>" width="10" height="10" hspace="2" vspace="2" style="float: left;" />
					<a style="float: left;" href="report.aspx?det=Web50GetOSVersion_Custom&amp;title=Windows 10 Version <%= HttpUtility.UrlEncode(myrow["OSVersionBuild"].ToString()) %>&amp;@OS=<%= HttpUtility.UrlEncode(myrow["OSName"].ToString()) %>&amp;@version=<%= HttpUtility.UrlEncode(myrow["OSVersion"].ToString()) %>">
		<%: myrow["WinVersion"] %></a>:
					<div style="float: right;"><%: myrow["Total"] %></div>
				</div>
				
				
			</td>
		</tr>
		<% }%> 
		</table>
	</td>
	<td>
		<table width="100%" border="0" cellpadding="0" cellspacing="0">
		<% foreach (DataRow myrow in dsAssetsWinX.Rows)
		{%>
		<tr>
			<td>
				<div style="width: 160px; float:left; white-space: nowrap; margin-right: 10px;">
					<img src="<%= ResolveUrl("~/") %>images/<%: myrow["icon"] %>" width="10" height="10" hspace="2" vspace="2" style="float: left;" />
					<a style="float: left;" href="report.aspx?det=Web50GetOSVersion_Custom&amp;title=Windows Version <%= HttpUtility.UrlEncode(myrow["OSVersionBuild"].ToString()) %>&amp;@OS=<%= HttpUtility.UrlEncode(myrow["OSName"].ToString()) %>&amp;@version=<%= HttpUtility.UrlEncode(myrow["OSVersion"].ToString()) %>">
		<%: myrow["WinVersion"] %></a>:
					<div style="float: right;"><%: myrow["Total"] %></div>
				</div>
				
				
			</td>
		</tr>
		<% }%> 
		</table>		
	</td>
</tr>	
</table>
<%=AutoRefresh(60) %>

</div>
<%}%>
<script type="text/javascript">
$('#WTitle<%=TabControlID %>', window.top.document).text("Windows Version Overview (New)");
</script>