Troubleshooting

Common issues and solutions for the OSPROV Query Builder.

Common Issues

No Data Found

Symptoms

• Query returns empty results
• "No matching records" message
• Zero count in aggregations

Possible Causes and Solutions

1. Filters Too Restrictive

Check: Review all applied filters

Solution:
1. Remove filters one by one
2. Test after each removal
3. Identify the problematic filter
4. Adjust filter criteria

2. Date Range Issues

Check: Date filter format and range

Common Issues:
- Date format mismatch (YYYY-MM-DD vs MM/DD/YYYY)
- Time zone differences
- Future date ranges
- Inverted from/to dates

Solution:
- Use standard ISO format: YYYY-MM-DD
- Verify time zone settings
- Check date range logic

3. Workflow Has No Data

Check: Selected workflow submissions

Verification Steps:
1. Confirm workflow has published status
2. Check if workflow has any submissions
3. Verify user permissions for the workflow

4. Field Name Changes

Check: Form structure modifications

If forms were updated:
1. Field names may have changed
2. Field types may be different
3. Fields may have been removed

Solution:
- Review current form structure
- Update query field selections
- Recreate query if necessary

Invalid Field Errors

Error Messages

• "Field 'field_name' does not exist"
• "Invalid field type for operation"
• "Cannot aggregate non-numeric field"

Solutions

Field Doesn't Exist

Steps to Resolve:
1. Check exact field name spelling
2. Verify field exists in current form version
3. Use field selector instead of manual entry
4. Check for special characters in field names

Type Mismatch

Common Type Issues:
- Text field with numeric operations
- Date field with text operations
- Null values in required fields

Solution:
- Verify field type in form builder
- Use appropriate operations for field type
- Handle null values explicitly

API Authentication Errors

401 Unauthorized

Invalid Token

Symptoms:
- "Invalid or expired token"
- "Authentication required"

Solutions:
1. Verify token hasn't expired
2. Check token format (Bearer prefix)
3. Ensure token has correct permissions
4. Generate new token if needed

Token Format Issues

Correct Format:
Authorization: Bearer your-token-here

Common Mistakes:
- Missing "Bearer" prefix
- Extra spaces
- Token truncated
- Wrong header name

403 Forbidden

Permission Denied

Causes:
- User lacks workflow access
- Token permissions insufficient
- IP restriction in place

Solutions:
1. Verify user role and permissions
2. Check workflow access settings
3. Review IP whitelist configuration
4. Contact administrator for access

Performance Issues

Slow Query Response

Large Dataset

Symptoms:
- Response time > 5 seconds
- Timeout errors
- Incomplete results

Solutions:
1. Add more specific filters
2. Reduce selected fields
3. Implement pagination
4. Use date range limits

Complex Aggregations

Optimization Steps:
1. Simplify aggregation logic
2. Pre-filter data before aggregating
3. Cache frequently used aggregations
4. Consider summary tables for reports

Missing Indexes

Performance Improvements:
1. Filter on indexed fields first
2. Use exact match over contains
3. Avoid complex string operations
4. Contact admin for index creation

Data Accuracy Issues

Incorrect Aggregation Results

Null Value Handling

Problem: Nulls affecting calculations
Solutions:
- Add "Is Not Null" filter for aggregated fields
- Use COALESCE in calculations
- Handle nulls in application logic

Duplicate Records

Problem: Same record counted multiple times
Solutions:
- Use COUNT DISTINCT for unique counts
- Check for data duplication in source
- Review join conditions if applicable

Missing Data

Partial Results

Symptoms:
- Known records not appearing
- Incomplete data sets
- Inconsistent counts

Checklist:
1. Check filter date ranges
2. Verify all filter conditions
3. Review field selections
4. Check pagination settings
5. Verify permissions

Query Management Issues

Cannot Edit Query

Query Locked

Reasons:
- Another user editing
- System maintenance
- Version conflict

Solutions:
1. Wait and retry
2. Check with other users
3. Create new version
4. Contact administrator

Permission Issues

Requirements:
- Must be query owner or admin
- Need edit permissions on workflow
- Account must be active

Resolution:
- Verify your role
- Request permission update
- Use "Save As" for new version

Cannot Delete Query

Dependencies Exist

Check for:
- Active API consumers
- Dashboard widgets using query
- Scheduled reports
- Other queries referencing this one

Solution:
1. Identify dependencies
2. Update dependent systems
3. Remove references
4. Then delete query

Error Reference

Error Codes and Solutions

Code	Error	Description	Solution
400	Bad Request	Invalid query parameters	Check parameter format and values
401	Unauthorized	Authentication failed	Verify API token
403	Forbidden	Access denied	Check permissions
404	Not Found	Query doesn't exist	Verify query name
409	Conflict	Query name already exists	Use different name
422	Unprocessable	Invalid filter/aggregation	Review query logic
429	Rate Limited	Too many requests	Implement backoff
500	Server Error	Internal error	Contact support
503	Unavailable	Service down	Wait and retry

Debugging Techniques

Query Testing Strategy

1. Incremental Building

Build queries step by step:
1. Start with workflow selection only
2. Add one field
3. Add one filter
4. Test after each addition
5. Add aggregations last

2. Filter Isolation

Test filters individually:
1. Disable all filters
2. Enable one filter
3. Verify results
4. Add next filter
5. Identify problematic filter

3. Known Data Testing

Use known test records:
1. Create test submission
2. Note all field values
3. Build query to find it
4. Verify query returns test record

API Debugging

Using cURL for Testing

# Basic API test
curl -X GET "https://api.osprov.com/api/query/test-query" \
  -H "Authorization: Bearer your-token" \
  -H "Content-Type: application/json" \
  -v  # Verbose output for debugging

# Test with filters
curl -X GET "https://api.osprov.com/api/query/test-query?\
filters[status][operation]=equals&\
filters[status][value]=Active" \
  -H "Authorization: Bearer your-token" \
  -v

Response Headers to Check

X-RateLimit-Remaining: 999
X-Query-Time: 145ms
X-Total-Count: 1500
X-Cache-Status: MISS

Logging and Monitoring

What to Log

Essential Information:
- Query name and version
- Execution timestamp
- Response time
- Record count
- Error messages
- User/API token
- Filter parameters
- Aggregation results

Log Analysis

Look for patterns:

• Frequent errors
• Performance degradation
• Usage spikes
• Failed authentications

Getting Help

Before Contacting Support

Gather this information:

1. Query name and ID
2. Error messages (exact text)
3. Steps to reproduce
4. Time of occurrence
5. API token ID (not the token itself)
6. Browser/client information
7. Network logs if available

Support Channels

1. System Administrator

   • Permission issues
   • Workflow access
   • User management

2. Technical Support

   • Query errors
   • Performance issues
   • API problems

3. Documentation

   • Feature questions
   • Best practices
   • Integration guides

Emergency Procedures

Query Causing System Issues

If a query impacts system performance:

1. Immediate Actions:

   • Stop query execution
   • Disable API endpoint
   • Clear query cache
   • Notify administrator

2. Investigation:

   • Review query complexity
   • Check data volume
   • Analyze filter efficiency
   • Examine aggregation load

3. Resolution:

   • Optimize query design
   • Add appropriate filters
   • Implement pagination
   • Consider archiving old data

Frequently Asked Questions

Q: Why does my query work in preview but fail via API?

A: Common causes:

• Authentication issues (token expired or invalid)
• Different permissions between UI and API user
• Parameter encoding issues in API request
• Rate limiting on API but not preview

Q: How can I improve query performance?

A: Performance tips:

1. Filter before aggregating
2. Select only needed fields
3. Use indexed fields in filters
4. Implement pagination
5. Cache stable results
6. Avoid complex string operations

Q: Why do counts differ between UI and API?

A: Possible reasons:

• Pagination limits in API
• Different filter parameters
• Time-based data changes
• Permission differences
• Cache inconsistencies

Q: Can I recover a deleted query?

A: Recovery options:

• Check version history (if enabled)
• Restore from backup (contact admin)
• Recreate from API logs
• Use export if previously saved

Q: How do I handle timeout errors?

A: Timeout solutions:

1. Reduce query complexity
2. Add more restrictive filters
3. Decrease field selection
4. Implement pagination
5. Break into multiple smaller queries
6. Increase timeout setting (if allowed)

Next Steps

• Review Best Practices to prevent issues
• Explore Advanced Features for optimization
• Check API Documentation for integration help