Adding ADUsers First name Last Name to a report

[Kevin_plack]

Our users log in using an employee number. In AD this employee number is tied to their First Name Last Name. I am trying to add user to a custom report. When i add username it gives me their employee number. When i add TblADUser to the report my query goes from 1600 to over 10000 because ADUser is querying separately from the rest of the information. Any ideas how to add First Name last name to the existing query without making it its own query?

Here is the current code in the report

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Scanserver,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.Serialnumber,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID,
tblADusers
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName Desc

[Kevin_plack]

That seems to work well. Thanks a lot!

[David_G]

You will have to make sure that the database table tblADusers is joined into the SQL query. We are using a left join as some users their AD information might not be filled in, and therefore would be missing out of the report. With the left join, these users without the AD info will still be in the report. If you only want to have information for AD users that have their information filled in, in AD, you can change the left joins to an inner join.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Scanserver,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.Serialnumber,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.Username
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName Desc