Auditando usuarios de Active Directory con Excel

Con la necesidad de hacer big data de usuarios de ActiveDirectory , me encontré con la genial posibilidad de generar un listado de usuarios desde Excel:

Datos -> Obtener Datos -> De otras fuentes -> De Active Directory

Especifico el dominio, busco user (y lo selecciono) y le damos cargar. Usamos "user" porque queremos saber sobre usuarios únicamente

Hasta ahí obtengo una información básica, pero siempre necesitamos mas:

Sobre la tabla , vamos a Herramientas- > Consulta -> Editar 

Nos lleva al editor de PowerQuery

 

Elimino las columnas innecesarias

Comienzo a completar con datos que me son de utilidad (para mi caso) expandiendo las columnas y seleccionando los campos:

• user
•     lastLogon = último log on del usuario (formato timestamp)
  
•     pwdLastSet = última vez que se configuró una clave al usuario (formato timestamp)
•     userAccountControl = Estado (flags) sobre el usuario (Cuenta -> Opciones de Cuenta)
  
• organizationalPerson
•     givenName = Nombre (General -> Nombre de Pila)
•     title = Puesto en la organización (Organización -> Puesto)
  
• person
•     sn = Apellido (General -> Apellidos)
  
• top
•     whenChanged = Cuando cambió el objeto*
•     whenCreated = Cuando fue creado el objeto
  
• securityPrincipal
•     sAMAccountName = nombre de usuario (Cuenta -> Nombre de inicio de sesión del usuario)
  
• mailRecipient
•     info = Descripción del user (General -> Descripción)
  
•     mail = correo asignado (General -> Correo Electrónico)
  
•     telephoneNumber = número de teléfono (General -> Número de Tel.)

 

Estos datos se visualizan también en las propiedades del objeto desde Usuarios y Equipos de Active Directory, especificado entre paréntesis (Ficha -> Campo)

*  Es una propiedad que presta confusión ya que solo se origina cuando hay cambios en el objeto y es por cada DC (ya que los demás replica) generando incongruencias. Ejemplo es con el cambio de grupos (ver detalle en referencia)

 

Convirtiendo los timestamp en formato humano legible 

(uso fórmulas en PowerQuery para transofrmar el campo)

1. Divido por 864000000000
2. Restar 109205
3. Reemplazar -109205 por null
4. Convertir en formato fecha

 

Interpretación del userAccountControl

(uso fórmulas en PowerQuery para transformar el campo)

1. Convierto en formato texto para poder realizar los reemplazos
2.  Reemplazo según la siguiente tabla:

Código	Representa
512	Habilitado
514	Deshabilitado
66048	Habilitado – Clave no caduca
66050	Deshabilitado – Clave no caduca
66082	Deshabilitado – Cuenta de confianza

De la tabla provista por Microsoft, los valores se conforman por suma (strange way of life 😐), por ejemplo:

512 (Usuario Habilitado) + 2 (Flag de deshabilitado) = 514 (Usuario Deshabilitado)

 

Referencias:

• https://stackoverflow.com/questions/57604513/active-directory-whenchanged-attribute
• https://activedirectorypro.com/useraccountcontrol-check-and-manage-attribute-value/
• https://wdmsb.wordpress.com/2014/12/03/descriptions-of-active-directory-useraccountcontrol-value/
• https://jackstromberg.com/2013/01/useraccountcontrol-attributeflag-values/
• https://learn.microsoft.com/en-us/troubleshoot/windows-server/active-directory/useraccountcontrol-manipulate-account-properties