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(last_logged_in_at: start_date..end_date).count

Commits

• ProjectLogEntry.where(event_type: :commit).where(datetime: start_date..end_date).first

Collaboration

• Accepted: HistoryElement::RequestAccepted.where(created_at: start_date..end_date).count
• Declined: HistoryElement::RequestDeclined.where(created_at: start_date..end_date).count
• Reviews finished: HistoryElement::Base.where(created_at: start_date..end_date).where(type: ['HistoryElement::ReviewAccepted', 'HistoryElement::ReviewDeclined']).count
• Reviews Accepted: HistoryElement::ReviewAccepted.where(created_at: start_date..end_date).count
• Reviews Declined: HistoryElement::ReviewDeclined.where(created_at: start_date..end_date).count
• Comments: Comment.where(created_at: start_date..end_date).count

Maintenance

• Tracked Packages: BinaryRelease.where(binary_buildtime: start_date..end_date).count

SCM / CI

• Workflows executed: WorkflowRun.where(created_at: start_date..end_date).count

Services

• Runs: ProjectLogEntry.where(event_type: [:service_fail, :service_success]).where(datetime: start_date..end_date).count
• Succeeded: ProjectLogEntry.where(event_type: :service_success).where(datetime: start_date..end_date).count
• Failed: ProjectLogEntry.where(event_type: :service_fail).where(datetime: 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