Shorten Active Directory user and computer OUs

[Susan_A]

The report below lists your Windows domain computers, their Active Directory OU, their last logged on user and their last logged on user's OU. The OUs are shortened, e.g. from "OU=Servers,DC=lansweeper,DC=local" to "Servers".

The report will only list assets that meet all of the following criteria:

• The asset is a Windows computer.
  
• The computer's state is set to "active".
  
• The computer has been successfully scanned at least once.
  
• The computer is part of a domain.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  SubString(SubString(tblADComputers.OU, CharIndex(',', tblADComputers.OU) -
  250, 250), 4, 250) As ShortComputerOU,
  tblAssets.Username,
  tblAssets.Userdomain,
  SubString(SubString(tblADusers.OU, CharIndex(',', tblADusers.OU) - 250, 250),
  4, 250) As ShortUserOU,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.PartOfDomain = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[mmeetze]

Would it be possible to also add a column for uptime in this report?

[lance321]

Thank you, this was very helpful. I was searching all around on how to shorten the OUs.