BI Database Queries

Some interesting business intelligence queries you can run for various OBS features

start_date = DateTime.parse('2022-11-01').beginning_of_day
end_date = DateTime.parse('2023-01-31').end_of_day

Users

• Active: User.where(last_logged_in_at: start_date..end_date).count
• Sign Ups: User.where(created_at: start_date..end_date).count

Code

• Commits: ProjectLogEntry.where(event_type: :commit).where(datetime: start_date..end_date).count
• Binaries Released: BinaryRelease.where(binary_buildtime: start_date..end_date).count
• Service Runs: ProjectLogEntry.where(event_type: [:service_fail, :service_success]).where(datetime: start_date..end_date).count
• SCM Workflows executed: WorkflowRun.where(created_at: start_date..end_date).count

Collaboration

• Requests reached final state: HistoryElement::Base.where(created_at: start_date..end_date).where(type: ['HistoryElement::RequestAccepted', 'HistoryElement::RequestDeclined']).count
• Reviews finished: HistoryElement::Base.where(created_at: start_date..end_date).where(type: ['HistoryElement::ReviewAccepted', 'HistoryElement::ReviewDeclined']).count
• Comments given: Comment.where(created_at: start_date..end_date).count
• Stagings: ProjectLogEntry.where(event_type: [:staged_request, :unstaged_request, :staging_project_created]).where(datetime: start_date..end_date).group(:event_type).count