All computers that have AD accounts

[Notonyourradar]

Trying to write up a report showing all computer in AD (Active scanning enabled) even if the computer isn't found on the network. I am able to get this information using the Dynamic Groups, but I need a report that only shows PC name, Last Scanned if applicable, and OS.

This is the report I've been working with but cannot show computers that are not able to be scanned, be it that they or offsite or other.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.OSname,
  tsysOS.Image As icon,
  tblAssets.Lastseen
From tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

Any help would be appreciated!

[Daniel_B · Accepted solution]

If you want to be shure that a domain account was scanned for the computers, make an INNER JOIN to tblADcomputers.
In order to find computers which are member of or have a function in a domain, use tblComputersystem.DomainRole.

The first option (inner join to tblADComputers):

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tsysOS.Image As icon,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

The second option (using tblComputersystem.DomainRole):

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tsysOS.Image As icon,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblComputersystem.Domainrole > 0 And tblAssetCustom.State = 1
Order By tblAssets.AssetName