
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-01-2019 02:50 PM
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
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
Labels:
- Labels:
-
Report Center
5 REPLIES 5

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-06-2019 03:55 PM
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.
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.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-06-2019 12:49 PM
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
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-04-2019 03:42 PM
awesome. I do that all the time 🙂

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-04-2019 02:56 PM
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
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-01-2019 10:31 PM
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.
can you then post a screenshot of it being sorted by OU? I think something about the records must be different - probably OS name.
