ld-sysinfo-server-backend/REPORTING_API_IMPLEMENTATION.md

Reporting API Implementation

Overview

Backend API endpoints for compliance reporting system providing security metrics, vulnerability data, and software inventory reporting.

Implemented Components

1. DTOs (Data Transfer Objects)

Location: src/main/java/com/psg/dlsysinfo/dl_sysinfo_server/dto/

ComplianceSummaryDTO

High-level security compliance metrics:

• totalDevices - Total device count
• vulnerableDevices - Devices with at least one vulnerability
• totalVulnerabilities - Total vulnerability count
• criticalVulns, highVulns, mediumVulns, lowVulns - Counts by severity
• totalSoftware - Total unique software packages
• vulnerableSoftware - Software with CVEs
• lastUpdated - Timestamp of last vulnerability scan

TopVulnerabilityDTO

Critical vulnerability information:

• cveId - CVE identifier
• title - Vulnerability description
• severity - CRITICAL/HIGH/MEDIUM/LOW
• score - CVSS score
• affectedDevices - Number of affected devices

VulnerableSoftwareDTO

Vulnerable software metrics:

• softwareName - Software package name
• totalInstances - Total installations
• vulnerableInstances - Installations with CVEs
• totalCves - CVE count for this software

2. Repository Layer

Location: src/main/java/com/psg/dlsysinfo/dl_sysinfo_server/repository/ReportingRepository.java

Compliance Summary Queries (JPQL)

• countTotalDevices() - Count all devices for client
• countVulnerableDevices() - Count devices with vulnerabilities
• countTotalVulnerabilities() - Total vulnerability count
• countCriticalVulnerabilities() - CRITICAL severity count
• countHighVulnerabilities() - HIGH severity count
• countMediumVulnerabilities() - MEDIUM severity count
• countLowVulnerabilities() - LOW severity count
• countTotalSoftware() - Unique software package count
• countVulnerableSoftware() - Software with CVEs > 0
• findLastVulnerabilityScanDate() - Last scan timestamp

Advanced Queries (Native SQL)

• findTopVulnerabilitiesNative() - Top 20 vulnerabilities by severity and device count

  • Sorted: CRITICAL > HIGH > MEDIUM > LOW, then by affected device count
  • Returns: cveId, title, severity, score, affectedDevices

• findVulnerableSoftwareNative() - Top 20 vulnerable software by risk score

  • Risk formula: (vulnerableInstances / totalInstances) × totalCves
  • Returns: softwareName, totalInstances, vulnerableInstances, totalCves

3. Service Layer

Location: src/main/java/com/psg/dlsysinfo/dl_sysinfo_server/service/ReportingService.java

Methods with Caching

1. getComplianceSummary(clientId)

   • Cache: 15 minutes
   • Aggregates multiple queries into single DTO
   • Handles null values with defaults

2. getTopVulnerabilities(clientId)

   • Cache: 30 minutes (configurable in CacheConfig)
   • Maps native query results to DTOs
   • Returns top 20 vulnerabilities

3. getVulnerableSoftware(clientId)

   • Cache: 60 minutes (configurable in CacheConfig)
   • Maps native query results to DTOs
   • Returns top 20 vulnerable software packages

4. Controller Layer

Location: src/main/java/com/psg/dlsysinfo/dl_sysinfo_server/controller/ReportingController.java

Endpoints

GET /api/reporting/compliance-summary

• Authentication: Required (@PreAuthorize("isAuthenticated()"))
• Returns: ComplianceSummaryDTO
• Filters by authenticated user's client
• Error handling: Returns 500 with error details on failure

GET /api/reporting/top-vulnerabilities

• Authentication: Required
• Returns: List<TopVulnerabilityDTO>
• Filters by authenticated user's client
• Error handling: Returns 500 with error details on failure

GET /api/reporting/vulnerable-software

• Authentication: Required
• Returns: List<VulnerableSoftwareDTO>
• Filters by authenticated user's client
• Error handling: Returns 500 with error details on failure

5. Caching Configuration

Location: src/main/java/com/psg/dlsysinfo/dl_sysinfo_server/config/CacheConfig.java

• Provider: Caffeine Cache
• Configuration:
  • Maximum size: 1000 entries per cache
  • Default TTL: 15 minutes
  • Cache names: complianceSummary, topVulnerabilities, vulnerableSoftware

6. Build Dependencies

Location: build.gradle

Added dependencies:

implementation 'org.springframework.boot:spring-boot-starter-cache'
implementation 'com.github.ben-manes.caffeine:caffeine'

Data Flow

1. Request Flow:

   Client Request
   → Controller (authentication/authorization)
   → Service (cache check)
   → Repository (database query)
   → Service (DTO mapping)
   → Controller (response)
   

2. Security:

   • All endpoints require authentication via JWT
   • Client isolation: queries automatically filter by authenticated user's clientId
   • No cross-client data access possible

3. Performance Optimization:

   • Multi-tiered caching (15min/30min/60min)
   • Native SQL for complex queries with LIMIT
   • Database queries use indexed columns (client_id, device_id, severity)

Database Schema Requirements

Tables Used

1. devices - Device inventory (indexed on client_id)
2. cached_device_vulns - Device-vulnerability junction (indexed on device_id, severity)
3. cached_installed_software - Software inventory (indexed on device_id, total_cves)
4. clients - Client/organization data

Key Columns

• client_id - Organization identifier (FK in devices)
• device_id - Device identifier (FK in cached tables)
• severity - Vulnerability severity level
• total_cves - CVE count per software installation
• last_updated - Scan timestamp

Error Handling

All endpoints include try-catch blocks that:

• Log errors with slf4j
• Return HTTP 500 with JSON error response
• Format: {"error": "message", "code": 500}

Testing Recommendations

1. Unit Tests:

   • Test repository queries with H2 in-memory database
   • Mock service layer for controller tests
   • Verify DTO mapping from Object[] arrays

2. Integration Tests:

   • Test with realistic dataset (100+ devices)
   • Verify cache behavior
   • Test client isolation

3. Load Tests:

   • Verify performance under concurrent requests
   • Test cache effectiveness
   • Monitor query execution times

Cache Invalidation Strategy

Currently, cache TTL is time-based. For production, consider:

1. Event-based invalidation on vulnerability scan completion
2. Manual cache eviction endpoint for admins
3. Cache warming on application startup

Example cache eviction (for future implementation):

@CacheEvict(value = {"complianceSummary", "topVulnerabilities", "vulnerableSoftware"}, allEntries = true)
public void invalidateReportingCache() {
    log.info("Reporting cache invalidated");
}

Future Enhancements

1. Pagination: Add pagination support for top vulnerabilities and software lists
2. Date Ranges: Add time-based filtering for trend analysis
3. Export: Add CSV/PDF export functionality
4. Webhooks: Notify on critical vulnerability detection
5. Custom Thresholds: Allow clients to set custom severity thresholds
6. Audit Logging: Track all reporting API access for compliance

Deployment Notes

1. Build the project: ./gradlew build
2. Verify application starts without errors
3. Test endpoints with valid JWT token
4. Monitor cache hit rates in production
5. Adjust cache TTL based on scan frequency

API Usage Examples

Compliance Summary

curl -X GET https://your-domain:8443/api/reporting/compliance-summary \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

Top Vulnerabilities

curl -X GET https://your-domain:8443/api/reporting/top-vulnerabilities \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

Vulnerable Software

curl -X GET https://your-domain:8443/api/reporting/vulnerable-software \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"