Giza Usage Statistics

[stdavis]

Benefit

Create an easy-to-use system for @rkelson to see which users have gone over the usage threshold in Giza on a monthly basis. This will provide him with the data necessary to send to finance so that they can bill the users.

This needs to be in place by July 31st so that it can be run on Aug 1st. July is the first month that overage charges will be applied.

Acceptance Criteria

• Create a shared query in BigQuery that returns the usage statistics (requests and data) for the previous calendar month per user
• Schedule this query to run on the first of each month and append it's results into a monthly usage table
• Set up a view or views on this table that help Rick see the overages

Notes

Alla Chernenko is a BigQuery resource and helped us get oriented on this project.

There is an existing "Monthly Usage" query saved in Scott's account in BigQuery that can be a starting point. The following query from the custom queries in Giza may also be helpful.

Note: This is legacy SQL that will need to be migrated if used.

select * from (
  SELECT 
    user,
    month(date) as month,
    year(date) as year,
    sum(size)/1048576 as egress,
    count(*) as count 
  FROM 
    [table] 
  group by 
    user,
    month,
    year
  having 
    (count > 5000 or egress > 50) and (month(CURRENT_TIMESTAMP()) != month or year(CURRENT_TIMESTAMP()) != year)
)
order by 
  year desc, 
  month desc,
  egress desc,
  count desc

Risks

No response

Issue Reference

No response