Wrong output for view web40repDuplicatelicenseMS

[wpa1160]

The sub-select of the above view is wrong (aka missing brackets)

   Select Top 1000000 
      tblSerialnumber.Product, 
      tblSerialnumber.ProductKey, 
      Count(tblSerialnumber.SerialID) As Total, 
      TsysSerials.SerialID 
   From 
      dbo.tblComputers 
      Inner Join web40ActiveComputers On web40ActiveComputers.Computername = dbo.tblComputers.Computername 
      Inner Join tblSerialnumber On dbo.tblComputers.Computername = tblSerialnumber.Computername 
      Inner Join TsysSerials On TsysSerials.Product = tblSerialnumber.Product 
   Group By 
      tblSerialnumber.Product, 
      tblSerialnumber.ProductKey, 
      TsysSerials.SerialID 
   Having 
      Count(tblSerialnumber.SerialID) > 1 
      And TsysSerials.SerialID = 1 Or tblSerialnumber.Product Like 'Microsoft%'

should be

   Select Top 1000000 
      tblSerialnumber.Product, 
      tblSerialnumber.ProductKey, 
      Count(tblSerialnumber.SerialID) As Total, 
      TsysSerials.SerialID 
   From 
      dbo.tblComputers 
      Inner Join web40ActiveComputers On web40ActiveComputers.Computername = dbo.tblComputers.Computername 
      Inner Join tblSerialnumber On dbo.tblComputers.Computername = tblSerialnumber.Computername 
      Inner Join TsysSerials On TsysSerials.Product = tblSerialnumber.Product 
   Group By 
      tblSerialnumber.Product, 
      tblSerialnumber.ProductKey, 
      TsysSerials.SerialID 
   Having 
      Count(tblSerialnumber.SerialID) > 1 
      And (TsysSerials.SerialID = 1 Or tblSerialnumber.Product Like 'Microsoft%')

Otherwise the query would return a single license as a duplicate (see attached file).

[Hemoco]

Can you test this one please:

Select Top 1000000 dbo.tblComputers.Computername,
  dbo.tblComputers.ComputerUnique, dbo.tblComputers.Domain, Web40OSName.OSname
  As OS, Duplicates.Product, Duplicates.ProductKey, Duplicates.Total,
  dbo.tblComputers.Lastseen, Web40OSName.Compimage As icon
From dbo.tblComputers Inner Join
  web40ActiveComputers On web40ActiveComputers.Computername =
    dbo.tblComputers.Computername Inner Join
  tblOperatingsystem On dbo.tblComputers.Computername =
    tblOperatingsystem.Computername Inner Join
  Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername
  Inner Join
  tblSerialnumber On dbo.tblComputers.Computername =
    tblSerialnumber.Computername Inner Join
  (Select Top 1000000 tblSerialnumber.Product, tblSerialnumber.ProductKey,
      Count(tblSerialnumber.SerialID) As Total, TsysSerials.SerialID
    From dbo.tblComputers Inner Join
      web40ActiveComputers On web40ActiveComputers.Computername =
        dbo.tblComputers.Computername Inner Join
      tblSerialnumber On dbo.tblComputers.Computername =
        tblSerialnumber.Computername Inner Join
      TsysSerials On TsysSerials.Product = tblSerialnumber.Product
    Group By tblSerialnumber.Product, tblSerialnumber.ProductKey,
      TsysSerials.SerialID
    Having Count(tblSerialnumber.SerialID) > 1 And (TsysSerials.SerialID = 1 Or
        tblSerialnumber.Product Like 'Microsoft%')) Duplicates On
    Duplicates.ProductKey = tblSerialnumber.ProductKey And Duplicates.Product =
    tblSerialnumber.Product
Order By Duplicates.Total Desc, Duplicates.Product, dbo.tblComputers.Computer

[wpa1160]

Any updates on that?

The view still contains the wrong WHERE condition. If I add the brackets as suggested in the first post the wrong output is gone...

[Hemoco]

wpa1160 wrote:
Any updates on that?

The view still contains the wrong WHERE condition. If I add the brackets as suggested in the first post the wrong output is gone...

This will be updated in the next update.

[wpa1160]

SELECT @@VERSION

---------------------------------------------------------------------------------
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)


(1 row(s) affected)

[Hemoco]

I can't reproduce this, which version of SQL/SP are you using?

[wpa1160]

Problem still exists even in the current release (4.0.0.42).
I've ran your query and it returns the same wrong result.

See screenshot for details; the duplicate licenses are correctly identified before the key "VYQTD".

[Hemoco]

Can you test this one:

Select Top 1000000 dbo.tblComputers.Computername,
  dbo.tblComputers.ComputerUnique, dbo.tblComputers.Domain, Web40OSName.OSname
  As OS, Duplicates.Product, Duplicates.ProductKey, Duplicates.Total,
  dbo.tblComputers.Lastseen, Web40OSName.Compimage As icon
From dbo.tblComputers Inner Join
  web40ActiveComputers On web40ActiveComputers.Computername =
    dbo.tblComputers.Computername Inner Join
  tblOperatingsystem On dbo.tblComputers.Computername =
    tblOperatingsystem.Computername Inner Join
  Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername
  Inner Join
  tblSerialnumber On dbo.tblComputers.Computername =
    tblSerialnumber.Computername Inner Join
  (Select Top 1000000 tblSerialnumber.Product, tblSerialnumber.ProductKey,
      Count(tblSerialnumber.SerialID) As Total, TsysSerials.SerialID
    From dbo.tblComputers Inner Join
      web40ActiveComputers On web40ActiveComputers.Computername =
        dbo.tblComputers.Computername Inner Join
      tblSerialnumber On dbo.tblComputers.Computername =
        tblSerialnumber.Computername Inner Join
      TsysSerials On TsysSerials.Product = tblSerialnumber.Product
    Group By tblSerialnumber.Product, tblSerialnumber.ProductKey,
      TsysSerials.SerialID
    Having (Count(tblSerialnumber.SerialID) > 1 And TsysSerials.SerialID = 1) Or
      (tblSerialnumber.Product Like 'Microsoft%')) Duplicates On
    Duplicates.ProductKey = tblSerialnumber.ProductKey And Duplicates.Product =
    tblSerialnumber.Product
Order By Duplicates.Total Desc, Duplicates.Product, dbo.tblComputers.Computer