06-15-2023 03:17 PM - last edited on 05-21-2024 04:17 PM by Riley
Total asset count: 1350. 75% of these are "owned by" and the other 25% are not owned, as they are in a classroom. I created a report based on the information below, which I pulled from https://community.lansweeper.com/t5/general-discussions/used-by-report/m-p/22400
When I ran the report, I only received around 400 assets and some didn't even have an "owned by" entry, even though there is an owner listed for the asset.
Can someone tell me where I went wrong? Please and thanks!!
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
Solved! Go to Solution.
06-16-2023 10:02 PM
Are all the assets assigned to an AD domain or are some standalone?
06-16-2023 10:02 PM
Are all the assets assigned to an AD domain or are some standalone?
06-19-2023 04:19 PM
Standalone was the issue. Thanks so much!
06-16-2023 09:58 PM
That allowed around 20 more to come into the report.
06-16-2023 01:54 PM
User/OU/AD group relations.
06-16-2023 06:43 PM
Remove the "Like 'Active'" entry from tblState.Statename and see what shows up.
06-15-2023 07:34 PM - edited 06-15-2023 07:36 PM
Where is your "owned by" relation setup in? Asset Relations or User/OU/AD group relations?
The report is pulling from the latter.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now