cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Lance
Engaged Sweeper
Hi,

I've seen following report and in need of adding more info to it. I'm trying to add Fist name, Last Name and email address attached to username.

I need to cross reference with other sheet but having just username from this report greatly increases time to match with other sheet.

Below is current report

Thank you!

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblOperatingsystem.Caption As [Operating System],
tblAssets.SP As SP,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tsysOS.OSname = 'Win XP' Then '#ffadad'
When tsysOS.OSname = 'Win 7' And tblAssets.SP <> 1 Then '#ffdb8e'
When tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then '#ffbe38'
When GetDate() > '12/14/2019' And GetDate() < '01/14/2020' And
tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then '#ff8b00'
When GetDate() > '01/14/2020' And tsysOS.OSname = 'Win 7' And
tblAssets.SP = 1 Then '#ffadad'
Else '#d4f4be'
End As backgroundcolor,
Case
When tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then DateDiff(dd,
GetDate(), '01/14/2020')
End As DaysRemaining
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName
1 REPLY 1
RCorbeil
Honored Sweeper II
LEFT JOIN tblADusers ON tblADUsers.Userdomain = tblAssets.Userdomain
AND tblADusers.Username = tblAssets.Username

Once tblADUsers is linked in, you should have access to the first & last names, email address, etc. assuming that information is populated in your AD.