Domain Computers listing Last User & Department

[Drexilla]

Hi all,

I'm relatively new to this and could use some help.

I'm trying to fine tune this report to show computers in AD and want a column for the last seen users' departments polled from AD.

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tblAssets.Username,
tsysOS.OSname,
tblAssets.Lastseen As [Last Seen],
tblAssets.Lasttried,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As Serial,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.Image As icon,
tblAssets.IPAddress
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblAssets.Lastseen > GetDate() - 60 And tblAssetCustom.PurchaseDate Is
Not Null And tblAssetCustom.State = 1) Or
(tblAssetCustom.Warrantydate Is Not Null And tblAssetCustom.State = 1)
Order By [Last Seen] Desc,
[Warranty Expiration] Desc

Any help would be much appreciated?

[Nick_VDB · Accepted solution]

The department information for users scanned from AD is stored in the tblADusers table. We added this table to your report below.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 tblAssetCustom.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Username,
  tsysOS.OSname,
  tblAssets.Lastseen As [Last Seen],
  tblAssets.Lasttried,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber As Serial,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tsysOS.Image As icon,
  tblAssets.IPAddress,
  tblADusers.Department
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADusers On tblAssets.Username = tblADusers.Username And
    tblAssets.Userdomain = tblADusers.Userdomain
Where (tblAssets.Lastseen > GetDate() - 60 And tblAssetCustom.PurchaseDate Is
  Not Null And tblAssetCustom.State = 1) Or
  (tblAssetCustom.Warrantydate Is Not Null And tblAssetCustom.State = 1)
Order By [Last Seen] Desc,
  [Warranty Expiration] Desc