Report with duplicates

[pscholz]

Hi,

I'm creating a Report with all Windows 7 Computers and their Hardware. Im getting multiple lines per Asset. What am I missing?

Thanks!

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblComputersystem.Lastchanged,
tblComputersystem.SystemType,
ProcCapacity.CPU,
ProcCapacity.Name,
ProcCapacity.MaxClockSpeed As ClockSpeed,
tblAssets.Memory,
tblComputersystem.Model,
tblFloppy.Model As Model1,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.Serialnumber
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tsysOS.OSname Like 'Win 7' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[pscholz]

Thanks @RC62N! Works great!

[RCorbeil · Accepted solution]

Since you're prepared to reduce the tblFloppy reference to just the first physical drive, with an eye to simplifying things you might consider replacing your CPU subselect with a simple JOIN ON tblProcessor.DeviceID='CPU0'. Just a thought.

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tblComputersystem.Lastchanged,
  tblComputersystem.SystemType,
  tblPROCESSOR.Name As CPU,
  tblPROCESSOR.Caption As Name,
  tblPROCESSOR.MaxClockSpeed As ClockSpeed,
  tblAssets.Memory,
  tblComputersystem.Model,
  tblFloppy.Model As Model1,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  'http://www.dell.com/support/my-support/us/en/04/product-support/servicetag/' + tblAssetCustom.Serialnumber + '?show_warranty_model=true' As hyperlink_SerialNumber1,
  tblAssetCustom.Serialnumber As hyperlink_name_SerialNumber1,
  tblFloppy.Name As Name1
From tblAssets
  Inner Join tblPROCESSOR On tblPROCESSOR.AssetID = tblAssets.AssetID And
    tblPROCESSOR.DeviceID = 'CPU0'
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID And
    tblFloppy.Name Like '\\.\PHYSICALDRIVE0'
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tsysOS.OSname Like 'Win 7' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[RCorbeil]

I don't think there's an inherent link reference you can pull from the database (someone from the LANsweeper support team please correct me if I'm wrong). Barring a correction, refer to this page for information on inserting your own links into reports.

If you right-click the link on the asset page, you can get the URL for that asset's serial number. Use the instructions on the page referenced above to build a link yourself, dynamically inserting the assest's serial number.

[pscholz]

@RC62N thanks for that and the second pointer! put in another filter..

is there any way to display the serial as a link to the dell support page (with warranty infos / dell service tag), like on the asset page.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblComputersystem.Lastchanged,
tblComputersystem.SystemType,
ProcCapacity.CPU,
ProcCapacity.Name,
ProcCapacity.MaxClockSpeed As ClockSpeed,
tblAssets.Memory,
tblComputersystem.Model,
tblFloppy.Model As Model1,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.Serialnumber,
tblFloppy.Name As Name1
From tblAssets
Inner Join (Select tblAssets.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Group By tblAssets.AssetID) ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tsysOS.OSname Like 'Win 7' And tblFloppy.Name Like '\\.\PHYSICALDRIVE0'
And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[RCorbeil]

You're linking against tblDiskDrives, but not using anything from the table, so you're seeing redundant results for each drive in that table. Remove that JOIN from your query.

You'll still get multiple records per asset, but that's because of the link against tblFloppy -- you're requesting one result record for each drive -- so the result records aren't redundant.