cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
pscholz
Engaged Sweeper
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

5 REPLIES 5
pscholz
Engaged Sweeper
Thanks @RC62N! Works great!
RCorbeil
Honored Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper
@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
Honored Sweeper II
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.