Custom OID Report

[bramassendorp]

Hi,

Is it possible to get all scanned custom oid's from the same asset on one row in a Lansweeper report?

At the moment I only have the possibility to have every custom scanned oid on a separate row.

Thank you.

[Amberion · Accepted solution]

2 years have passed, but the topic is still relevant.
Since Comrade Brahma did not attach any data, I will post my code.
Perhaps this code will help other users write their report by analogy.

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tsysassettypes.AssetType,
  tblUps.Manufacturer,
  tblUps.Model,
  tblUps.Name,
  Case
    When tblUps.BatteryStatus = 1 Then 'Unknown'
    When tblUps.BatteryStatus = 2 Then 'Normal'
    When tblUps.BatteryStatus = 3 Then 'Low'
    When tblUps.BatteryStatus = 4 Then 'Depleted'
  End As BatteryStatus,
  tblUps.EstimatedMinutesRemaining,
  tblUps.UpsSoftwareVersion,
  tblUps.AgentSoftwareVersion,
  tblUps.EstimatedChargeRemaining,
  tblassets.Lastseen,
  tblassets.Lasttried,
  Case
    When tblUps.BatteryStatus = 3 Then '#f7f0ca'
    When tblUps.BatteryStatus = 4 And tblUps.BatteryStatus = 1 Then '#ffcccc'
    Else '#ccffcc'
  End As backgroundcolor,
  SN.Data As sn,
  ca.Data As Capacity,
  rr.Data As RuntimeRemaining,
  ol.Data As OutputLoad,
  lr.data as LastReplaced
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join lansweeperdb.dbo.tblUps On tblassets.AssetID = tblUps.AssetId
  Left Join (Select tblassets.AssetID,
      tbloiddata.Label,
      tbloiddata.Data
    From tblassets
      Inner Join tbloiddata On tblassets.AssetID = tbloiddata.AssetID
    Where tbloiddata.Label Like 'sn') As SN On tblassets.AssetID = SN.AssetID
  Left Join (Select tblassets.AssetID,
      tbloiddata.Label,
      tbloiddata.Data
    From tblassets
      Inner Join tbloiddata On tblassets.AssetID = tbloiddata.AssetID
    Where tbloiddata.Label Like 'Capacity') As ca On tblassets.AssetID =
      ca.AssetID
  Left Join (Select tblassets.AssetID,
      tbloiddata.Label,
      tbloiddata.Data
    From tblassets
      Inner Join tbloiddata On tblassets.AssetID = tbloiddata.AssetID
    Where tbloiddata.Label Like 'RuntimeRemaining') As rr On tblassets.AssetID =
      rr.AssetID
  Left Join (Select tblassets.AssetID,
      tbloiddata.Label,
      tbloiddata.Data
    From tblassets
      Inner Join tbloiddata On tblassets.AssetID = tbloiddata.AssetID
    Where tbloiddata.Label Like 'OutputLoad') As ol On tblassets.AssetID =
      ol.AssetID
  Left Join (Select tblassets.AssetID,
      tbloiddata.Label,
      tbloiddata.Data
    From tblassets
      Inner Join tbloiddata On tblassets.AssetID = tbloiddata.AssetID
    Where tbloiddata.Label Like 'LastReplaced') As lr On tblassets.AssetID =
      lr.AssetID
Where tblassetcustom.State = 1