cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
marcolips
Engaged Sweeper
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
3 REPLIES 3
Esben_D
Lansweeper Employee
Lansweeper Employee
Yep, we are aware.
RobertBasil
Engaged Sweeper III
Any update on when this bug will be fixed?

Esben.D wrote:
Yep, we are aware.


njordur
Engaged Sweeper III
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>