The purpose of this section of the Administrative Guide is to help you become familiar with the table structure of the trending database tables so that you can create custom trending reports with the data in the database. Each table was designed to be flexible enough to accommodate multiple types of trends.
There is a database devoted to each object and trending job. Therefore, there are 2 total trending-related database tables. Below is a list of these tables with a categorization of the types of fields in each table:
agl_trending_ticket
The agl_trending_ticket table, as the name implies, contains trending data for tickets. The tasks for the trending job, covered earlier in this chapter, execute the queries that populate the data in this table. The columns in this table and the type of data each column contains are as follows:
trending_type - This column labels the type of ticket trending data in the database row. You will need to refer to the type in your WHERE clause to ensure your query is obtaining the type of data that you desire. The possible type values equate to the task names, which were covered earlier in this section. Examples of trending type values include ticket_type_stage, ticket_priority_stage, and ticket_entityowner_stage.
current_interval - The starting point of each interval in which rollups to the trending table occur. If an object's job is set to run on a daily basis, then there will be daily, weekly, and monthly rollups. All daily jobs will have the same current_interval, all weekly jobs share the same current_interval, and all monthly jobs have the same current_interval. This field is designed for internal use.
- collection_time - The time the record is created
- quantity - Total number of tickets in a group (e.g. total tickets of a type)
- is_closed - Total number of tickets that were closed during an interval.
- is_opened - Total number of tickets that were created during an interval.
- total_closed - At this collection time, the number of tickets in a closed state.
- total_opened - At this collection time, the number of tickets in an open state.
- avg_stage_age - Average days spent in the current stage.
- avg_age - Average days from creation to close for closed tickets. For open tickets, this is calculated as the difference between the creation date and the date the metric was written to the database.
- std_stage_age - Standard deviation of stage age.
- std_age - Standard deviation of age
- groupby1_string - String that describes the group of trend metrics. For example, if the trending query is for tickets by owner, by priority, by risk, and by type, the groupby1_string would be the owner's name.
- groupby2_string - String that describes the group of trend metrics. For example, if the trending query is for tickets by owner, by priority, by risk, and by type, the groupby2_string would be the priority of the group of tickets.
- groupby3_string - String that describes the group of trend metrics. For example, if the trending query is for tickets by owner, by priority, by risk, and by type, the groupby3_string would be the risk level of the group of tickets.
- groupby4_string - String that describes the group of trend metrics. For example, if the trending query is for tickets by owner, by priority, by risk, and by type, the groupby3_string would be the type of the group of tickets.
- groupby5_string - String that describes the group of trend metrics. Most trending queries will not require a 5th groupby string, but this column is here in case it is needed.
- groupby1_number - Numerical value that is common to a group of tickets. Most queries will probably not use this field, but it could be useful if a custom attribute that is represented by a number, such as Category, is added to the ticket object.
- Groupby2_number - Numerical value that is common to a group of tickets. Most queries will probably not use this field, but it could be useful if a custom attribute that is represented by a number, such as Category, is added to the ticket object.
- Groupby3_number - Numerical value that is common to a group of tickets. Most queries will probably not use this field, but it could be useful if a custom attribute that is represented by a number, such as Category, is added to the ticket object.
