E911 Subnet CSV Lookup App

Overview

The Subnet CSV Lookup app matches phone IP addresses against CSV subnet data to provide location context for emergency calls and network tracking. This post-call app works with the Phone Discovery App to identify where a caller is located based on their network subnet.

The app supports both local CSV data and remote CSV URLs with automatic synchronization, making it ideal for emergency response teams who need real-time location information and network administrators tracking device locations.

Prerequisites

Before using the Subnet CSV Lookup app:

• Call Telemetry Appliance: Version 0.7.0 or later. Deployment Guide
• Call Telemetry Premium or Demo License
• Cisco CallManager 8.5: Configured with CURRI API Integration. Setup Guide
• Phone Discovery App: Must be configured to run before this app to provide IP address data
• CSV subnet data: Either local data or accessible remote URL with subnet information

How the App Works

The Subnet CSV Lookup app integrates with the Phone Discovery App workflow:

1. Phone Discovery: The Phone Discovery App first identifies the calling phone's IP address from network data
2. Subnet Matching: This app compares the IP address against your CSV subnet list using CIDR notation
3. Location Data: Returns structured location data from the matching CSV row
4. Alert Integration: Location data becomes available for call alerts and other downstream apps

CSV Data Format

Required CSV Structure

Your CSV data must include separate subnet and mask columns. The first row should contain headers that define your data fields:

subnet,mask,building,floor,room,address,city,state,zip
10.1.1.0,255.255.255.0,HQ,1,Lobby,123 Main St,Boston,MA,02101
10.1.2.0,255.255.255.0,HQ,2,Room 201,123 Main St,Boston,MA,02101
192.168.1.0,255.255.255.0,Branch,1,Office,456 Oak Ave,Cambridge,MA,02139
10.100.0.0,255.255.0.0,VPN,Remote,Workers,Remote Access,N/A,N/A,N/A

Required Columns:

• subnet - Network address (e.g., 10.1.1.0)
• mask - Subnet mask (e.g., 255.255.255.0)

Subnet Notation Guidelines

• Use separate subnet and mask columns: subnet: 10.1.1.0, mask: 255.255.255.0
• Avoid overlapping subnets: Most specific subnet will be matched automatically
• Order doesn't matter: The app automatically selects the most specific match
• Include catch-all ranges: Add VPN or guest network ranges for remote users

App Configuration

Local CSV Configuration

For static subnet data that doesn't change frequently:

• Header Row: Define your CSV column names
• CSV Data: Paste your subnet data directly into the app
• Match Message: Message Template for successful matches
• No Match Message: Message when no subnet is found

Remote CSV Configuration

For dynamic subnet data that updates regularly:

• CSV URL: HTTP/HTTPS URL to your subnet CSV file
• Sync Schedule: Enable automatic hourly synchronization
• Fallback Data: Local CSV data used if remote fetch fails
• Remote Match Message: Custom message template for remote data matches

Message Templates

Using CSV Data in Messages

Message templates allow you to create dynamic responses using data from your CSV. Any column header from your CSV becomes available as a template variable:

Example Template:

Emergency Location: {{building}} Floor {{floor}} {{room}} - {{address}}, {{city}} {{state}}

Example Output:

Emergency Location: HQ Floor 2 Room 201 - 123 Main St, Boston MA

Available Template Variables

• CSV Fields: Any column from your CSV (, , , etc.)
• Network Data: Information from the phone discovery app
• Call Data: Original call event information

Message Configuration Examples

E911 Emergency Response:

EMERGENCY CALL: {{building}} {{floor}} {{room}} at {{address}}, {{city}} {{state}} {{zip}}

Network Inventory Tracking:

Device Location: {{department}} network (VLAN {{vlan}}) - Contact: {{contact_email}}

Remote Worker Detection:

{{type}} connection from {{location}} via {{vpn_gateway}}

Common Use Cases

Emergency Location Services

Configure the app to provide detailed location information for 911 calls:

subnet,mask,building,floor,room,address,city,state,zip,emergency_contact
10.1.1.0,255.255.255.0,HQ,1,Lobby,123 Main St,Boston,MA,02101,[email protected]
10.1.2.0,255.255.255.0,HQ,2,Suite 200,123 Main St,Boston,MA,02101,[email protected]
192.168.1.0,255.255.255.0,Branch,1,Main Office,456 Oak Ave,Cambridge,MA,02139,[email protected]

Message Template:

EMERGENCY: {{address}}, {{city}} {{state}} {{zip}} - {{building}} {{floor}} {{room}} (Contact: {{emergency_contact}})

Remote Worker Identification

Distinguish between office and remote workers for appropriate emergency response:

subnet,mask,type,location,response_type
10.1.0.0,255.255.0.0,Office,Corporate HQ,Dispatch to building
192.168.0.0,255.255.0.0,Office,Branch Office,Contact local security
10.100.0.0,255.255.0.0,VPN,Remote Workers,Contact via phone/email only
172.16.0.0,255.240.0.0,Guest,Guest Network,Limited location data

App Response Format

Successful Local Match Response

{
  "result": "success",
  "message": "Emergency Location: HQ Floor 2 Room 201 - 123 Main St, Boston MA",
  "provider_message": "csv_lookup: matched local subnet: 10.1.2.0- ip: 192.168.1.50",
  "payload": {
    "type": "local"
  },
  "debug": {
    "match": {
      "subnet": "10.1.2.0",
      "mask": "255.255.255.0",
      "building": "HQ",
      "floor": "2",
      "room": "Room 201",
      "address": "123 Main St",
      "city": "Boston",
      "state": "MA",
      "zip": "02101"
    },
    "csv": "10.1.2.0,255.255.255.0,HQ,2,Room 201,123 Main St,Boston,MA,02101",
    "header_row": "subnet,mask,building,floor,room,address,city,state,zip"
  }
}

Successful Remote Match Response

{
  "result": "success",
  "message": "Found in network database: Regional Office",
  "provider_message": "csv_lookup: remote subnet matched 192.168.1.50",
  "payload": {
    "type": "remote"
  },
  "debug": {
    "match": {
      "subnet": "192.168.1.0",
      "mask": "255.255.255.0",
      "location": "Regional Office"
    }
  }
}

No Match Response

{
  "result": "no_match_local",
  "message": "UNKNOWN LOCATION - Dispatch to main address",
  "provider_message": "csv_lookup: no match for phone ip 10.5.5.5",
  "payload": {},
  "debug": {
    "reason": "No match found. Match? returned null/nil.",
    "csv": "10.1.1.0,255.255.255.0,HQ,1,Lobby",
    "header_row": "subnet,mask,building,floor,room",
    "pipeline_phone_ip": "10.5.5.5"
  }
}

No Phone Data Available

{
  "result": "no_phone",
  "message": "No phone found available in workflow.",
  "provider_message": "No phone found available in workflow.",
  "payload": {
    "type": "no_phone"
  },
  "debug": {}
}

Troubleshooting

Common Issues

App Not Finding Matches:

• Verify subnet/mask format is correct (separate subnet and mask columns required)
• Check that Phone Discovery App is running first to provide IP data
• Ensure IP address is within your defined subnet ranges
• Confirm CSV contains the required subnet and mask columns

CSV Format Errors:

• "CSV is missing required columns: subnet, mask" - Add required columns to your CSV
• "CSV contains no data rows" - Ensure CSV has data beyond the header row
• "Error validating CSV data" - Review header row and data format consistency

Remote CSV Not Loading:

• Verify the CSV URL is accessible from your Call Telemetry server
• Check that the CSV format matches your header row configuration
• Review network connectivity and firewall settings
• Monitor the last sync timestamp for remote CSV updates

Template Variables Not Working:

• Confirm CSV column names match your template variables exactly
• Check for spaces or special characters in CSV headers
• Verify the CSV data contains values for the referenced columns

App Response Types:

• success - Subnet match found (check payload.type for local/remote)
• no_match_local - No matching subnet found for the phone's IP
• no_phone - Phone Discovery App didn't provide IP address data

Best Practices

CSV Structure:

• Use separate subnet and mask columns (required format)
• Use consistent delimiter (comma) throughout
• Quote fields containing commas or special characters
• Keep column names short and descriptive
• Most specific subnet will be automatically matched

Performance:

• Limit CSV size for better lookup performance
• Use remote CSV for frequently changing data
• Consider splitting large CSVs by location or department
• Test with representative data before production deployment

Integration Workflow:

• Always configure Phone Discovery App to run before Subnet CSV Lookup
• Test the complete workflow with Call Tests before production
• Monitor app execution order and timing in call event logs
• Verify CSV contains required subnet and mask columns before deployment

Integration with Other Apps

The Subnet CSV Lookup app works well with other Call Telemetry apps:

• Phone Discovery App: Provides the IP address data for subnet matching
• Call Alerts: Use location data in emergency notification templates
• Webhook Apps: Send enriched location data to external systems
• Teams/SMS Apps: Include location context in emergency messages

By combining these apps in a policy rule, you can create emergency response workflows that automatically provide location context and notify appropriate personnel about emergency calls.