How to create a CSV file of all users with domains on DirectAdmin

In order to create a CSV file of all users with domains hosted on a DirectAdmin server in a format "User;Contact Email;domain(s);Package;Reseller;" follow the instructions below.

List of all users

A list of all users can be build from /usr/local/directadmin/data/users/*/users.list. The file exists under resellers in DirectAdmin, and lists all their users. So for the default admin account (it has admin/reseller/user levels) it will be 

• /usr/local/directadmin/data/users/admin/users.list

So if we unify content of files from all resellers we will get a list of all users from a single server.

Users metadata

All the information about DirectAdmin users can be found in /usr/local/directadmin/data/users/*/user.conf, and again the default admin account will have 

• /usr/local/directadmin/data/users/admin/user.conf

which contains all possible metadata.

Users domains

A list of all user's domains can be found in /usr/local/directadmin/data/users/*/domains.list. The user admin will have

• /usr/local/directadmin/data/users/admin/domains.list

listing all domains hosted under the account.

Creating CSV report

So now we need to go through a loop, collect the desired data and build a report. Here is an example of a shell command which we can use in a server console as root:

for USER in $(cat /usr/local/directadmin/data/users/*/users.list | sort | uniq); do \
echo "${USER};$(grep ^email= /usr/local/directadmin/data/users/${USER}/user.conf | cut -d= -f2);\
$(cat /usr/local/directadmin/data/users/${USER}/domains.list | xargs);\
$(grep ^package= /usr/local/directadmin/data/users/${USER}/user.conf | cut -d= -f2);\
$(grep ^creator= /usr/local/directadmin/data/users/${USER}/user.conf | cut -d= -f2);"; \
done;

Here is an example of the output:

user01;hidden@email.com;dev.example.com;developer;resellerbob;
user02;hidden@email.com;example2.com;hosting_2G;resellerbob;
user03;hidden@email.com;example.net example.nl;hosting_2G;resellerbob;
user04;hidden@email.com;example9.com example9.net;custom;resellerbob;
user05;hidden@email.com;html.example9.com html2.example9.com new.example9.com new2018.example9.com;hosting_3G;resellerbob;
user06;hidden@email.com;files.example9.com;hosting_SSL_2XL;resellerbob;

Domains will be space-separated.

Add SQL databases

Want to add SQL databases into the list? Try this:

mysql --defaults-file=/usr/local/directadmin/conf/my.cnf -e "SHOW DATABASES;" | grep -v ^Database > /root/list_dbs.out
for USER in $(cat /usr/local/directadmin/data/users/*/users.list | sort | uniq); do \
echo "${USER};$(grep ^email= /usr/local/directadmin/data/users/${USER}/user.conf | cut -d= -f2);\
$(cat /usr/local/directadmin/data/users/${USER}/domains.list | xargs);\
$(grep ^package= /usr/local/directadmin/data/users/${USER}/user.conf | cut -d= -f2);\
$(grep ^creator= /usr/local/directadmin/data/users/${USER}/user.conf | cut -d= -f2);\
$(grep ^${USER}_ /root/list_dbs.out | xargs);"; \
done;
rm -f /root/list_dbs.out;

Here is an example of the output:

user01;hidden@email.com;dev.example.com;developer;resellerbob;;
user02;hidden@email.com;example2.com;hosting_2G;resellerbob;user02_wp;
user03;hidden@email.com;example.net example.nl;hosting_2G;resellerbob;;
user04;hidden@email.com;example9.com example9.net;custom;resellerbob;user04_db;
user05;hidden@email.com;html.example9.com html2.example9.com new.example9.com new2018.example9.com;hosting_3G;resellerbob;;user05_db user05_db1;
user06;hidden@email.com;files.example9.com;hosting_SSL_2XL;resellerbob;;

That's it.