→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

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

View solution in original post

2 REPLIES 2
Daniel_B
Lansweeper Alumni
@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
Champion Sweeper
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