
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-08-2019 08:14 AM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-08-2019 11:21 PM
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
5 REPLIES 5

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-11-2019 09:44 AM
Thanks @RC62N! Works great!
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-08-2019 11:21 PM
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
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-08-2019 08:14 PM
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.
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.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-08-2019 08:00 PM
@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
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
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-08-2019 05:50 PM
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.
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.
