Technical implementation guide using the actual Compliance Scorecard database schema with AI-powered evidence collection mapped to existing table structures.
Primary table for storing control evidence and AI validation results
Integration credentials for AI evidence collection sources
Maps internal clients to external system identifiers for evidence collection
Using actual database tables to store AI-collected evidence with human oversight workflows.
where('assessment_event_id', $assessmentEventId)
->where('control_id', 'SOC2_CC6.1')
->first();
// Get client's ConnectWise mapping
$cwMapping = DB::table('connector_client_mappings')
->where('client_id', $clientId)
->where('type', 'connectwise')
->first();
// Get API credentials
$apiKey = DB::table('api_keys')
->where('client_id', $clientId)
->where('type', 'connectwise')
->first();
// Collect user access data
$cwApi = new ConnectWiseAPI($apiKey);
$members = $cwApi->get("/company/companies/{$cwMapping->connector_client_id}/members");
// AI Analysis of user access controls
$aiAnalysis = $this->analyzeUserAccess($members);
// Store evidence in existing schema
DB::table('assessment_events_questions')
->where('id', $assessmentQuestion->id)
->update([
'control_evidence_source' => 'connectwise',
'control_evidence_location' => "/company/companies/{$cwMapping->connector_client_id}/members",
'evidence_location' => json_encode([
'total_users' => count($members),
'active_users' => $aiAnalysis['active_count'],
'privileged_users' => $aiAnalysis['privileged_count'],
'last_access_review' => $aiAnalysis['last_review_date'],
'compliance_score' => $aiAnalysis['score'],
'raw_data' => $members
]),
'evidence_location_type' => 'text',
'responsibility' => 'tool',
'selected_option_id' => $this->getOptionByScore($aiAnalysis['score']),
'auditor_notes' => $aiAnalysis['summary'],
'auditor_conformity_mark' => $aiAnalysis['score'] >= 85 ? 'met' : 'not met',
'question_notes' => $aiAnalysis['recommendations']
]);
return $aiAnalysis;
}
private function analyzeUserAccess($members)
{
$ai = new OpenAIAnalyzer();
$prompt = "Analyze this user access data for SOC 2 CC6.1 compliance:
- Review user privileges and access levels
- Check for segregation of duties
- Identify inactive accounts
- Assess access review processes
Data: " . json_encode($members);
$analysis = $ai->analyze($prompt);
return [
'active_count' => $this->countActiveUsers($members),
'privileged_count' => $this->countPrivilegedUsers($members),
'last_review_date' => $this->getLastReviewDate($members),
'score' => $this->calculateComplianceScore($analysis),
'summary' => $analysis['summary'],
'recommendations' => $analysis['recommendations'],
'risks' => $analysis['identified_risks']
];
}
}
?>where('assessment_event_id', $assessmentEventId)
->where('control_id', 'ISO27001_A.8.1.1')
->first();
// Collect from multiple sources in parallel
$assets = collect();
// ConnectWise - Hardware assets
if ($cwMapping = $this->getClientMapping($clientId, 'connectwise')) {
$cwAssets = $this->collectConnectWiseAssets($clientId, $cwMapping);
$assets = $assets->merge($this->tagAssets($cwAssets, 'hardware', 'connectwise'));
}
// Auvik - Network devices
if ($auvikMapping = $this->getClientMapping($clientId, 'auvik')) {
$networkAssets = $this->collectAuvikAssets($clientId, $auvikMapping);
$assets = $assets->merge($this->tagAssets($networkAssets, 'network', 'auvik'));
}
// Microsoft Graph - Software/licenses
if ($graphMapping = $this->getClientMapping($clientId, 'msgraph')) {
$softwareAssets = $this->collectGraphAssets($clientId, $graphMapping);
$assets = $assets->merge($this->tagAssets($softwareAssets, 'software', 'msgraph'));
}
// AI consolidation and analysis
$aiAnalysis = $this->performAIAssetAnalysis($assets->toArray());
// Update assessment question with consolidated evidence
DB::table('assessment_events_questions')
->where('id', $assessmentQuestion->id)
->update([
'control_evidence_source' => 'multiple', // connectwise,auvik,msgraph
'control_evidence_location' => json_encode([
'connectwise' => '/company/configurations',
'auvik' => '/v1/inventory/device/info',
'msgraph' => '/v1.0/organization/{id}/applications'
]),
'evidence_location' => json_encode([
'total_assets' => $assets->count(),
'asset_categories' => $aiAnalysis['categories'],
'coverage_score' => $aiAnalysis['coverage_score'],
'risk_assessment' => $aiAnalysis['risk_assessment'],
'consolidated_inventory' => $aiAnalysis['consolidated_assets'],
'collection_timestamp' => now()->toISOString()
]),
'evidence_location_type' => 'text',
'responsibility' => 'tool',
'selected_option_id' => $this->getOptionByScore($aiAnalysis['coverage_score']),
'auditor_notes' => $aiAnalysis['executive_summary'],
'auditor_conformity_mark' => $aiAnalysis['coverage_score'] >= 90 ? 'met' : 'not met',
'implementation_statement' => $this->generateImplementationStatement($aiAnalysis),
'question_notes' => implode("\n", $aiAnalysis['recommendations'])
]);
return $aiAnalysis;
}
private function performAIAssetAnalysis($assets)
{
$ai = new OpenAIAnalyzer();
return $ai->analyze([
'task' => 'asset_inventory_analysis',
'framework' => 'ISO27001_A.8.1.1',
'data' => $assets,
'requirements' => [
'completeness_check',
'categorization',
'risk_assessment',
'compliance_scoring',
'gap_identification'
]
]);
}
}
?>where('assessment_event_id', $assessmentEventId)
->where('control_id', 'NIST_CSF_RS.RP')
->first();
// Collect incident data from service desk
$incidents = $this->collectIncidentTickets($clientId, $startDate, $endDate);
// Collect security alerts
$securityAlerts = $this->collectSecurityAlerts($clientId, $startDate, $endDate);
// AI analysis of incident response capability
$aiAnalysis = $this->analyzeIncidentResponse([
'incidents' => $incidents,
'security_alerts' => $securityAlerts,
'time_period' => ['start' => $startDate, 'end' => $endDate]
]);
// Store comprehensive incident response evidence
DB::table('assessment_events_questions')
->where('id', $assessmentQuestion->id)
->update([
'control_evidence_source' => 'connectwise,huntress,cybercns',
'control_evidence_location' => json_encode([
'incidents' => '/service/tickets',
'security_alerts' => '/api/alerts',
'response_metrics' => '/api/metrics'
]),
'evidence_location' => json_encode([
'incident_count' => count($incidents),
'security_incident_count' => $aiAnalysis['security_incidents'],
'average_response_time' => $aiAnalysis['avg_response_time'],
'response_effectiveness' => $aiAnalysis['effectiveness_score'],
'process_maturity' => $aiAnalysis['maturity_level'],
'team_readiness' => $aiAnalysis['readiness_score'],
'detailed_analysis' => $aiAnalysis['detailed_breakdown']
]),
'evidence_location_type' => 'text',
'responsibility' => 'tool',
'selected_option_id' => $this->getOptionByScore($aiAnalysis['overall_score']),
'auditor_notes' => $aiAnalysis['executive_summary'],
'auditor_conformity_mark' => $aiAnalysis['overall_score'] >= 80 ? 'met' : 'not met',
'implementation_statement' => $this->generateNISTImplementationStatement($aiAnalysis),
'question_notes' => $this->formatRecommendations($aiAnalysis['recommendations'])
]);
return $aiAnalysis;
}
private function collectIncidentTickets($clientId, $startDate, $endDate)
{
$cwMapping = $this->getClientMapping($clientId, 'connectwise');
$apiKey = $this->getAPIKey($clientId, 'connectwise');
$cwApi = new ConnectWiseAPI($apiKey);
return $cwApi->get('/service/tickets', [
'conditions' => "company/id={$cwMapping->connector_client_id} AND " .
"dateEntered >= [{$startDate}] AND dateEntered <= [{$endDate}] AND " .
"(summary CONTAINS 'security' OR summary CONTAINS 'incident' OR " .
"summary CONTAINS 'breach' OR priority/name = 'Critical')",
'pageSize' => 1000
]);
}
private function analyzeIncidentResponse($data)
{
$ai = new OpenAIAnalyzer();
return $ai->analyze([
'framework' => 'NIST_CSF_RS.RP',
'task' => 'incident_response_analysis',
'data' => $data,
'analysis_requirements' => [
'response_time_analysis',
'process_effectiveness',
'team_readiness_assessment',
'process_maturity_evaluation',
'compliance_scoring'
]
]);
}
}
?>find($assessmentQuestionId);
// AI collects and analyzes evidence
$evidence = $this->collectEvidence($question);
$aiScore = $this->calculateConfidenceScore($evidence);
if ($aiScore >= 95) {
// High confidence - auto-approve
$this->updateQuestionWithEvidence($question, $evidence, 'auto_approved');
$this->logDecision($question, 'auto_approved', $aiScore);
} elseif ($aiScore >= 75) {
// Medium confidence - queue for review
$this->queueForHumanReview($question, $evidence, 'review_required');
$this->notifyReviewer($question, $evidence);
} else {
// Low confidence - require human collection
$this->flagForManualCollection($question, $evidence, 'manual_required');
$this->notifyComplianceTeam($question);
}
return $aiScore;
}
}
?>// Frontend human review interface
class EvidenceReviewInterface {
async loadPendingReviews() {
const reviews = await api.get('/evidence/pending-review');
reviews.forEach(review => {
this.renderReviewCard({
control: review.control_id,
aiScore: review.ai_confidence,
evidence: JSON.parse(review.evidence_location),
aiNotes: review.auditor_notes,
recommendation: review.ai_recommendation,
onApprove: () => this.approveEvidence(review.id),
onReject: () => this.rejectEvidence(review.id),
onModify: () => this.modifyEvidence(review.id)
});
});
}
async approveEvidence(questionId) {
await api.post(`/evidence/${questionId}/approve`, {
human_reviewer: currentUser.id,
review_decision: 'approved',
review_timestamp: new Date()
});
this.updateQuestionStatus(questionId, 'human_approved');
}
}