steampipe plugin install ldap

Table: ldap_user - Query LDAP Users using SQL

The Lightweight Directory Access Protocol (LDAP) is a protocol designed to manage and access distributed directory information services over an Internet Protocol (IP) network. LDAP is used to look up encryption certificates, pointers to printers and other services on a network, and provide 'single sign-on' where one password for a user is shared between many services. An LDAP user is an entry or record within the LDAP directory that represents a single user with attributes such as common name, distinguished name, and email address.

Table Usage Guide

The ldap_user table provides insights into user entries within the LDAP directory. As an IT administrator, explore user-specific details through this table, including distinguished names, common names, and email addresses. Utilize it to uncover information about users, such as their specific attributes, the hierarchical structure of the directory, and the relationships between different entries.

Important Notes

  • This table supports optional quals. Queries with optional quals in a where clause are optimised to use LDAP search filters.
  • If filter is provided, other optional quals will not be used when searching.
  • Optional quals are supported for the following columns:
    • cn
    • department
    • description
    • disabled
    • display_name
    • filter - Allows use of an explicit filter. Please refer to LDAP filter language.
    • given_name
    • mail
    • object_sid
    • sam_account_name
    • surname
    • user_principal_name
    • when_created
    • when_changed

Examples

Basic info

Determine the areas in which certain user details were created, including email and department specifics, to gain insights into your user base. This can help in assessing the elements within your organization for better user management.

select
dn,
cn,
when_created,
mail,
department,
sam_account_name,
from
ldap_user
limit
100;
select
dn,
cn,
when_created,
mail,
department,
sam_account_name
from
ldap_user
limit
100;

List disabled users

Identify instances where user accounts have been disabled to ensure proper access control and maintain the integrity of your system.

select
dn,
sam_account_name,
mail,
object_sid
from
ldap_user
where
disabled;
select
dn,
sam_account_name,
mail,
object_sid
from
ldap_user
where
disabled = 1;

List users in the 'Engineering' department

Identify individuals who are part of the Engineering team. This is useful for understanding team composition and for reaching out to specific team members.

select
dn,
sam_account_name,
mail,
department
from
ldap_user
where
department = 'Engineering';
select
dn,
sam_account_name,
mail,
department
from
ldap_user
where
department = 'Engineering';

List users that have been created in the last 30 days

Discover the segments that include users who were added recently. This can help in monitoring user growth and understanding the rate at which new users are being added to your system.

select
dn,
sam_account_name,
mail,
when_created
from
ldap_user
where
when_created > current_timestamp - interval '30 days';
select
dn,
sam_account_name,
mail,
when_created
from
ldap_user
where
when_created > datetime('now', '-30 days');

Get details for groups the user 'Bob Smith' is a member of

Determine the groups that a specific user, such as 'Bob Smith', is associated with. This can be useful in managing user permissions and understanding user roles within a system.

select
u.dn as userDn,
u.mail as email,
u.object_sid as user_object_sid,
g.dn as group_dn,
g.cn as group_name,
g.object_sid as group_object_sid
from
ldap.ldap_user as u
cross join jsonb_array_elements_text(u.member_of) as groups
inner join ldap.ldap_group as g on g.dn = groups
where
u.cn = 'Bob Smith';
select
u.dn as userDn,
u.mail as email,
u.object_sid as user_object_sid,
g.dn as group_dn,
g.cn as group_name,
g.object_sid as group_object_sid
from
ldap_user as u,
json_each(u.member_of) as groups
inner join ldap_group as g on g.dn = groups.value
where
u.cn = 'Bob Smith';

Filter Examples

List users whose names start with "Adam"

Explore which users have names starting with 'Adam' to quickly locate their account details and creation dates, useful for user management and auditing purposes.

select
dn,
sam_account_name,
mail,
when_created
from
ldap_user
where
filter = '(cn=Adam*)';
select
dn,
sam_account_name,
mail,
when_created
from
ldap_user
where
filter = '(cn=Adam*)';

List members of a group filtering by the group's DN

Identify the members of a specific group in your network, including their display names and principal user names. This can help you understand who has access to certain resources and when they were added to the group.

select
cn,
display_name,
when_created,
user_principal_name,
ou,
given_name
from
ldap_user
where
filter = '(memberof=CN=Devs,OU=Steampipe,OU=SP,DC=sp,DC=turbot,DC=com)';
select
cn,
display_name,
when_created,
user_principal_name,
ou,
given_name
from
ldap_user
where
filter = '(memberof=CN=Devs,OU=Steampipe,OU=SP,DC=sp,DC=turbot,DC=com)';

Schema for ldap_user

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
attributesjsonbAll attributes that have been returned from LDAP.
base_dntextThe Base DN on which the search was performed.
cntext=Full name of the user.
departmenttext=Department to which the user belongs to.
descriptiontext=Description of the user.
disabledboolean!=, =Whether the user account is disabled.
display_nametext=Display name of the user.
dntext=Distinguished name of the user.
filtertext=Optional search filter.
given_nametext=Given name of the user.
initialstextInitials of the user.
job_titletextJob title of the user.
mailtext=E-mail address of the user.
member_ofjsonbGroups that the user is a member of.
object_classjsonbObject classes of the user.
object_sidtext=The security identifier (SID) of the user.
outextOrganizational unit to which the user belongs to.
sam_account_nametext=Logon name (pre-Windows 2000) of the user.
surnametext=Family name of the user.
titletextTitle of the user.
user_principal_nametext=Login name of the user, usually mapped to the user email name.
when_changedtimestamp with time zone>, >=, =, <, <=Date when the user was last changed.
when_createdtimestamp with time zone>, >=, =, <, <=Date when the user was created.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- ldap

You can pass the configuration to the command with the --config argument:

steampipe_export_ldap --config '<your_config>' ldap_user