Duplication of Devices in Full OS Build Report

[justanotherbod]

Hi All

I hope everyone is well?

Im trying to run a report where I get the FULL OS build along with the OS version and some other fields.

But when I run the report, its seems to duplicate the "FULL BUILD" field (web50repnotlatestbuildwin10.Build). 

Im a bit of a noob when it comes to creating LS reports, but suspect it could be something to do with tables being/not linked, or perhaps the DISTINCT field, but I dont really know enough to resolve the issue and I need this report pretty urgently. 

Any help GREATLY appreciated. 

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Username,
tsysOS.OSname,
tsysOS.Image As icon,
tblOperatingsystem.Version As Build,
tblAssetCustom.Model,
Case tblAssets.OScode
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'
When '10.0.19042' Then '2009'
When '10.0.19043' Then '21H1'
When '10.0.19044' Then '21H2'
When '10.0.19045' Then '22H2'
Else '?'
End As Version,
tblAssets.Lastseen,
tblOperatingsystem.Caption As Caption1,
tblADComputers.OU,
web50repnotlatestbuildwin10.OS,
web50repnotlatestbuildwin10.Build As [Full Build],
web50repnotlatestbuildwin10.[OS Version]
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
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID,
web50repnotlatestbuildwin10
Where tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1
Order By Build,
tblAssets.AssetUnique

[FrankSc]

Hello, 
You are indeed joining on a view which causes the duplication.
The best is to remove that join on web50repnotlatestbuildwin10, the fields from that view, and start from there.
The report editor can help you further in selecting the additional fields you need and also to bring in extra tables.  
 

[justanotherbod]

Hi Frank

Really appreciate your swift response.

Iv tried a few things (like deleting the web50repnotlatestbuildwin10 from below the INNER JOIN line), but unable to find the fix to stop the duplicating.

Is there any chance you can elaborate further so I can better understand what you mean.

Apologies for not fully understanding. 

Thanks again, Dan

[Jacob_H]

Hey Justan -  reports are stored as SQL Views as you have probably guessed already - and you can definitely join on SQL views but I don't recommend doing that as  1) you can't see the entire query and 2) if someone edits that built-in report, any report referencing that view will potentially break or become inaccurate.

So, yeah I don't recommend doing it that way, though you totally can/could.  In your case, you aren't specifying a JOIN on that SQL View/Report, which makes it a Cross-Join so you're getting dupes because you don't have a JOIN condition.  

You need to JOIN on the AssetID:

Left Join web50repnotlatestbuildwin10 on tblAssets.AssetID = web50repnotlatestbuildwin10.AssetID

 that report/view you are joining is a complicated one for sure - you picked like the hardest one lol - as it calculates the build version using intermediate SQL code...   what I'd recommend, starting out, is to pick an existing report that has similar to what you need, SAVE AS a new report, and modify it accordingly - so you'd pick some report that has OS information in it....   unless you need the 'not latest build'  query...   in that case, honestly,  I'd copy that query and JOIN on the thing as an embedded SELECT versus reference the SQL view/report -   it's a lot messier but you won't mess things up down the road.   (i.e. LEFT JOIN (Select bla bla bla bla bla bla the original report/query) as BLAH on tblassets.assetid = BLAH.assetid)

hope that helps