Help with Report. Duplication

[magson]

Hi All,

I have create a report that pulls all Windows 10 devices from every OU in the domain. What we have found is the report is creating duplicate OU enteries. Please can someone assist?

What is requires is a count against all OU's for windows win10 devices as follows -

Win 10 - COUNT - OU

Here is the code and a screen shot of the issue

Select Top 1000000 tsysOS.OSname,
Count(tblAssets.AssetID) As Total,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tsysOS.OSname Like '%Win 10%' And tblADComputers.OU Like '%GP%' And
tsysIPLocations.IPLocation Not Like '%CRH - IT Build VLAN%' And
tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tsysOS.OSname,
tblADComputers.OU,
tsysIPLocations.IPLocation
Order By Total Desc

[JacobH]

the links key off of the field name... if you want to have the asset name turned into a link to it, I believe you use tblAssets.AssetUnique for the field.... or.... tblAssets.AssetID, followed by
tblAssets.AssetName -

i can't remember them off the top of my head, but you can take a look at any report that has links to stuff (like asset types, software names, etc... and you use the same fields, or name them the same, and the links will appear in your report.

[magson]

I greatly appreciate the support on this.

I have one more question. Is it pissible on this report to add hostname but have the hostnames collected together as a link as apposed to individual fields as shown in the picture. I hope this makes sense

[JacobH]

awesome. I do that all the time 🙂

[magson]

Hi JacobH,

I made the change you advised and I do beleive its the answer. Here is the code and ScreenShot as requested

thank you ever so much. So obvious, that i couldnt see for looking

Select Top 1000000 tsysOS.OSname,
Count(tblAssets.AssetID) As Total,
tblADComputers.OU,
tblOperatingsystem.Caption
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where tsysOS.OSname Like '%Win 10%' And tblADComputers.OU Like '%GP%' And
tsysIPLocations.IPLocation Not Like '%CRH - IT Build VLAN%' And
tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tsysOS.OSname,
tblADComputers.OU,
tblOperatingsystem.Caption,
tsysIPLocations.IPLocation
Order By Total Desc

[JacobH]

can you add more columns that you are grouping by... like OS - and see what you get?

can you then post a screenshot of it being sorted by OU? I think something about the records must be different - probably OS name.