Get full DN for AD user account?

[stefanw]

Hi everyone --

I searched every-which-way for a previous topic which answered this but came up empty...

We have four Nagios XI servers (two active, two standby) and I need to sync up all the user accounts across all of them. The accounts involved are all "imported" from Active Directory, with thankfully only one base DN for all accounts. It's roughly 150 accounts total and everything is working great other than the lack of uniformity. In other words some users were created on one set but not another, and some users weren't even created on the standby server for each active one.

I can get the username for everyone from the /nagiosxi/api/v1/system/user?apikey=XYZ&pretty=1 call but that doesn't include the full DNs. There are at least three different OUs involved, sort of like this:

Code: Select all

CN=fred,OU=Employees,OU=TeamX,OU=DepartmentA,DC=mycompany
CN=barney,OU=Vendors,OU=TeamY,OU=DepartmentB,DC=mycompany
CN=wilma,OU=Employees,OU=TeamZ,OU=DepartmentC,DC=mycompany

Is there an undocumented API option which will return the full DN for an AD user, or...

Is there a database query you can tell me which will return the full DN for all the users / each user?
(Even some SQL for a specific user would be better than nothing. I have no problem scripting something to iterate over a list if there's a query to get just one.)

Needless to say my goal is to take the full DN and POST the creation of missing users on each XI server. If I have to do it manually it's going to be a royal pain.

Thanks!!

[stefanw]

I forgot to mention... three of the four are XI version 5.5.11, and one is 5.5.7.

[cdienger]

Try this query against the nagiosxi database:

select * from xi_usermeta where keyname='ldap_ad_dn' || keyname='ldap_ad_username';

[stefanw]

Also since one of the possible solutions might be SQL, I should note the database basics...

All four of our XI instances are using MySQL.
Three of the four are using the latest available on RHEL 6.10, MySQL 5.1.73.
One has an offloaded MySQL on AWS RDS, engine 5.7.23.

(I doubt the engine version makes a difference, but I thought I'd share it anyway, just in case.)

[stefanw]

Try this query against the nagiosxi database:

select * from xi_usermeta where keyname='ldap_ad_dn' || keyname='ldap_ad_username';

Spectacular! That does the trick perfectly! Thanks for the quick reply!

OK to lock up this topic.

For posterity and to benefit others who may find it helpful, some obfuscated output:

Code: Select all

% echo "use nagiosxi ; select * from xi_usermeta where keyname='ldap_ad_dn' || keyname='ldap_ad_username';" | mysql
usermeta_id	user_id	keyname	keyvalue	autoload
112	4	ldap_ad_username	fred	1
113	4	ldap_ad_dn	CN=fred,OU=Employees,OU=TeamX,OU=DepartmentA,DC=mycompany	1
141	5	ldap_ad_username	barney	1
142	5	ldap_ad_dn	CN=barney,OU=Vendors,OU=TeamY,OU=DepartmentB,DC=mycompany	1
170	6	ldap_ad_username	wilma	1
171	6	ldap_ad_dn	CN=wilma,OU=Employees,OU=TeamZ,OU=DepartmentC,DC=mycompany	1

[cdienger]

Glad to hear it worked for you!