Used By Report

[cmaddox]

Hello All,

So, we have diligently filled in the "used by" field and selected the users from our domain.  How do we then run a report that shows the asset and who is currently used by?

Thank you,

Cole.

[Hendrik_VE]

Tbh, I don't use the 'relationships' to report on system responsibilities. I've configured 3 custom fields with a drop-down list of persons that are responsible for certain tasks (eg. 'Network responsible', 'System Administrator', ...). It makes reporting and bulk assignments easier.

[StillGoing]

Thanks for this! I've been considering using these types of relationships to record and report on the delegation of systems admin and other IS responsibilities. Knowing how to efficiently extract this information based on the relationship name will be really helpful.

[Hendrik_VE · Accepted solution]

Hi,

Try this one (only for Windows assets I believe):

Select Top 1000000 tblAssets.AssetName,
  tblState.Statename,
  tblAssets.Username,
  tsysAssetTypes.AssetTypename,
  tblOperatingsystem.Caption,
  tblAssets.Domain,
  tblAssets.Lastseen,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  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 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 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 = 'used by') SubQuery On
    SubQuery.AssetID = tblAssets.AssetID
Where tblState.Statename Like 'Active'
Order By tblAssets.AssetName