Temp_MSSPLASHPage/TROUBLESHOOTING_PASSERSBY.md

Troubleshooting Guide: Passersby (Transeúntes) Data Showing as 0

📋 Table of Contents

1. System Overview
2. How Data Flows
3. Common Problems & Solutions
4. Step-by-Step Diagnosis
5. Database Schema Reference
6. Code References

---

🎯 System Overview

The Passersby (Transeúntes) Tracking System uses WiFi scanning data from Cisco Meraki devices to detect people passing by your locations, even if they don't connect to your WiFi network.

Key Concepts

• PasserBy (Transeúnte): A device detected with:

  • No SSID (not connected to WiFi): SSID IS NULL OR SSID = ''
  • Weak signal (RSSI < -60): Device is outside or far from access points
  • Valid manufacturer (not in exclusion list)

• Visitor: A device detected with:

  • No SSID (not connected to WiFi)
  • Strong signal (RSSI >= -60): Device is inside the location
  • Valid manufacturer

• Connected User: A device that has connected to your WiFi (has SSID)

Real-Time vs Historical Data

• Real-Time Widget (PassersRealTimeWidget):

  • Shows data from last 30 minutes only
  • Queries SplashWiFiScanningData table directly
  • Updates every 15 seconds
  • Located at: src/SplashPage.Web.Ui/src/app/dashboard/dynamicDashboard/_components/widgets/realtime/PassersRealTimeWidget.tsx

• Historical Metrics:

  • Uses database views: scanning_report_daily_unique and scanning_report_hourly_full
  • Aggregated data by day/hour
  • Used for reports and analytics

---

🔄 How Data Flows

┌─────────────────┐
│  Meraki Devices │
│  (WiFi Scanning)│
└────────┬────────┘
         │ Sends scanning data every few minutes
         ▼
┌─────────────────────────────────────┐
│  Webhook Endpoint                   │
│  /ScanningAPI/ReceiveScanningData   │
│  (ScanningAPIController.cs:64)      │
└────────┬────────────────────────────┘
         │ Validates & filters data
         ▼
┌─────────────────────────────────────┐
│  SplashWiFiScanningData Table       │
│  (Raw scanning data)                │
└────────┬────────────────────────────┘
         │
         ├──► Real-Time Path (30 min window)
         │    └──► SplashMetricsService.RealTimeStats()
         │         └──► PassersRealTimeWidget (Frontend)
         │
         └──► Historical Path
              └──► Database Views (daily/hourly aggregation)
                   └──► SplashMetricsQueryService
                        └──► Reports & Analytics

Critical Components

1. Meraki Dashboard: Must have Location Scanning webhook configured
2. SplashLocationScanningConfigs: Must have IsEnabled=1 and SyncStatus='Active'
3. SplashWiFiScanningData: Raw data table populated by webhook
4. Database Views: Must exist for historical reporting
5. Dashboard Network Selection: Only selected networks show data

---

⚠️ Common Problems & Solutions

Problem 1: Location Scanning Not Enabled or Synced

Symptoms:

• Master diagnostic shows: "Scanning Deshabilitado" or "Sync No Activo"
• No data in SplashLocationScanningConfigs table
• IsEnabled = 0 or SyncStatus != 'Active'

Causes:

• Feature never configured for this network
• Configuration failed to sync with Meraki
• Network was disabled manually

Solutions:

1. Go to: Dashboard → Settings → Location Scanning (Synced Networks)
2. Find the network and click "Enable"
3. Verify sync status changes to "Active"
4. Check ErrorMessage field if sync fails
5. Ensure Meraki organization has Location Scanning enabled

SQL to Check:

SELECT Name, IsEnabled, SyncStatus, ErrorMessage, LastSyncedAt
FROM SplashLocationScanningConfigs c
JOIN SplashMerakiNetworks n ON n.Id = c.NetworkId
WHERE n.IsDeleted = 0;

---

Problem 2: No Data Received from Meraki (Webhook Not Working)

Symptoms:

• Master diagnostic shows: "No hay datos"
• SplashWiFiScanningData table is empty or has no recent records
• Last record is older than 1 hour

Causes:

• Webhook not configured in Meraki Dashboard
• Webhook URL is incorrect
• Validator secret doesn't match
• Firewall blocking incoming webhooks
• Meraki devices not scanning

Solutions:

1. Verify Webhook Configuration in Meraki Dashboard:

   • Login to Meraki Dashboard
   • Go to: Network → General → Location and Scanning
   • Check webhook URL matches PostUrl from database
   • Expected format: https://api-moonshot-center.beprime.mx/ScanningAPI/ReceiveScanningData/{secret}
   • Verify "Post URL" is active and shows recent activity

2. Check Application Logs:

   • Look for webhook validation errors
   • Check for 401 (unauthorized) or 400 (bad request) errors
   • Verify Secret parameter matches SplashPageConsts.EnvSecretKey

3. Test Webhook Manually:

   • Use Meraki Dashboard's "Test" button
   • Check application logs for received requests

4. Verify API Version:

   • Database should have ApiVersion = "3.0"
   • Meraki webhook should send version "3.0"

SQL to Check:

-- Check configuration
SELECT NetworkId, PostUrl, ValidatorSecret, ApiVersion, LastSyncedAt
FROM SplashLocationScanningConfigs
WHERE IsEnabled = 1;

-- Check recent data
SELECT MAX(CreationTime) as last_record,
       TIMESTAMPDIFF(MINUTE, MAX(CreationTime), NOW()) as minutes_ago
FROM SplashWiFiScanningData;

Code Reference:

• Controller: src/SplashPage.Web.Host/Controllers/ScanningAPIController.cs:64
• Validation logic: Lines 85-94

---

Problem 3: All Data is Filtered Out

Symptoms:

• Master diagnostic shows: "Todos los manufacturers excluidos" or "All devices have SSID"
• Raw data exists but no passersby in calculations
• Query 2 shows passersby_eligible = 0

Causes:

• All manufacturers in exclusion list
• All devices are connected to WiFi (have SSID)
• No devices with weak signal (RSSI >= -60 for all)

Solutions:

3a. Manufacturer Exclusion:

-- Check excluded manufacturers
SELECT Manufacturer,
       SUM(CASE WHEN ManufacturerIsExcluded = 1 THEN 1 ELSE 0 END) as excluded,
       SUM(CASE WHEN ManufacturerIsExcluded = 0 THEN 1 ELSE 0 END) as included
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY Manufacturer
ORDER BY excluded DESC;

• Review ScanningAPIController.cs:99-107 for exclusion logic
• Manufacturers like "Unknown", "Meraki", etc. may be automatically excluded
• Update exclusion list if needed

3b. All Devices Have SSID (Connected to WiFi):

-- Check SSID distribution
SELECT
    CASE WHEN SSID IS NULL OR SSID = '' THEN 'No SSID' ELSE 'Has SSID' END as status,
    COUNT(*) as count
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
GROUP BY status;

• If all devices have SSID, they're not "passersby" - they're connected users
• This is normal during off-peak hours or in locations with few passersby
• Verify Meraki is scanning non-connected devices (check Meraki settings)

3c. No Weak Signals (All RSSI >= -60):

-- Check RSSI distribution
SELECT
    CASE
        WHEN NearestApRssi < -60 THEN 'Weak (PasserBy)'
        ELSE 'Strong (Visitor/Inside)'
    END as signal_strength,
    COUNT(DISTINCT ClientMac) as devices
FROM SplashWiFiScanningData
WHERE CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
  AND (SSID IS NULL OR SSID = '')
GROUP BY signal_strength;

• If all signals are strong (>= -60), devices are inside, not passing by
• This is the expected classification logic - they're "Visitors", not "PassersBy"
• Check frontend to ensure "Visitors" metric is displayed

Code Reference:

• RSSI threshold: src/SplashPage.Application/Splash/SplashMetricsService.cs:774
• SSID filter: src/SplashPage.Application/Splash/SplashMetricsService.cs:682

---

Problem 4: Database Views Don't Exist

Symptoms:

• Historical reports show no data
• Error querying scanning_report_daily_unique or scanning_report_hourly_full
• Master diagnostic works but historical queries fail

Causes:

• Database migrations not run
• Views were never created
• Views were dropped accidentally

Solutions:

1. Check if views exist:

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('scanning_report_daily_unique', 'scanning_report_hourly_full')
  AND TABLE_TYPE = 'VIEW';

2. Create views from SQL files:

   • Location: SQL/scanning_report/scanning_report_daily_unique.sql
   • Run the SQL script to create the views
   • These views aggregate raw data for reporting

3. Verify views work:

SELECT COUNT(*) FROM scanning_report_daily_unique;
SELECT COUNT(*) FROM scanning_report_hourly_full;

Code Reference:

• View configuration: src/SplashPage.EntityFrameworkCore/Configurations/SplashWifiScanningReportConfiguration.cs:15
• View configuration: src/SplashPage.EntityFrameworkCore/Configurations/ScanningReportHourlyFullConfiguration.cs:19

---

Problem 5: Wrong Networks Selected in Dashboard

Symptoms:

• Data exists but widget shows 0
• Other dashboards show data correctly
• Changing network selection fixes the issue

Causes:

• Dashboard filters are excluding the networks with data
• Network groups don't include the correct networks

Solutions:

1. Check dashboard network selection:

SELECT
    d.Name as dashboard_name,
    n.Name as network_name,
    n.Id as network_id
FROM SplashDashboards d
LEFT JOIN SplashNetworkGroups ng ON FIND_IN_SET(ng.Id, REPLACE(REPLACE(d.SelectedNetworkGroups, '[', ''), ']', ''))
LEFT JOIN SplashNetworkGroupMembers m ON m.NetworkGroupId = ng.Id
LEFT JOIN SplashMerakiNetworks n ON n.Id = m.NetworkId
WHERE d.Id = ? -- Replace with dashboard ID
  AND d.IsDeleted = 0;

2. Verify which networks have data:

SELECT
    n.Name as network_name,
    COUNT(*) as records_last_30min
FROM SplashWiFiScanningData s
JOIN SplashMerakiNetworks n ON n.Id = s.NetworkId
WHERE s.CreationTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
GROUP BY n.Name
ORDER BY records_last_30min DESC;

3. Update dashboard filters to include networks with data

Code Reference:

• Service filter: src/SplashPage.Application/Splash/SplashMetricsService.cs:676 (line filtering by SelectedNetworks)

---

Problem 6: Real-Time Window (30 Minutes) Has No Data

Symptoms:

• Historical data exists
• Master diagnostic shows old data (> 30 minutes ago)
• Real-time widget shows 0

Causes:

• Data is old (more than 30 minutes)
• Webhook stopped working recently
• Network activity paused

Solutions:

1. Check data recency:

SELECT
    MAX(CreationTime) as last_record,
    TIMESTAMPDIFF(MINUTE, MAX(CreationTime), NOW()) as minutes_ago,
    COUNT(*) as total_records
FROM SplashWiFiScanningData;

2. If data is old (> 30 min):

   • Follow Problem 2: No Data Received
   • Verify webhook is still active
   • Check for recent errors in logs

3. Check if this is normal:

   • Low traffic times may legitimately have no passersby
   • Verify with other time periods (use historical data)

Code Reference:

• 30-minute window: src/SplashPage.Application/Splash/SplashMetricsService.cs:668 (AddMinutes(-30))

---

🔍 Step-by-Step Diagnosis

Follow this process to diagnose the issue systematically:

Step 1: Run Master Diagnostic Query

-- Open DIAGNOSTIC_QUERIES_PASSERSBY.sql
-- Run the MASTER DIAGNOSTIC QUERY at the top

What to look for:

• diagnosis column will tell you the problem
• recommended_action column will tell you what to do

Step 2: Identify the Problem Category

Based on the diagnosis:

Diagnosis	Go To
"Scanning Deshabilitado"	Problem 1
"Sync No Activo"	Problem 1
"No hay datos"	Problem 2
"Datos obsoletos"	Problem 6
"Todos los manufacturers excluidos"	Problem 3a
"All devices have SSID"	Problem 3b
"No weak signals"	Problem 3c

Step 3: Run Specific Diagnostic Queries

Based on the problem, run the corresponding queries from DIAGNOSTIC_QUERIES_PASSERSBY.sql:

• Problem 1: Query 4 (Configuration)
• Problem 2: Query 1, 2, 12 (Data recency)
• Problem 3: Query 5, 6, 7 (Filters)
• Problem 4: Query 8, 9 (Views)
• Problem 5: Query 11 (Dashboard selection)
• Problem 6: Query 2, 10 (Real-time window)

Step 4: Apply Solution

Follow the solution steps for the identified problem.

Step 5: Verify Fix

After applying the solution:

1. Wait 5-10 minutes for new data
2. Run Query 2 (Real-Time Window) again
3. Check frontend widget for updates
4. Run Query 3 to simulate PassersBy calculation

---

📊 Database Schema Reference

Table: SplashWiFiScanningData

Primary table for raw scanning data from Meraki webhooks.

Column	Type	Description
Id	int	Primary key
NetworkId	int	Foreign key to SplashMerakiNetworks
ClientMac	string	Device MAC address (unique identifier)
SSID	string	WiFi network name (NULL for passersby)
Manufacturer	string	Device manufacturer
ManufacturerIsExcluded	bool	True if in exclusion list
OS	string	Device operating system
NearestApMac	string	Nearest access point MAC
NearestApRssi	int	Signal strength (-90 to 0, higher is stronger)
NearestApTime	DateTime	Detection timestamp from Meraki
CreationTime	DateTime	Record insertion timestamp

Key: src/SplashPage.Core/Splash/SplashWiFiScanningData.cs

---

Table: SplashLocationScanningConfigs

Configuration for Location Scanning feature per network.

Column	Type	Description
Id	int	Primary key
NetworkId	int	Foreign key to SplashMerakiNetworks (unique)
IsEnabled	bool	Feature enabled flag
SyncStatus	string	"Pending", "Active", "Failed", "NotConfigured"
ValidatorSecret	string	Webhook validation secret
PostUrl	string	Webhook endpoint URL
ApiVersion	string	Meraki API version (default "3.0")
LastSyncedAt	DateTime	Last successful sync
ErrorMessage	string	Error details if sync failed
FailedAttempts	int	Number of failed sync attempts

Key: src/SplashPage.Core/Splash/SplashLocationScanningConfig.cs

---

View: scanning_report_daily_unique

Aggregated daily unique visitors/passersby per MAC address.

Column	Type	Description
MacAddress	string	Device MAC (part of composite key)
DetectionDate	date	Date of detection (part of composite key)
NetworkId	int	Network ID (part of composite key)
PersonType	string	"PasserBy" or "Visitor"
PresenceCategory	string	Dwell time classification
DurationInMinutes	int	Total time detected
DetectionsCount	int	Number of detections
AverageRssi	int	Average signal strength
FirstDetection	DateTime	First time seen
LastDetection	DateTime	Last time seen

Key: src/SplashPage.Core/Splash/SplashWifiScanningReport.cs SQL: SQL/scanning_report/scanning_report_daily_unique.sql

---

💻 Code References

Backend Services

Component	Location	Purpose
Webhook Controller	src/SplashPage.Web.Host/Controllers/ScanningAPIController.cs:64	Receives data from Meraki
Real-Time Stats	src/SplashPage.Application/Splash/SplashMetricsService.cs:794	Calculates PassersBy/Visitors
PassersBy Logic	src/SplashPage.Application/Splash/SplashMetricsService.cs:670-774	Filters and classifies devices
Historical Metrics	src/SplashPage.Application/Splash/SplashMetricsQueryService.cs:119	Uses database views

Frontend Components

Component	Location	Purpose
Real-Time Widget	src/SplashPage.Web.Ui/src/app/dashboard/dynamicDashboard/_components/widgets/realtime/PassersRealTimeWidget.tsx	Displays PassersBy count
API Hook	Auto-generated	usePostApiServicesAppSplashmetricsserviceRealtimestats

Configuration Files

File	Location	Purpose
Entity Config	src/SplashPage.EntityFrameworkCore/Configurations/SplashWiFiScanningDataConfiguration.cs	Table mapping
View Config	src/SplashPage.EntityFrameworkCore/Configurations/SplashWifiScanningReportConfiguration.cs	Daily view mapping
DbContext	src/SplashPage.EntityFrameworkCore/EntityFrameworkCore/SplashPageDbContext.cs	Database context

---

🎯 Key Business Logic Rules

PasserBy Classification

A device is classified as a PasserBy if:

1. ✅ Not connected to WiFi: SSID IS NULL OR SSID = ''
2. ✅ Not in exclusion list: ManufacturerIsExcluded = false
3. ✅ Has valid manufacturer: Manufacturer IS NOT NULL
4. ✅ Weak signal: AverageRssi < -60
5. ✅ Detected in last 30 minutes (for real-time)
6. ✅ Network is in selected networks

Code: src/SplashPage.Application/Splash/SplashMetricsService.cs:774

RSSI Threshold

• RSSI < -60: PasserBy (outside, far from AP)
• RSSI >= -60: Visitor (inside, close to AP)

RSSI values typically range from -90 (very weak) to -30 (very strong).

Real-Time Window

• Widget refreshes every 15 seconds
• Only shows data from last 30 minutes
• Uses CreationTime >= NOW() - INTERVAL 30 MINUTE

---

📝 Quick Reference: SQL Queries by Use Case

Use Case	Query Number	Query Name
First diagnostic	Master	Master Diagnostic Query
Check if data exists	1	Raw Data (24h)
Check real-time window	2	Real-Time Window (30 min)
Simulate widget calculation	3	Simulate PassersBy
Check configuration	4	Location Scanning Config
Check manufacturer filters	5	Excluded Manufacturers
Check SSID distribution	6	SSID Distribution
Check signal strength	7	RSSI Distribution
Check views exist	8	Database Views Existence
Check historical data	9	Historical Data (Daily)
Check time distribution	10	Time Distribution (24h)
Check dashboard filters	11	Dashboard Network Selection
Check recent activity	12	Recent Webhook Activity

---

🆘 Getting Help

If you've followed this guide and still see 0 passersby:

1. Collect diagnostics:

   • Run Master Diagnostic Query
   • Run Query 12 (Recent Activity)
   • Export results to share with team

2. Check logs:

   • Application logs for webhook errors
   • Meraki Dashboard logs for webhook activity

3. Verify Meraki side:

   • Login to Meraki Dashboard
   • Check Location and Scanning is enabled
   • Verify webhook shows recent posts
   • Test webhook manually

4. Review recent changes:

   • Check changelog.MD for recent code changes
   • Review database migrations
   • Check if configuration was modified

---

✅ Success Checklist

After fixing the issue, verify:

• Master diagnostic shows "OK - Should have data"
• Query 2 (Real-Time Window) shows passersby_eligible > 0
• Query 3 shows devices classified as PasserBy
• Frontend widget displays non-zero value
• Widget refreshes every 15 seconds
• Historical reports also show data
• Multiple networks showing data (if applicable)

---

Last Updated: 2025-01-10 Related Files:

• DIAGNOSTIC_QUERIES_PASSERSBY.sql
• SQL/scanning_report/scanning_report_daily_unique.sql