List owner of an asset

[jimmern]

Hi

I use this report to list computers etc i Our AD.

Select Top 1000000 tblADusers.Company,
tblAssets.AssetName,
tblstate.Statename,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tsysAssetTypes.AssetTypename,
tblAssets.SP,
tblAssets.Domain,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
TsysChassisTypes.ChassisName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblADComputers.AssetID,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblOperatingsystem.InstallDate,
tblOperatingsystem.PlusProductID
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblstate On tblstate.State = tblAssetCustom.State
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblstate.Statename Like 'Active' And tblAssets.Lastseen > GetDate() - 90
Order By tblADusers.Company,
tblstate.Statename,
tsysIPLocations.IPLocation

And I need to add assetsrelation for these computer with the value "Owned By" , Can You help me to combine these two reports ?

[SHib11]

This is a great report.
I've been struggling to get a report like this made. (SQL Query Newbie)

This report only seems to list Windows servers. is there a way to adjust this to also include Linux servers?

[Susan_A · Accepted solution]

User links are stored in the tblAssetUserRelations database table. I've posted a modified report below. I recommend using our database dictionary to locate specific data in the database.

Select Top 1000000 tblADusers.Company,
  tblAssets.AssetName,
  tblState.Statename,
  tblAssets.Username,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tsysAssetTypes.AssetTypename,
  tblAssets.SP,
  tblAssets.Domain,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  TsysChassisTypes.ChassisName,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblADComputers.AssetID,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblOperatingsystem.InstallDate,
  tblOperatingsystem.PlusProductID,
  SubQuery.Name As Relation,
  SubQuery.Username As RelatedUser,
  SubQuery.Userdomain As RelatedUserdomain,
  SubQuery.Comments As RelationComments,
  SubQuery.StartDate As RelationStartdate
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
    TsysChassisTypes.Chassistype
  Inner Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join (Select Top 1000000 tblAssetUserRelations.AssetID,
    tsysAssetRelationTypes.Name,
    tblAssetUserRelations.Username,
    tblAssetUserRelations.Userdomain,
    tblAssetUserRelations.Comments,
    tblAssetUserRelations.StartDate
  From tblAssetUserRelations
    Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
      tblAssetUserRelations.Type
  Where tsysAssetRelationTypes.Name = 'owned by') SubQuery On SubQuery.AssetID =
    tblAssets.AssetID
Where tblState.Statename Like 'Active' And tblAssets.Lastseen > GetDate() - 90
Order By tblADusers.Company,
  tblState.Statename,
  tsysIPLocations.IPLocation