→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
s_subramanian
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
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