Creating business hours aggregation

You can prepare reports showing information relative to the business hours of operation based on a user-specified time offset, which allows for more accurate reporting of how applications and services are performing relative to different user communities. For example, you can aggregate data for a specified time range that has been defined as a "business day" or "shift". Each row is named for the shift or business hours it represents, e.g., austin, london, singapore as locations or shifts like austin_day, austin_swing, austin_night.

The creation of rows for the business hour aggregation is performed manually by inserting records into the reporting database. The tables of interest are described below:

  • business_hours_config: main table defining the business hours

    • name: name of the shift or business hours

    • description: up to 255 characters to describe the row (optional)

    • shift_by: difference in time between the raw data time stamp and the time specified by the shift_start and shift_end fields. This value can be negative and is in the format hh:mm.

    • shift_start_time: time of day when the shift (or business day) begins. The format is hh:mm.

    • shift_end_time: time of day when the shift (or business day) ends. This value can be less than shift_start which indicates that the end of the shift is the following day. The shift begins at the shift_start time and goes up to but does not include the shift_end time. The format is hh:mm.

     shift_start_time shift_end_time shift_by Selected range of raw data
    08:00 16:00 04:00 12:00 PM through 8:00 PM of the current day
    17:00 02:00 00:00 Assuming today is 1/17/2017: 1/17/2017 5:00 PM through 1/18/2017 2:00 AM
    06:00 14:00 -11:30 Assuming today is 1/17/2017: 1/16/2017 6:30 PM through 1/17/2017 2:30 AM
  • group_filter: optional filter used to reduce the set of devices included in the aggregation. If a location is used, such as London, you may only want to include devices in the London data center. This filter uses a zenoss_instance_key from dim_group, so groups need to be created and populated using the grouping procedure from the user interface. If this filter is not provided for a business hour row (see business_hours_group_assoc below), all devices are included in the aggregation.

    • filter_name: name to identify the filter

    • description: up to 255 characters to describe the filter (optional)

    • zenoss_instance_key: key of the zenoss instance, used along with device_organizer_uid to identify a device grouping in a particular zenoss instance

    • device_organizer_uid: id of the device group, used along with zenoss_instance_key to identify a device grouping in a particular zenoss instance

  • meta_metric: optional filter used to reduce the set of metrics included in the aggregation. If this filter is not provided for a business hour row (see business_hours_group_assoc below), all metrics are included in the aggregation.

    • metric_key: primary key

    • metric_name: name of the metric to aggregate

  • business_hours_group_assoc: each business hour row may have multiple associated job filters. Each job filter may be used by many business hour rows. For each job filter associated with a business hour row, an association record is created.

    • business_hours_config_id: id of the row in the business hours table

    • group_filter_id: id of the job filter

  • business_hours_metric_assoc: each business hour row may have multiple associated metric filters. Each metric filter may be used by many business hour rows. For each metric filter associated with a business hour row, an association record is created.

    • business_hours_config_id: id of the row in the business hours table

    • metric_key: id of the metric filter