Report duplication assets only VMs

[stanislav_tsenk]

Hi to all.

I'm creating report that need to have CPU, speed, number cores, RAM, Ip, MAC, system hdd size, serial, and MB model.

It seems that when i put tblProcessor the VM-s in report are duplicating. No problem with laptops and desktops.


Can someone help for workaround?
Thanks!

This is the report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Mac,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblDiskdrives.Caption As Drive,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
tblBaseBoard.Product As MBModel,
tblBaseBoard.SerialNumber As MBSerial,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.Processor,
tblProcessor.NumberOfCores,
tblProcessor.MaxClockSpeed
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join lansweeperdb.dbo.tblBaseBoard On tblAssets.AssetID =
tblBaseBoard.AssetID
Inner Join lansweeperdb.dbo.tblProcessor On tblAssets.AssetID =
tblProcessor.AssetID
Where tblDiskdrives.Caption = 'C:' And tblAssets.Lastseen > GetDate() - 90

[prighi61]

As you could have more than one processor record you should summarize data, maybe this way (otherwise you should restrict it to CPU0 only):

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Mac,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblDiskdrives.Caption As Drive,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
tblBaseBoard.Product As MBModel,
tblBaseBoard.SerialNumber As MBSerial,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.Processor,
Sum(tblProcessor.NumberOfCores) As TotalNumberOfCores,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join lansweeperdb.dbo.tblBaseBoard On tblAssets.AssetID =
tblBaseBoard.AssetID
Inner Join lansweeperdb.dbo.tblProcessor On tblAssets.AssetID =
tblProcessor.AssetID
Where tblDiskdrives.Caption = 'C:' And tblAssets.Lastseen > GetDate() - 90
group by tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Mac,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblDiskdrives.Caption,
tblDiskdrives.Size,
tblBaseBoard.Product,
tblBaseBoard.SerialNumber,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.Processor

[stanislav_tsenk]

Thanks prighi61

Working as expected 🙂

prighi61 wrote:
As you could have more than one processor record you should summarize data, maybe this way (otherwise you should restrict it to CPU0 only):

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Mac,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblDiskdrives.Caption As Drive,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
tblBaseBoard.Product As MBModel,
tblBaseBoard.SerialNumber As MBSerial,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.Processor,
Sum(tblProcessor.NumberOfCores) As TotalNumberOfCores,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join lansweeperdb.dbo.tblBaseBoard On tblAssets.AssetID =
tblBaseBoard.AssetID
Inner Join lansweeperdb.dbo.tblProcessor On tblAssets.AssetID =
tblProcessor.AssetID
Where tblDiskdrives.Caption = 'C:' And tblAssets.Lastseen > GetDate() - 90
group by tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Mac,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblDiskdrives.Caption,
tblDiskdrives.Size,
tblBaseBoard.Product,
tblBaseBoard.SerialNumber,
tblAssets.Lastseen,
tblAssets.Memory,
tblAssets.Processor