Internal Errigal Collaboration Wiki

User Tools

Site Tools

Trace:
Writing /app/www/public/data/meta/onboarding/reportingmanager/conventions_-_report_variables.meta failed
onboarding:reportingmanager:conventions_-_report_variables

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
onboarding:reportingmanager:conventions_-_report_variables [2017/05/03 16:33] – mmcconboarding:reportingmanager:conventions_-_report_variables [2021/06/25 10:09] (current) – external edit 127.0.0.1
Line 1: Line 1:
 +====== Conventions - Report Variables ======
  
 +Author: Michelle McCausland
 +
 +===== Guidelines for report variables =====
 +
 +  - Where possible avoid hardcoding in values for things like network element names, region names, cluster names etc. as these fields can be subject to change at any time. In these cases it is preferable to query the database and return those results.
 +  - Report variables should be written in camel case i.e. myVariableIsAwesome.
 +  - Report variable names should be clear and obvious to what they are referring to, NO: myVariable1 , YES: districtValue
 +  - Find some example reports by querying the script_report domain
 +
 +**Note: Please verify all scripts, if used, work correctly - do not blindly copy and paste**
 +
 +----
 +
 +
 +===== Variable Field Conventions =====
 +
 +==== 1. Network Identifier ====
 +
 +The network identifier is an ExteNet used field used for grouping network elements based on location.
 +
 +**Query to retrieve network identifier:**
 +<code>SELECT DISTINCT(network_identifier) from snmp_manager.network_element WHERE network_identifier != "" ORDER BY 1;</code>
 +
 +**Network Identifier variable script:**
 +<code>
 +import groovy.sql.Sql
 +import com.osssoftware.reporting.database.*
 +def query = """
 +   SELECT DISTINCT(network_identifier) from snmp_manager.network_element WHERE network_identifier != "" ORDER BY 1;
 +"""
 +def list = []
 +db = SqlConnectionManager.instance.retrieveSql("snmp_manager")
 +db.eachRow(query) {
 +  list << it.network_identifier
 +}
 +return list as String[]</code>
 +
 +**Script for use when Network Identifier is dependant on region & carrier:**
 +
 +<code>
 +import groovy.sql.Sql
 +import com.osssoftware.reporting.database.*
 +
 +String region = dependents.region
 +String carrier = (dependents.carrier)?.replaceAll("\\(Tickets\\)", "")
 +
 +String query = """
 +SELECT DISTINCT element.network_identifier FROM network_element element 
 +WHERE element.network_identifier IS NOT NULL
 +${
 + (region.equals('All')) ? '' : "AND element.network_identifier like '${region}%'"
 +}
 +${
 + (carrier.equals('All')) ? '' : "AND element.network = '${carrier}'"
 +}
 + AND element.network_identifier not in ('', 'n/a', '0')
 +ORDER BY element.network_identifier
 +"""
 +
 +def list = []
 +
 +db = SqlConnectionManager.instance.retrieveSql("snmp_manager")
 +list << "All"
 +db.eachRow(query) {
 +  list << it.network_identifier
 +}
 +return list as String[]
 +</code>
 +
 +----
 +
 +==== 2. Carrier ====
 +
 +Carrier refers to for example Verizon Wireless, Sprint, T-Mobile etc, the carriers that are associated with network elements.
 +
 +It is important to ensure that non-admin users have been assigned the correct reporting manager carrier roles e.g. T-MOBILE(Tickets) and that a report with a carrier variable will run for all carriers assigned to the non-admin user's account.
 +
 +**Query to retrieve carriers:**
 +''SELECT name FROM snmp_manager.network_element 
 +where parent_id = 1 
 +AND name not IN('ERRIGAL','ORPHANS') 
 +ORDER BY 1;''
 +     
 +Note: We don't usually want to include Errigal or orphans unless specifically stated in report requirements. 
 +
 +**Carrier variable script:**
 +
 +<code>
 +import groovy.sql.Sql
 +import com.osssoftware.reporting.database.*
 +def query = """
 +SELECT name FROM snmp_manager.network_element where parent_id = 1 AND name not IN('ERRIGAL','ORPHANS') ORDER BY 1;
 +"""
 +def list = []
 +db = SqlConnectionManager.instance.retrieveSql("snmp_manager")
 +def result = db.rows(query)
 +result.each{resultRow ->
 +      list << resultRow.name + "(Tickets)"     
 +}
 +return list as String[]</code>
 +
 +**Useful Carrier Info:**
 +     Clearwire is now Sprint so is deprecated
 +
 +
 +----
 +
 +==== 3. Network Element ====
 +
 +The classification of a network element in the case of most reports is that it is show_in_monitor = true unless otherwise stated. Where network elements have been soft deleted i.e. marked show in monitor = false we do not want these elements to be accounted for with reports.
 +
 +**Query for hubs and nodes:**
 +
 +<code>
 +SELECT hub.id, hub.name 
 +
 +FROM snmp_manager.network_element hub 
 +
 +LEFT JOIN snmp_manager.network_element node ON node.parent_id = hub.id AND node.ne_type != "Controller"
 +
 +WHERE hub.ne_type = "Controller";
 +</code>
 +
 +Please consult [[onboarding:reportingmanager:conventions_-_general_report_writing|Conventions - General Report Writing]] for more detail on using nodes.
 +
 +**Script for use when Hub(network element) is dependant on network identifier:**
 +
 +<code>
 +import groovy.sql.Sql
 +import com.osssoftware.reporting.database.*
 +
 +def networkIdentifier= dependents.networkIdentifier
 +if(!networkIdentifier) throw new Exception("invalid networkIdentifier '${networkIdentifier}' found")
 +//define datasource
 +def db = SqlConnectionManager.instance.retrieveSql("snmp_manager")
 +def hubNames = []
 +
 +if(networkIdentifier == 'All')
 +{
 +     hubNames << "All"
 +     db.eachRow("""
 +SELECT DISTINCT
 +    ne.name
 +FROM
 +    snmp_manager.network_element ne
 +WHERE
 +    ne.show_in_monitor AND ne.on_air
 +    AND ne.ne_type = 'Controller'
 +    ORDER BY 1;"""){
 +     hubNames << it.name
 +     }
 +}
 +
 +else{
 +     hubNames << "All"
 +     db.eachRow("""
 +  SELECT DISTINCT ne.name
 +  FROM
 +  snmp_manager.network_element ne
 +  WHERE ne.show_in_monitor AND ne.on_air
 +  AND ne.ne_type = "Controller"
 +  AND ne.network_identifier = '${networkIdentifier}'
 +  ORDER BY 1;"""){
 +     hubNames << it.name
 +     }
 +}
 +log.info hubNames
 +return hubNames as String[]
 +</code>
 +----
 +
 +==== 4. Region ====
 +Region is used to group network elements based on their regions. This is done in the Ticketer database through the ticketer.visibility(region_id) and the ticketer.region(id)
 +
 +**Region Variable Script**
 +
 +<code>
 +import groovy.sql.Sql
 +import com.osssoftware.reporting.database.*      
 +def ticketerDb = SqlConnectionManager.instance.retrieveSql('ticketer')
 +
 +def query = """
 +SELECT DISTINCT r.name  as region from ticketer.visibility v JOIN ticketer.region r on r.id = v.region_id 
 +"""
 +      def list = []
 +      list << "All"
 +      ticketerDb.eachRow(query) {
 +          list.add(it.region)
 +      }
 +      return list as String[]
 +</code>
 +
 +----
 +
 +==== 5. Cluster/ Visibility/ Market ====
 +
 +
 +These terms are used interchangeably but essentially mean the same thing. The tables used are ticketer.visibility (name) and snmp_manager.network_element (cluster_name)
 +
 +**Query to retrieve visibilities:**
 +<code>SELECT name from ticketer.visibility where name LIKE "%-%";</code>
 +
 +----
 +
 +==== 6. District ====
 +
 +District is used to group network elements based on their districts. This is done in the Ticketer database through the ticketer.visibility(district_id) and the ticketer.district(id)
 +
 +Note: As of the time of writing (May 2017) Extenet do not use the district functionality.

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki