= Generating Usage Reports = == Scheduler reports == * To get total reservations on all machines in minutes for certain period: {{{ SELECT sum((reservations.end_date + (reservations.endTime * 60))-(reservations.start_date + (reservations.startTime * 60)))/60 duration FROM reservations WHERE unix_timestamp('2000-01-01') <=(reservations.start_date + (reservations.startTime * 60)) AND unix_timestamp('2016-03-31') >= (reservations.end_date + (reservations.endTime * 60)) AND is_pending = 0; }}} * To get total user reservations in minutes for certain period per machine: {{{ SELECT machines.name resource_name, sum((reservations.end_date + (reservations.endTime * 60))-(reservations.start_date + (reservations.startTime * 60)))/60 duration FROM reservations LEFT JOIN reservation_users ON reservation_users.resid = reservations.resid LEFT JOIN machines ON reservations.machid = machines.machid WHERE unix_timestamp('2000-01-01') <=(reservations.start_date + (reservations.startTime * 60)) AND unix_timestamp('2016-03-31') >= (reservations.end_date + (reservations.endTime * 60)) AND is_pending = 0 GROUP BY resource_name ORDER BY duration desc }}} * To get total resource reservations in minutes for certain period per machine. This is slightly different than above because user can invite other user(s) to his reservation. {{{ SELECT machines.name resource_name, sum((reservations.end_date + (reservations.endTime * 60))-(reservations.start_date + (reservations.startTime * 60)))/60 duration FROM reservations LEFT JOIN machines ON reservations.machid = machines.machid WHERE unix_timestamp('2016-03-01') <=(reservations.start_date + (reservations.startTime * 60)) AND unix_timestamp('2016-03-31') >= (reservations.end_date + (reservations.endTime * 60)) AND is_pending = 0 GROUP BY resource_name ORDER BY resource_name; }}} == LDAP reports == * To get total number of registered user run following command: {{{ ldapsearch -h ldap.orbit-lab.org -p 389 -x -D "cn=admin,dc=orbit-lab, dc=org" -w "objectClass=person" uid -z 0 }}} '''numEntries''' represents number of users * To get total number of registered organizations run following command: {{{ ldapsearch -h ldap.orbit-lab.org -p 389 -x -b "dc=orbit-lab, dc=org" "objectclass=organizationalUnit" }}} '''numEntries''' represents number of organizations * Get list of registered organizations: {{{ ldapsearch -LLL -h ldap.orbit-lab.org -p 389 -x -b "dc=orbit-lab, dc=org" "objectclass=organizationalUnit" ou |grep ^ou:|sed -e 's/ou: //' }}} * Get number of users per organization: sh ldapUsers.sh groups.txt 1. ldapUsers.sh script attached to thus page 2. groups.txt file containing list of organizations (it can be produced by previous ldapsearch)