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 <ldap secret> "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 registrated 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: //'