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 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)