List Active Directory Extension Attributes of All Users
Active directory extension attributes allow sysadmins to assign custom values to 15 fields by default. While really useful in specific use cases, managing which extension attributes have already been used, or which users have which attributes is much harder without a way to audit all extension attributes in your IT environment. By scanning your users with Lansweeper, detailed active directory information is scanned to give you these details.
The report below gives an overview of all users along with any AD extension attributes they might have. This way you can avoid potential conflict with previous attributes used or find all the values for a specific attribute.
AD Extension Attributes Query
Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
extension1.Value As ExtensionAttribute1,
extension2.Value As ExtensionAttribute2,
extension3.Value As ExtensionAttribute3,
extension4.Value As ExtensionAttribute4,
extension5.Value As ExtensionAttribute5,
extension6.Value As ExtensionAttribute6,
extension7.Value As ExtensionAttribute7,
extension8.Value As ExtensionAttribute8,
extension9.Value As ExtensionAttribute9,
extension10.Value As ExtensionAttribute10,
extension11.Value As ExtensionAttribute11,
extension12.Value As ExtensionAttribute12,
extension13.Value As ExtensionAttribute13,
extension14.Value As ExtensionAttribute14,
extension15.Value As ExtensionAttribute15
From tblADusers
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute1') As extension1 On
extension1.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute2') As extension2 On
extension2.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute3') As extension3 On
extension3.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute4') As extension4 On
extension4.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute5') As extension5 On
extension5.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute6') As extension6 On
extension6.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute7') As extension7 On
extension7.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute8') As extension8 On
extension8.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute9') As extension9 On
extension9.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute10') As extension10 On
extension10.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute11') As extension11 On
extension11.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute12') As extension12 On
extension12.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute13') As extension13 On
extension13.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute14') As extension14 On
extension14.AdObjectId = tblADusers.ADObjectID
Left Join (Select tblAdProperty.Value,
tblAdProperty.AdObjectId
From tblAdProperty
Inner Join tsysAdPropertyType On tsysAdPropertyType.TypeId =
tblAdProperty.TypeId
Where tsysAdPropertyType.Name = 'ExtensionAttribute15') As extension15 On
extension15.AdObjectId = tblADusers.ADObjectID
