Adobe Report with Manger of Each User

[dillont]

Hello,

I'm trying to create an report that will pull all workstation and show me what Adobe software is installed. Everything works fine but I want to know what the name of their managers name. This is the report I'm working with and I can't get it to pull in the managers name.

Thanks,
Dillon

Select Top 1000000 tblAssets.AssetName,
tblAssets.Username,
tblState.Statename As [Asset state],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblOperatingsystem.Caption As OS,
tSoftware.softwareName,
tSoftware.SoftwarePublisher,
tSoftware.softwareVersion,
tblADusers.Name,
tblADusers.ManagerADObjectId,
tblAssets.AssetID,
tblAssets.Domain
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.softwareName Like '%adobe%') Or
(tblSoftwareUni.SoftwarePublisher Like '%adobe%')) tSoftware
On tSoftware.AssetID = tblAssets.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Order By tblAssets.AssetName,
tSoftware.softwareName

[Daniel_B · Accepted solution]

@dillont: Do you need the manager of the user who was logged on to the computer or the manager of the computer? If you need the manager of the computer, you need to add table tblADComputers to your report and afterwards do the same what esr explained between tblADComputers and tblADUsers (or alternatively tblADObjects) in order to list the name of the manager. The link between AD users or AD computers and their managers always happens through the field "ManagerADObjectID".

The following is a modification of your report:

Select Top 1000000 tblAssets.AssetName,
  tblAssets.Username,
  tblState.Statename As [Asset state],
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Lastseen,
  tblOperatingsystem.Caption As OS,
  tSoftware.softwareName,
  tSoftware.SoftwarePublisher,
  tSoftware.softwareVersion,
  tblADusers.Name,
  tblADusers.ManagerADObjectId,
  tblAssets.AssetID,
  tblAssets.Domain,
  tblADusersManager.Displayname As [AD manager]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblSoftware.AssetID,
    tblSoftwareUni.softwareName,
    tblSoftwareUni.SoftwarePublisher,
    tblSoftware.softwareVersion
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Where (tblSoftwareUni.softwareName Like '%adobe%') Or
    (tblSoftwareUni.SoftwarePublisher Like '%adobe%')) tSoftware
    On tSoftware.AssetID = tblAssets.AssetID
  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username And
    tblAssets.Userdomain = tblADusers.Userdomain
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblADusers tblADusersManager On tblADusersManager.ADObjectID =
    tblADComputers.ManagerADObjectId
Order By tblAssets.AssetName,
  tSoftware.softwareName

[esr]

You're close-
tblADusers.ManagerADObjectId only shows the AD Object ID, a numeric value, and for your use you'll need to translate that to the Managers Name which is found at tblADObjects.sAMAccountName

So, don't show the ManagerADObjectId, rather Left Join the two together and display the sama account name instead. In the following example the Left Join will allow results even if the manager is blank. Inner Join would only show results where the manager is populated.

example:

Select Top 1000000 tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetName,
  tblAssets.Username,
  tblADObjects.sAMAccountName As Manager,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
  Left Join tblADObjects On tblADObjects.ADObjectID =
    tblADusers.ManagerADObjectId
Where tblAssetCustom.State = 1