Used By Report

[Gartski]

I'm after a report that shows all assets and who they are used by but I cant for the life of me find the used by table/field. I used a report be but this only seemed to show Windows machines

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

[MikeMc]

You can add 'tblOperatingsystem.Caption' (without quotes) to the select portion of the original query.

[dtsteinb]

Is there a way to add the OS Name to this report? I need like Windows 7 Pro.

[Susan_A · Accepted solution]

Your report is listing "owned by" relations, not "used by" relations. Replace "owned by" with "used by" in your query. Your report is only listing Windows computers as well because of the way your tables are linked. There are several tables in your report that only store Windows data. If you join two tables with an Inner Join, SQL will only display a result in the report output if there is a record in both connected tables. This is standard SQL behavior, not specific to Lansweeper. As you have Windows-only tables linked to tblAssets with an Inner Join, only Windows computers are listed.

You need to right-click on the links between tblAssets and the Windows-only tables below and tick "select all rows from tblAssets" to make non-Windows show up as well. Also right-click on the link between TsysChassisTypes and tblSystemEnclosure and tick "select all rows from tblSystemEnclosure".

• tblSystemEnclosure
  
• tblComputersystem
  
• tblADComputers
  
• tblADusers
  
• tblOperatingsystem