User Tools
This is an old revision of the document!
Table of Contents
Key Stats
Updated by Yanjun Wang 14/12/2021
Overview
The key_status is written to provide customers with key information from their SnmpManager&Ticketer database. Basically, it's using SQL queries to generate data at key_stats database.
The scripts are written with BASH&Groovy and need to be installed on each server/environment and run by a cronjob to refresh/update the data every day.
Due to each customer having a different DAS alarm workflow, form name etc. We have templated all the scripts, and the script used to install can be generated based on configuration.
Generator Repo: https://bitbucket.org/errigal/key-stats/src/dev/
Scripts Generation and Installation
The scripts for each customer(Currently, support for ATC and EXT) can be generated by running the etl-tester locally, details as below:
- Imported the project in IntelliJ via Gradle and SpringBoot.
- Change clientCode in application.properties to either EXT or ATC.
- Run SpringBoot application, the customer based on key_stats script will be generated at {baseDir} which can be migrated to a server. (For both EXT and ATC, need to run twice)
More details for the generator at:here
Installation Playbook:
ansible-playbook -i ../env-configuration/os1/hosts.ini upload-key_stats-script.yml --ask-vault-pass
Table Summary
Source: Reporting Manager KPI and BI Tool Integration IDMS 3.3 2017/2018
Database Name: key_stats
ticket_summary
Every ticket created in the Ticketer will correspond to exactly one row in this table. This table contains counts of all the major forms linked to a ticket. It also contains basic details of alarms triggered by SNMP trap.
daily_active_alarm_summary
This table shows on any given date what was the outage in seconds per network element. It does not consider alarms cleared within 5 minutes to avoid noise from report.
device_outage_summary
This table further enriches data store in daily_active_alarm_summary. It brings service impacting alarms and aggregates outage time of all alarms generated for a NE on a given date. This table can help you find out outages between a desired date range. The important field to use ‘effective_date’
field_tech_site_visit
This table contains the Site Visit form added on tickets for Node elements. This is used for the Worst H/W by Site Visit Tableau Report. It contains data for the last 1 year only.
network_element_summary
This table gives a simplified view of network element, controller and node/host information.
ticket_basic_summary
This table contains basic information about tickets such as associated workflow and forms and when they were created. This table is used by State Transition diagram to provide form name-based filter.
wf_extenet_das_alarm/wf_atc_das_alarm
This table contains information about the DAS Alarms and associated Ticket information.
wf_ticket_status_change
This table contains ticket transition from one stage to another and how much time it spends at any stage. There will be one row for each transition for a ticket.
v_hw_elements
This view contains total count of active on-air NE per technology. This table provides additional to reports to calculate average stats.
v_network_element_latest_change_on v_network_element_status
This view contains information about on-Air status of NE which is used by daily_active_alarm_summary to filter out off-air NE for report. If there is any pending request for on-air element that will also be considered in outage report.
v_service_impacting
This view is used by outage summary report to filter out all non-service impacting alarm outages. It is a supplementary view.
v_device_outage_summary
This view is derived from device_outage_summary table and used by Tableau Reporting.
v_hw_alarms
This view contains the number of alarms received per technology. It is used by in the Worst H/W by technology Tableau Report.
Appendix
Project folder:
