wiki:Internal/cProcedures/aGeneratingReports

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)
Last modified 4 years ago Last modified on 04/07/16 13:02:58

Attachments (1)

Download all attachments as: .zip