Conventions - Report Variables

Author: Michelle McCausland

1. 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.
2. Report variables should be written in camel case i.e. myVariableIsAwesome.
3. Report variable names should be clear and obvious to what they are referring to, NO: myVariable1 , YES: districtValue
4. 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

The network identifier is an ExteNet used field used for grouping network elements based on location.

Query to retrieve network identifier:

SELECT DISTINCT(network_identifier) from snmp_manager.network_element WHERE network_identifier != "" ORDER BY 1;

Network Identifier variable script:

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[]

Script for use when Network Identifier is dependant on region & carrier:

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[]

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:

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[]

Useful Carrier Info:

   Clearwire is now Sprint so is deprecated

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:

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";

Please consult Conventions - General Report Writing for more detail on using nodes.

Script for use when Hub(network element) is dependant on network identifier:

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[]

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

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[]

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:

SELECT name from ticketer.visibility where name LIKE "%-%";

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.