Active Directory Extension Attributes Audit

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