cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Adding an "Employee ID" column to an asset report

ABaker
Engaged Sweeper
I have the below custom report that shows each computer and the software installed on them. It also shows the last logged-on user for each computer.

I want to add a column to the report that also shows the "Employee ID" for the last logged-on user of each computer. I know the "Employee ID" is in tblADusers, but I can't figure out how to join that table to the report to achieve what I'm trying to do.

My report-building knowledge is somewhat limited, so any insight would be very appreciated.

Here's my existing report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber As Serial,
tblAssets.IPAddress As [IP Address],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Lastseen,
tblAssets.Firstseen,
tsysIPLocations.IPLocation,
tblSoftwareUni.softwareName,
tsysOS.OSname,
tblSoftware.softwareVersion
From tblAssets
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetID
1 ACCEPTED SOLUTION

RCorbeil
Honored Sweeper II
If you check the database documentation, you'll see that Userdomain and Username together form the primary key for tblADUsers. To link in tblADUsers, then,
LEFT JOIN tblADUsers ON tblADUsers.Userdomain=tblAssets.Userdomain AND tblADUsers.Username=tblAssets.Username

Once you've added tblADUsers to your query you can add the field(s) you need to your report.

View solution in original post

2 REPLIES 2

RCorbeil
Honored Sweeper II
If you check the database documentation, you'll see that Userdomain and Username together form the primary key for tblADUsers. To link in tblADUsers, then,
LEFT JOIN tblADUsers ON tblADUsers.Userdomain=tblAssets.Userdomain AND tblADUsers.Username=tblAssets.Username

Once you've added tblADUsers to your query you can add the field(s) you need to your report.

RC62N wrote:
If you check the database documentation, you'll see that Userdomain and Username together form the primary key for tblADUsers. To link in tblADUsers, then,
LEFT JOIN tblADUsers ON tblADUsers.Userdomain=tblAssets.Userdomain AND tblADUsers.Username=tblAssets.Username

Once you've added tblADUsers to your query you can add the field(s) you need to your report.


That worked perfectly, thank you. I had tried linking it by Username and Userdomain previously using the GUI, but I must have done it incorrectly. Adding your line under "From tblAssets" worked perfectly. Thanks for your quick reply!