→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
magson
Engaged Sweeper II
Hi All,

please can someone assist with this report? I dont know why but i am getting duplicate entries. can anyone see why?

Also i need to add a field that shows locally installed printers but im having no joy. Thank you


Select Top 1000000 tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblOperatingsystem.Caption,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
Where tblADComputers.OU Like '%OU=Overgate Hospice,DC=xthis,DC=nhs,DC=uk' And
tblAssetCustom.State = 1
1 REPLY 1
RCorbeil
Honored Sweeper II
On the "duplicate records" front, your final JOIN is saying "give me all the printers associated with each asset".

Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
You're not actually including any fields from tblPrinters in your report, but you're producing results for each of the associated printers, hence what appears to be the duplicate entries. If you don't want duplicate results, remove the JOIN to tblPrinters. If, on the other hand, you want a list of printers, add fields from tblPrinters to your report and the results will no longer look like duplicates.

As far as a field showing locally-installed printers, tblPrinters.Local should tell you that, e.g.
  tblPrinters.Caption,
CASE
WHEN tblPrinters.Local = 0 THEN 'No'
WHEN tblPrinters.Local = 1 THEN 'Yes'
END AS isLocal