Manager Name

[s_subramanian]

Hi,

I have a below report in which i wanted to add manager name and couldn't able to figure it out.

Please help in figuring it out


Select Top 1000000 tblAssets.AssetID,
tblAssetCustom.Custom9 As GPID,
tblAssetCustom.Custom10 As [Asset User Name],
tblADusers.Username As [AD.GPID],
tblADusers.Name As [AD.Username],
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblADusers.email,
tsysAssetTypes.AssetTypename As [Asset Type],
tblAssetCustom.Custom7 As [Asset Status],
tblAssetCustom.Manufacturer,
tblAssetCustom.Custom1 As [Asset Model],
tblAssetCustom.Custom2 As [Asset Category],
tblAssetCustom.Custom3 As Region,
tblAssetCustom.Custom4 As [FPR Unit],
tblAssetCustom.Custom5 As [Asset Location],
tblAssetCustom.Custom6 As [Sub Location],
tblAssetCustom.Custom8 As Assesment,
tblAssetCustom.Custom11 As [Location Type],
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.IPAddress As [IP address],
tsysIPLocations.IPLocation As [IP location],
tblADComputers.OU,
Case
When tblAssetCustom.Manufacturer Like '%lenovo%' Then
tblComputerSystemProduct.Version Else tblAssetCustom.Model End As Model,
tblAssets.Domain,
tsysOS.OSname As [OS name],
Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
End As [not affected by cleanup options],
tComputersystem.type
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblComputerSystemProduct On tblAssets.AssetID =
tblComputerSystemProduct.AssetID
Left Join (Select Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation' End As type,
tblComputersystem.AssetID
From tblComputersystem) tComputersystem On tblAssets.AssetID =
tComputersystem.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblADusers.Username <> tblAssetCustom.Custom9 And
IsNumeric(tblAssetCustom.Custom9) <> 0
Order By GPID

[Daniel_B · Accepted solution]

For this you need to add tblADusers again under a new alias to your report and link it through tblADUsers.ManagerADobjectID. Please find the modified report below.

Select Top 1000000 tblAssets.AssetID,
  tblAssetCustom.Custom9 As GPID,
  tblAssetCustom.Custom10 As [Asset User Name],
  tblADusers.Username As [AD.GPID],
  tblADusers.Name As [AD.Username],
  tManager.Username As [Manager],
  tblAssets.AssetName,
  tblAssetCustom.Serialnumber,
  tblADusers.email,
  tsysAssetTypes.AssetTypename As [Asset Type],
  tblAssetCustom.Custom7 As [Asset Status],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Custom1 As [Asset Model],
  tblAssetCustom.Custom2 As [Asset Category],
  tblAssetCustom.Custom3 As Region,
  tblAssetCustom.Custom4 As [FPR Unit],
  tblAssetCustom.Custom5 As [Asset Location],
  tblAssetCustom.Custom6 As [Sub Location],
  tblAssetCustom.Custom8 As Assesment,
  tblAssetCustom.Custom11 As [Location Type],
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.IPAddress As [IP address],
  tsysIPLocations.IPLocation As [IP location],
  tblADComputers.OU,
  Case
    When tblAssetCustom.Manufacturer Like '%lenovo%' Then
    tblComputerSystemProduct.Version Else tblAssetCustom.Model End As Model,
  tblAssets.Domain,
  tsysOS.OSname As [OS name],
  Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
  End As [not affected by cleanup options],
  tComputersystem.type
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblComputerSystemProduct On tblAssets.AssetID =
    tblComputerSystemProduct.AssetID
  Left Join (Select Case When tblComputersystem.Domainrole > 1 Then 'Server'
      Else 'Workstation' End As type,
    tblComputersystem.AssetID
  From tblComputersystem) tComputersystem On tblAssets.AssetID =
    tComputersystem.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
  Left Join tblADusers tManager On tblADusers.ManagerADObjectId =
    tManager.ADObjectID
Where tblADusers.Username <> tblAssetCustom.Custom9 And
  IsNumeric(tblAssetCustom.Custom9) <> 0
Order By GPID