Create 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

The 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

An 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

An 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

Inserting records for business hours aggregation

Perform the following steps to insert records into the reporting database. The code in this procedure is for demonstration purposes only. This example assumes that you have two data centers (Austin and Bangalore) already defined and the metrics you want to filter on are called analytics_test1 and analytics_test2:

Log in to the Analytics reporting database as the root user or a user with administrative permissions.
```
mysql -u root reporting
```

Enter the following commands at the prompt substituting values that apply to your company's situation. The first section of the following inserts groups. If you already have groups, ignore this section and customize the commands to your situation:

insert into dim_group
   (group_key, sid, zenoss_instance_key, id, device_organizer_name, device_key,
       device_organizer_uid)
values
   (1001, '8525e4e2-4da4-4da5-8d4c-20ec70eaae0b/analytics_test_1', 1, 'AustinDataCenter',
      'AustinDataCenter', 1, '/zport/dmd/Groups/AustinDataCenter'),
   (1002, '8525e4e2-4da4-4da5-8d4c-20ec70eaae0b/analytics_test_3', 1, 'AustinDataCenter',
      'AustinDataCenter', 3, '/zport/dmd/Groups/AustinDataCenter'),
   (1003, '8525e4e2-4da4-4da5-8d4c-20ec70eaae0b/analytics_test_2', 1, 'BangaloreDataCenter',
      'BangaloreDataCenter', 2, '/zport/dmd/Groups/BangaloreDataCenter'),
   (1004, '8525e4e2-4da4-4da5-8d4c-20ec70eaae0b/analytics_test_4', 1, 'BangaloreDataCenter',
      'BangaloreDataCenter', 4, '/zport/dmd/Groups/BangaloreDataCenter');

insert into group_filter
   (name, description, zenoss_instance_key, device_organizer_uid)
values
   ('austin', 'Devices in the Austin data center', 1, '/zport/dmd/Groups/AustinDataCenter'),
   ('bangalore', 'Devices in the Bangalore data center', 1, '/zport/dmd/Groups/BangaloreDataCenter');

insert into meta_metric (metric_name)
values ('analytics_test1'), ('analytics_test2');


insert into business_hours_config
   (name, description, shift_by, shift_start_time, shift_end_time)
values
   ('austin', 'Austin data center', '-06:00', '8:00', '16:00'),
   ('bangalore', 'Bangalore data center', '-09:30', '7:00', '17:00');

insert into business_hours_group_assoc
   (business_hours_config_id, group_filter_id)
values
   (
      (select id from business_hours_config where name='austin'),
      (select id from group_filter where name='austin')
   ),
   (
      (select id from business_hours_config where name='bangalore'),
      (select id from group_filter where name='bangalore')
   );

insert into business_hours_metric_assoc
   (business_hours_config_id, metric_key)
values
   (
      (select id from business_hours_config where name='austin'),
      (select metric_key from meta_metric where metric_name='analytics_test1')
   ),
   (
      (select id from business_hours_config where name='austin'),
      (select metric_key from meta_metric where metric_name='analytics_test2')
   ),
   (
      (select id from business_hours_config where name='bangalore'),
      (select metric_key from meta_metric where metric_name='analytics_test1')
   ),
   (
      (select id from business_hours_config where name='bangalore'),
      (select metric_key from meta_metric where metric_name='analytics_test2')
   );