How I get full Asset and Active Directory Information in report

[LocDP]

Hello Everyone,

I am a new Lansweeper user. I tried the below query to get the device information. In addition, I want to get more information about Department, Office, Title, Country...
I have tried linking table AD users but no success.
Can you help me add tables containing user information to the command below?

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.Image As icon,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.Memory,
  Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
  numeric) As nvarchar) As [Disk size],
  tblAssets.NrProcessors As #CPU,
  tblAssets.Processor,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Serialnumber As Serial,
  tblAssets.IPAddress As [IP Address],
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblAssets.Lastseen,
  tblAssets.Uptime,
  tblAssets.SP,
  tblBIOS.Version,
  tblAssets.Mac,
  tblAssetCustom.Department,
  tblDiskdrives.Freespace,
  tblDiskdrives.DriveType,
  tblAssets.SiteID,
  tblAssetCustom.Location,
  tblAssetCustom.Branchoffice,
  tblAssetCustom.Building,
  tblADComputers.Company
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1

Thank you very much

[Ian_Prentice · Accepted solution]

I think you are going to need to pull that info from the tblADUsers.  Link the username from tblAssets to the username on tblADUsers.

I have updated your code.  see if this helps:;

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.Image As icon,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.Memory,
  Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
  numeric) As nvarchar) As [Disk size],
  tblAssets.NrProcessors As #CPU,
  tblAssets.Processor,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Serialnumber As Serial,
  tblAssets.IPAddress As [IP Address],
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblAssets.Lastseen,
  tblAssets.Uptime,
  tblAssets.SP,
  tblBIOS.Version,
  tblAssets.Mac,
  tblAssetCustom.Department,
  tblDiskdrives.Freespace,
  tblDiskdrives.DriveType,
  tblAssets.SiteID,
  tblAssetCustom.Location,
  tblAssetCustom.Branchoffice,
  tblAssetCustom.Building,
  tblADusers.Office,
  tblADusers.Department As Department1,
  tblADusers.Company As Company1,
  tblADusers.Title
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1

[LocDP]

yeah it work, thank you very much

[LocDP]

Hi,

I still need your help. 

Hope can resolve soon

Thanks,

LocDP