cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mistymadewell
Engaged Sweeper III

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

 

 

1 ACCEPTED SOLUTION
rader
Champion Sweeper III

Are all the assets assigned to an AD domain or are some standalone?

View solution in original post

6 REPLIES 6
rader
Champion Sweeper III

Are all the assets assigned to an AD domain or are some standalone?

mistymadewell
Engaged Sweeper III

Standalone was the issue.  Thanks so much!

mistymadewell
Engaged Sweeper III

That allowed around 20 more to come into the report.

mistymadewell
Engaged Sweeper III

User/OU/AD group relations.

rader
Champion Sweeper III

Remove the "Like 'Active'" entry from tblState.Statename and see what shows up.

rader
Champion Sweeper III

Where is your "owned by" relation setup in? Asset Relations or User/OU/AD group relations?

The report is pulling from the latter.

2023-06-15 10_32_53-HistoriaNAS (DS3617xs) — Mozilla Firefox.png