→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Reen87
Engaged Sweeper II
Hello all. I have a small report problem. I have one report that maps all the curent active user/asset relationships.

but I also need this report totake older relationships. (aka. if a user has 1 laptop, and 1 desktop. this report only shows the one with the latest date).

anybody that can help me with this? I am not used too sql.

Select Top 1000000 tblAssetUserRelations.StartDate,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tblAssetUserRelations.Comments,
tblADusers.Lastname,
tblADusers.Firstname,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetRelations
On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Where tblAssetCustom.State = 1
Group By tblAssetUserRelations.StartDate,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tblAssetUserRelations.Comments,
tblADusers.Lastname,
tblADusers.Firstname,
tblAssets.Username
Order By tblAssetUserRelations.StartDate Desc,
tblADusers.Lastname
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
In your report you had joined tblAssetRelations as well. That might have prevented some assets to be displayed. Asset-user-relations are stored in tblAssetUserRelations only. Please find a modified report below.

Select Top 1000000 tblAssetUserRelations.StartDate,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tsysAssetRelationTypes.Name As Relation,
tblAssetUserRelations.Comments,
tblADusers.Lastname,
tblADusers.Firstname,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetUserRelations.Type
Where tblAssetCustom.State = 1
Order By tblAssetUserRelations.StartDate Desc,
tblADusers.Lastname

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
In your report you had joined tblAssetRelations as well. That might have prevented some assets to be displayed. Asset-user-relations are stored in tblAssetUserRelations only. Please find a modified report below.

Select Top 1000000 tblAssetUserRelations.StartDate,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tsysAssetRelationTypes.Name As Relation,
tblAssetUserRelations.Comments,
tblADusers.Lastname,
tblADusers.Firstname,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetUserRelations.Type
Where tblAssetCustom.State = 1
Order By tblAssetUserRelations.StartDate Desc,
tblADusers.Lastname