Report request for MS inventory

[khaleehh]

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 ?

[Daniel_B]

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]

can we collect two monitors in one row !

[Daniel_B · Accepted solution]

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, '') = '')