Add displayname or email to existing report

[Morten]

Can someone help me adding user displayname and/or full email adress to the native report called "Asset: Out of warranty"

Default it looks like this :

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [Warranty Expiration] Desc


when i add "htblemailaccounts.displayname" i get 0 results - when i remove it i get 76

[Morten]

It works like a charm 🙂

Thank you

[brandon_jones · Accepted solution]

See if this give you what you want:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Domain,
tblADusers.Displayname,
tblADusers.email,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join lansweeperdb.dbo.tblADusers On tblAssets.Username =
tblADusers.Username
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [Warranty Expiration] Desc