User Relation reports

[AquaGoat]

Hello,

We are trying to use Lansweeper to track which assets are 'used by' our AD users.

I can report on which assets have user assets set to 'used by' or 'borrowed by' pretty easily.

My problem arises with End Dates, and past relationships. To report assets that are available to be assigned I need a report that can show all assets that have No User Relationships OR All User Relationships have an End Date.

Any help creating this report?

[AquaGoat]

Replying to my own question, as I have figured out a working solution. Not sure if there is a better way to do this, but if there is I'm all ears.

Anyways here is my report:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As [Phone Type],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Custom2 As Carrier,
tblAssetCustom.Custom3 As [Phone Num],
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Full Join (Select Top 100000 tblAssetUserRelations.RelationID,
tblAssetUserRelations.AssetID,
tblAssetUserRelations.EndDate
From tblAssetUserRelations
Where tblAssetUserRelations.EndDate Is Not Null) SubTest1 On
tblAssets.AssetID = SubTest1.AssetID
Where tsysAssetTypes.AssetTypename = 'iPhone' And tblAssetCustom.State = 1 And
(Select COUNT(*) From tblAssetUserRelations
Where tblAssetUserRelations.AssetID = tblAssets.AssetID And
tblAssetUserRelations.EndDate Is Null) < 1
Order By tblAssets.AssetName

I just do a Select COUNT(*) and specify that I only want Active, iPhones, with less than 1 user relationship with no EndDate. If there is one relationship with no EndDate then we know the phone is still in use.