Author: Anna Dowling

The following outline will give the reader a summary of the main domain objects associated with the Ticketer application.

For further fields relating to any of these tables, run a desc command on that table on a ticketer database.

The Configuration table stores references for the following data relating to the functioning of the Ticketer Application: url settings to access the other applications, filesystem locations, snmp_manager configuration, database details.

This table stores district names for use with grouping within the ticketer.

District examples include CHI. NCA, PHL.

Districts are linked to the visibility table based on the link visibility.district_id = district.id

Note: Extenet does not use districts.

SELECT * from ticketer.district

SELECT * from ticketer.visibility WHERE district_id = 1

This is one of the most important tables in the Ticketer.

The ticket table stores references to ticket details including:

• id - The ticket's unique identifier value.
• create_date - The date when the ticket was created.
• closed_date - The date when the ticket was closed.
• current_status_id - This field is linked to the ticket_status table which denotes the current status of the ticket.
• worklfow_id - This field is linked to the gworkflow table which denotes the workflow the ticket is located in.
• parent_id - If the ticket is a child ticket, the id of the parent ticket is added to this field.
• maintenance_id - If the ticket is involved in the Preventative Maintenance process this value is populated with the relevant maintenance id.

For further fields relating to the ticket table, run a desc ticket command on a ticketer database.

The snmp_manager_config table stores the connection details for accessing a specific snmp_manager instance.

This stores remote_url, username and password details.

This table stores an entry for each Ticket Status that has been entered on a ticket.

An example of a Ticket Status would be “Alarm Received”.

This table stores fields for values such as:

• ticket_id
• status_date
• next_ticket_status_id that was entered following that status
• status_duration_in_seconds
• status

Query to find all of the ticket statuses for a specific ticket:

SELECT * from ticket_status where ticket_id = 11;

This table holds references to the MySQL connections used within the Ticketer applications.

This table stores information such as:

• hostname
• username
• password
• database driver associated with the database connection to establish.

The change form table stores form entries.

There can be only one “Active” change form that can be modified (referred to as the mockup), only one “Active” change form that is in production (published).

“Inactive” Change Forms are archived for use when displaying old Tickets.

The form component table stores an entry for each form field on a form that is created through the form Architect in the Ticketer.

This could be a checkbox, text field, dropdown list etc.

The email_account table stores entries for email account configurations.

Emails are one of the central features of the ticketer and are sent out to parties such as the Carrier, Ticket owner etc. to notify them of updates on a Ticket.

This table stores details for the active email account used for relaying emails to these parties in conjunction with the application.

The email_account_props tables stores the properties used in order to connect to and use the specified email_account with javax.mail library.

This includes the port number, auth value, class and fallback.

These properties are used to send the emails.

The external element table stores a link between the network element and the ticket in the preventative maintenance process.

It stores references to:

• network element id
• network element name
• colocation details
• acceptance_record_id (ticket id)
• technician
• market
• table_id (external_table id for the query used to populate this external_element entry)
• region

This table stores the queries used during the preventative maintenance process.

For more information on Preventative Maintenance refer to the section: Ticketer - Preventative Maintenance Introduction

The ticket change table references a new entry that is created for each form update that occurs in the ticketer.

It stores ticket_id, form_id and user id details relating to these changes.

Query to find all of the ticket changes for a specific ticket:

SELECT * from ticket_change where ticket_id = 11;

The user domain stores user account details for the Ticketer application.

The user_role domain stores user role information associated with access limitations in the Ticketer applications.

For example the role Administrator has access to the admin pages in the Noc Portal etc.

The user_roles domain is a join table which stores the relationship between the user table and the user_role table, creating the link between users and their assigned roles.

The user_role_permissions table stores the grails permissions String to allow access to certain pages and functionality within certain pages.

This has an association with the user_role.

This table stores region names for use with grouping within the ticketer. Region examples include South East, North East, Central.

Regions are linked to the visibility table based on the link visibility.region = region.id

SELECT * from ticketer.region

SELECT * from ticketer.visibility WHERE region_id = 1

The visibility table stores entries for the visibility levels (also known as clusters or markets) available within the Ticketer application.

For example the Global visibility can see everything and would typically be given to an admin user.

Verizon - All Hubs would limit the user to Verizon markets at a hub level.

This table also links to both the region and district tables.

This table stores custom configurable groovy script entries which are editable for use at run time.

The ticketer application uses groovlet for may aspects of its functionality.

One example one be for workflow customisation of behaviour, such as population of a form following a specific status change etc.

Some commonly used groovlets include Service entitlements (Ext), Das Incident Management (CC).

GWorkflow stores an entry for each published and mockup workflow in the Ticketer Application.

This table contains the ticket_prefix value that can be associated with tickets.

GNode stores an entry for each status created on a workflow entry within the Ticketer Application.

This table contains the preserve_owners field which denotes if the state has preserve owners set to true or false.

The following assessment is to test your knowledge of the basic object table outlined above for the Ticketer.

This assessment assumes that you have a basic working knowledge of MySQL and have completed the MySQL assessment prior to this.

1. Connect to a qa database and locate the ticketer application database. (If you need assistance in establishing this connection please contact a member of the team.)
2. In the MySQL prompt, Describe the contents of the following tables: configuration, snmp_manager_config, external_datasource.
3. Join an entry of the user table to the user_role table.
4. Join an entry of the region table to the visibility table.
5. Join an entry of the district table to the visibility table.
6. Locate the active configuration entry for the Ticketer.
7. Locate a published gworkflow entry and its associated gnode entries.
8. Join a ticket entry to its relevant current ticket_status.
9. Find the correct email_account_props associated with an email_account.