cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
khaleehh
Engaged Sweeper II
Hey ,
I need report to meet the attached excel sheet table as following :
https://www.dropbox.com/s/084i7rbhdp7fq9l/MS%20Inventory%20form.xlsx?dl=0


AssetID
Employee Name > Relations (Owned By)
Computer Name > {computer}
SAP Asset code > Custom03
Service TAG > Serial
Type > Asset Type
CPU > Processor
RAM > Memory
HHD > Hardisk Size
Monitor > Relations (Connected Monitors)
Serial# for monitor > (Serial) for connected monitor
SAP Asset Code > Custom03 for connected monitor
Docking Station > Relations (Connected Docking Stations Model)
Serial# for Docking Station > (Serial) for connected Docking Station
SAP Asset Code > Custom03 for connected Docking Station
OS > OS Type
OS Product Key (if possible) or just the version
MS Office Key (if possible) or just the version
MS Visio Key (if possible) or just the version
MS Project Key (if possible) or just the version
Adobe Key (if possible) or just the version

Please can you help ?
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
We recommend using the following report, which will not include product keys, and to list these in another report like the built-in report License: Software licensekey overview


Select Top 1000000 tblAssetUserRelations.Username As [Employee Name],
tblAssets.AssetID,
tblAssets.AssetName As Computer,
tblAssetCustom.Custom3 As [SAP Asset code],
tblAssetCustom.Serialnumber As [Service Tag],
Case When tblAssets.AssetID In (Select tblPortableBattery.AssetID
From tblPortableBattery) Then tblAssetCustom.Model Else 'o'
End As [Model Laptop],
Case When tblAssets.AssetID In (Select tblPortableBattery.AssetID
From tblPortableBattery) Then 'o' Else tblAssetCustom.Model
End As [Model Desktop],
tblAssets.Processor As CPU,
tblAssets.Memory As RAM,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) + 'GB' As HDD,
tCustomMonitor.Model As [Monitor Model],
tCustomMonitor.Serialnumber As [Monitor Serial#],
tCustomMonitor.Custom3 As [Monitor SAP Asset code],
tsysOS.OSname As OS,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblAssetUserRelations
On tblAssets.AssetID = tblAssetUserRelations.AssetID
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Left Join tblAssetRelations
On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssets tAssetsMonitor On tAssetsMonitor.AssetID =
tblAssetRelations.ChildAssetID
Inner Join tsysAssetTypes tTypeMonitors On tAssetsMonitor.Assettype =
tTypeMonitors.AssetType
Inner Join tblAssetCustom tCustomMonitor On tAssetsMonitor.AssetID =
tCustomMonitor.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1 And (tblAssetUserRelations.Type = 1 Or
Coalesce(tblAssetUserRelations.Type, '') = '') And
tblComputersystem.Domainrole < 2 And tblDiskdrives.Caption = 'c:' And
(tTypeMonitors.AssetTypename = 'Monitor' Or
Coalesce(tTypeMonitors.AssetTypename, '') = '')

View solution in original post

3 REPLIES 3
Daniel_B
Lansweeper Alumni
SQL server doesn't really support giving out several result rows of the same query in a single column or row. You can find an example here in our report center, but it won't run on SQL compact.
khaleehh
Engaged Sweeper II
can we collect two monitors in one row !
Daniel_B
Lansweeper Alumni
We recommend using the following report, which will not include product keys, and to list these in another report like the built-in report License: Software licensekey overview


Select Top 1000000 tblAssetUserRelations.Username As [Employee Name],
tblAssets.AssetID,
tblAssets.AssetName As Computer,
tblAssetCustom.Custom3 As [SAP Asset code],
tblAssetCustom.Serialnumber As [Service Tag],
Case When tblAssets.AssetID In (Select tblPortableBattery.AssetID
From tblPortableBattery) Then tblAssetCustom.Model Else 'o'
End As [Model Laptop],
Case When tblAssets.AssetID In (Select tblPortableBattery.AssetID
From tblPortableBattery) Then 'o' Else tblAssetCustom.Model
End As [Model Desktop],
tblAssets.Processor As CPU,
tblAssets.Memory As RAM,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) + 'GB' As HDD,
tCustomMonitor.Model As [Monitor Model],
tCustomMonitor.Serialnumber As [Monitor Serial#],
tCustomMonitor.Custom3 As [Monitor SAP Asset code],
tsysOS.OSname As OS,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblAssetUserRelations
On tblAssets.AssetID = tblAssetUserRelations.AssetID
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Left Join tblAssetRelations
On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssets tAssetsMonitor On tAssetsMonitor.AssetID =
tblAssetRelations.ChildAssetID
Inner Join tsysAssetTypes tTypeMonitors On tAssetsMonitor.Assettype =
tTypeMonitors.AssetType
Inner Join tblAssetCustom tCustomMonitor On tAssetsMonitor.AssetID =
tCustomMonitor.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1 And (tblAssetUserRelations.Type = 1 Or
Coalesce(tblAssetUserRelations.Type, '') = '') And
tblComputersystem.Domainrole < 2 And tblDiskdrives.Caption = 'c:' And
(tTypeMonitors.AssetTypename = 'Monitor' Or
Coalesce(tTypeMonitors.AssetTypename, '') = '')