====== 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 for each customer 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  [[https://bitbucket.org/errigal/key-stats/src/dev/|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)
  * Commit the change to bitbucket

More details for the generator at:[[https://bitbucket.org/errigal/key-stats/src/dev/|here]]

Installation Playbook:
<code>
ansible-playbook -i ../env-configuration/os1/hosts.ini upload-key_stats-script.yml --ask-vault-pass
</code>

----

==== Table Summary ====

//Source: [[https://docs.google.com/document/d/1kgD8ofamAPfOmwkC95T-RAzyoixWaylF0uClW33PD2I/edit|Reporting Manager KPI and BI Tool Integration IDMS 3.3 2017/2018]]//

Database Name: **key_stats**

<WRAP group>
<WRAP half column>
ticket_summary
</WRAP>

<WRAP half column>
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.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
daily_active_alarm_summary
</WRAP>

<WRAP half column>
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.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
device_outage_summary
</WRAP>

<WRAP half column>
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’
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
field_tech_site_visit
</WRAP>

<WRAP half column>
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.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
network_element_summary
</WRAP>

<WRAP half column>
This table gives a simplified view of network element, controller and node/host information.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
ticket_basic_summary
</WRAP>

<WRAP half column>
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.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
wf_extenet_das_alarm/wf_atc_das_alarm
</WRAP>

<WRAP half column>
This table contains information about the DAS Alarms and associated Ticket information.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
wf_ticket_status_change
</WRAP>

<WRAP half column>
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.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
v_hw_elements
</WRAP>

<WRAP half column>
This view contains total count of active on-air NE per technology. This table provides additional to reports to calculate average stats.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
v_network_element_latest_change_on   
v_network_element_status
</WRAP>

<WRAP half column>
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.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
v_service_impacting
</WRAP>

<WRAP half column>
This view is used by outage summary report to filter out all non-service impacting alarm outages. It is a supplementary view.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
v_device_outage_summary
</WRAP>

<WRAP half column>
This view is derived from device_outage_summary table and used by Tableau Reporting.
</WRAP>
</WRAP>

<WRAP group>
<WRAP half column>
v_hw_alarms
</WRAP>

<WRAP half column>
This view contains the number of alarms received per technology. It is used by in the Worst H/W by technology Tableau Report.
</WRAP>
</WRAP>

----

==== Appendix ====

Project folder:
  * [[https://drive.google.com/drive/folders/0BzOPOur52nscUXdwTll0Sm9XVTA|Reprting Manager]]