| 1 | import hashlib |
| 2 | import io |
| 3 | import mimetypes |
| 4 | import os |
| 5 | from datetime import datetime |
| 6 | from pathlib import Path |
| 7 | from typing import List |
| 8 | from typing import Optional |
| 9 | from typing import Tuple |
| 10 | |
| 11 | from fastapi import HTTPException |
| 12 | from fastapi import UploadFile |
| 13 | from loguru import logger |
| 14 | from sqlalchemy import asc |
| 15 | from sqlalchemy import delete |
| 16 | from sqlalchemy import desc |
| 17 | from sqlalchemy import distinct |
| 18 | from sqlalchemy import func |
| 19 | from sqlalchemy import update |
| 20 | from sqlalchemy.exc import IntegrityError |
| 21 | from sqlalchemy.ext.asyncio import AsyncSession |
| 22 | from sqlalchemy.future import select |
| 23 | from sqlalchemy.orm import selectinload |
| 24 | |
| 25 | from app.auth.models.users import User |
| 26 | from app.data_store.data_store_operations import delete_file |
| 27 | from app.data_store.data_store_operations import download_data_store |
| 28 | from app.data_store.data_store_operations import upload_case_data_store |
| 29 | from app.data_store.data_store_operations import upload_case_report_template_data_store |
| 30 | from app.data_store.data_store_schema import CaseDataStoreCreation |
| 31 | from app.data_store.data_store_schema import CaseReportTemplateDataStoreCreation |
| 32 | from app.incidents.middleware.tag_access import tag_access_handler |
| 33 | from app.incidents.models import AIAnalystTriggerEnabled |
| 34 | from app.incidents.models import Alert |
| 35 | from app.incidents.models import AlertContext |
| 36 | from app.incidents.models import AlertTag |
| 37 | from app.incidents.models import AlertTitleFieldName |
| 38 | from app.incidents.models import AlertToIoC |
| 39 | from app.incidents.models import AlertToTag |
| 40 | from app.incidents.models import Asset |
| 41 | from app.incidents.models import AssetFieldName |
| 42 | from app.incidents.models import Case |
| 43 | from app.incidents.models import CaseAlertLink |
| 44 | from app.incidents.models import CaseComment |
| 45 | from app.incidents.models import CaseDataStore |
| 46 | from app.incidents.models import CaseEvent |
| 47 | from app.incidents.models import CaseReportTemplateDataStore |
| 48 | from app.incidents.models import CaseTask |
| 49 | from app.incidents.models import Comment |
| 50 | from app.incidents.models import CustomerCodeFieldName |
| 51 | from app.incidents.models import FieldName |
| 52 | from app.incidents.models import IoC |
| 53 | from app.incidents.models import IoCFieldName |
| 54 | from app.incidents.models import Notification |
| 55 | from app.incidents.models import ThresholdAlertMetadata |
| 56 | from app.incidents.models import TimestampFieldName |
| 57 | from app.incidents.schema.db_operations import AlertContextCreate |
| 58 | from app.incidents.schema.db_operations import AlertCreate |
| 59 | from app.incidents.schema.db_operations import AlertIoCCreate |
| 60 | from app.incidents.schema.db_operations import AlertIoCDelete |
| 61 | from app.incidents.schema.db_operations import AlertOut |
| 62 | from app.incidents.schema.db_operations import AlertTagBase |
| 63 | from app.incidents.schema.db_operations import AlertTagCreate |
| 64 | from app.incidents.schema.db_operations import AssetBase |
| 65 | from app.incidents.schema.db_operations import AssetCreate |
| 66 | from app.incidents.schema.db_operations import CaseAlertLinkCreate |
| 67 | from app.incidents.schema.db_operations import CaseAlertLinksCreate |
| 68 | from app.incidents.schema.db_operations import CaseAlertUnLink |
| 69 | from app.incidents.schema.db_operations import CaseAlertUnLinkResponse |
| 70 | from app.incidents.schema.db_operations import CaseCommentBase |
| 71 | from app.incidents.schema.db_operations import CaseCommentCreate |
| 72 | from app.incidents.schema.db_operations import CaseCommentEdit |
| 73 | from app.incidents.schema.db_operations import CaseCreate |
| 74 | from app.incidents.schema.db_operations import CaseOut |
| 75 | from app.incidents.schema.db_operations import CaseReportTemplateDataStoreListResponse |
| 76 | from app.incidents.schema.db_operations import CommentBase |
| 77 | from app.incidents.schema.db_operations import CommentCreate |
| 78 | from app.incidents.schema.db_operations import CommentEdit |
| 79 | from app.incidents.schema.db_operations import IoCBase |
| 80 | from app.incidents.schema.db_operations import LinkedCaseCreate |
| 81 | from app.incidents.schema.db_operations import PutNotification |
| 82 | from app.incidents.schema.db_operations import UpdateAlertStatus |
| 83 | from app.incidents.schema.db_operations import UpdateCaseStatus |
| 84 | from app.integrations.alert_creation_settings.models.alert_creation_settings import ( |
| 85 | AlertCreationSettings, |
| 86 | ) |
| 87 | from app.middleware.customer_access import customer_access_handler |
| 88 | |
| 89 | |
| 90 | async def customer_code_valid(customer_code: str, db: AsyncSession) -> bool: |
| 91 | result = await db.execute(select(AlertCreationSettings).where(AlertCreationSettings.customer_code == customer_code)) |
| 92 | if result.scalars().first(): |
| 93 | return True |
| 94 | raise HTTPException(status_code=404, detail="Customer code not found") |
| 95 | |
| 96 | |
| 97 | async def alert_total(db: AsyncSession) -> int: |
| 98 | result = await db.execute(select(Alert)) |
| 99 | return len(result.scalars().all()) |
| 100 | |
| 101 | |
| 102 | async def alerts_closed(db: AsyncSession) -> int: |
| 103 | result = await db.execute(select(Alert).where(Alert.status == "CLOSED")) |
| 104 | return len(result.scalars().all()) |
| 105 | |
| 106 | |
| 107 | async def alerts_in_progress(db: AsyncSession) -> int: |
| 108 | result = await db.execute(select(Alert).where(Alert.status == "IN_PROGRESS")) |
| 109 | return len(result.scalars().all()) |
| 110 | |
| 111 | |
| 112 | async def alerts_open(db: AsyncSession) -> int: |
| 113 | result = await db.execute(select(Alert).where(Alert.status == "OPEN")) |
| 114 | return len(result.scalars().all()) |
| 115 | |
| 116 | |
| 117 | async def alert_total_by_assest_name(db: AsyncSession, asset_name: str) -> int: |
| 118 | result = await db.execute(select(Alert).join(Asset, Alert.id == Asset.alert_linked).where(Asset.asset_name == asset_name)) |
| 119 | return len(result.scalars().all()) |
| 120 | |
| 121 | |
| 122 | async def alerts_closed_by_asset_name(db: AsyncSession, asset_name: str) -> int: |
| 123 | result = await db.execute( |
| 124 | select(Alert).join(Asset, Alert.id == Asset.alert_linked).where((Alert.status == "CLOSED") & (Asset.asset_name == asset_name)), |
| 125 | ) |
| 126 | return len(result.scalars().all()) |
| 127 | |
| 128 | |
| 129 | async def alerts_in_progress_by_assest_name(db: AsyncSession, asset_name: str) -> int: |
| 130 | result = await db.execute( |
| 131 | select(Alert).join(Asset, Alert.id == Asset.alert_linked).where((Alert.status == "IN_PROGRESS") & (Asset.asset_name == asset_name)), |
| 132 | ) |
| 133 | return len(result.scalars().all()) |
| 134 | |
| 135 | |
| 136 | async def alerts_open_by_assest_name(db: AsyncSession, asset_name: str) -> int: |
| 137 | result = await db.execute( |
| 138 | select(Alert).join(Asset, Alert.id == Asset.alert_linked).where((Alert.status == "OPEN") & (Asset.asset_name == asset_name)), |
| 139 | ) |
| 140 | return len(result.scalars().all()) |
| 141 | |
| 142 | |
| 143 | async def alert_total_by_alert_title(db: AsyncSession, alert_title: str) -> int: |
| 144 | result = await db.execute(select(Alert).where(Alert.alert_name.like(f"%{alert_title}%"))) |
| 145 | return len(result.scalars().all()) |
| 146 | |
| 147 | |
| 148 | async def alerts_closed_by_alert_title(db: AsyncSession, alert_title: str) -> int: |
| 149 | result = await db.execute(select(Alert).where((Alert.status == "CLOSED") & (Alert.alert_name.like(f"%{alert_title}%")))) |
| 150 | return len(result.scalars().all()) |
| 151 | |
| 152 | |
| 153 | async def alerts_in_progress_by_alert_title(db: AsyncSession, alert_title: str) -> int: |
| 154 | result = await db.execute(select(Alert).where((Alert.status == "IN_PROGRESS") & (Alert.alert_name.like(f"%{alert_title}%")))) |
| 155 | return len(result.scalars().all()) |
| 156 | |
| 157 | |
| 158 | async def alerts_open_by_alert_title(db: AsyncSession, alert_title: str) -> int: |
| 159 | result = await db.execute(select(Alert).where((Alert.status == "OPEN") & (Alert.alert_name.like(f"%{alert_title}%")))) |
| 160 | return len(result.scalars().all()) |
| 161 | |
| 162 | |
| 163 | async def alerts_total_by_assigned_to(db: AsyncSession, assigned_to: str) -> int: |
| 164 | result = await db.execute(select(Alert).where(Alert.assigned_to == assigned_to)) |
| 165 | return len(result.scalars().all()) |
| 166 | |
| 167 | |
| 168 | async def alerts_closed_by_assigned_to(db: AsyncSession, assigned_to: str) -> int: |
| 169 | result = await db.execute(select(Alert).where((Alert.status == "CLOSED") & (Alert.assigned_to == assigned_to))) |
| 170 | return len(result.scalars().all()) |
| 171 | |
| 172 | |
| 173 | async def alerts_in_progress_by_assigned_to(db: AsyncSession, assigned_to: str) -> int: |
| 174 | result = await db.execute(select(Alert).where((Alert.status == "IN_PROGRESS") & (Alert.assigned_to == assigned_to))) |
| 175 | return len(result.scalars().all()) |
| 176 | |
| 177 | |
| 178 | async def alerts_open_by_assigned_to(db: AsyncSession, assigned_to: str) -> int: |
| 179 | result = await db.execute(select(Alert).where((Alert.status == "OPEN") & (Alert.assigned_to == assigned_to))) |
| 180 | return len(result.scalars().all()) |
| 181 | |
| 182 | |
| 183 | async def alerts_total_by_customer_code(db: AsyncSession, customer_code: str) -> int: |
| 184 | result = await db.execute(select(Alert).where(Alert.customer_code == customer_code)) |
| 185 | return len(result.scalars().all()) |
| 186 | |
| 187 | |
| 188 | async def alerts_closed_by_customer_code(db: AsyncSession, customer_code: str) -> int: |
| 189 | result = await db.execute(select(Alert).where((Alert.status == "CLOSED") & (Alert.customer_code == customer_code))) |
| 190 | return len(result.scalars().all()) |
| 191 | |
| 192 | |
| 193 | async def alerts_in_progress_by_customer_code(db: AsyncSession, customer_code: str) -> int: |
| 194 | result = await db.execute(select(Alert).where((Alert.status == "IN_PROGRESS") & (Alert.customer_code == customer_code))) |
| 195 | return len(result.scalars().all()) |
| 196 | |
| 197 | |
| 198 | async def alerts_open_by_customer_code(db: AsyncSession, customer_code: str) -> int: |
| 199 | result = await db.execute(select(Alert).where((Alert.status == "OPEN") & (Alert.customer_code == customer_code))) |
| 200 | return len(result.scalars().all()) |
| 201 | |
| 202 | |
| 203 | async def alerts_total_by_source(db: AsyncSession, source: str) -> int: |
| 204 | result = await db.execute(select(Alert).where(Alert.source == source)) |
| 205 | return len(result.scalars().all()) |
| 206 | |
| 207 | |
| 208 | async def alerts_closed_by_source(db: AsyncSession, source: str) -> int: |
| 209 | result = await db.execute(select(Alert).where((Alert.status == "CLOSED") & (Alert.source == source))) |
| 210 | return len(result.scalars().all()) |
| 211 | |
| 212 | |
| 213 | async def alerts_in_progress_by_source(db: AsyncSession, source: str) -> int: |
| 214 | result = await db.execute(select(Alert).where((Alert.status == "IN_PROGRESS") & (Alert.source == source))) |
| 215 | return len(result.scalars().all()) |
| 216 | |
| 217 | |
| 218 | async def alerts_open_by_source(db: AsyncSession, source: str) -> int: |
| 219 | result = await db.execute(select(Alert).where((Alert.status == "OPEN") & (Alert.source == source))) |
| 220 | return len(result.scalars().all()) |
| 221 | |
| 222 | |
| 223 | async def alert_total_by_customer_codes(db: AsyncSession, customer_codes: List[str]) -> int: |
| 224 | """Get total alerts for multiple customer codes""" |
| 225 | result = await db.execute(select(Alert).where(Alert.customer_code.in_(customer_codes))) |
| 226 | return len(result.scalars().all()) |
| 227 | |
| 228 | |
| 229 | async def alerts_closed_by_customer_codes(db: AsyncSession, customer_codes: List[str]) -> int: |
| 230 | """Get closed alerts for multiple customer codes""" |
| 231 | result = await db.execute(select(Alert).where((Alert.status == "CLOSED") & (Alert.customer_code.in_(customer_codes)))) |
| 232 | return len(result.scalars().all()) |
| 233 | |
| 234 | |
| 235 | async def alerts_in_progress_by_customer_codes(db: AsyncSession, customer_codes: List[str]) -> int: |
| 236 | """Get in-progress alerts for multiple customer codes""" |
| 237 | result = await db.execute(select(Alert).where((Alert.status == "IN_PROGRESS") & (Alert.customer_code.in_(customer_codes)))) |
| 238 | return len(result.scalars().all()) |
| 239 | |
| 240 | |
| 241 | async def alerts_open_by_customer_codes(db: AsyncSession, customer_codes: List[str]) -> int: |
| 242 | """Get open alerts for multiple customer codes""" |
| 243 | result = await db.execute(select(Alert).where((Alert.status == "OPEN") & (Alert.customer_code.in_(customer_codes)))) |
| 244 | return len(result.scalars().all()) |
| 245 | |
| 246 | |
| 247 | async def alert_total_for_user(user: User, db: AsyncSession, customer_codes: Optional[List[str]] = None) -> int: |
| 248 | """Get total alerts count with customer and tag filtering""" |
| 249 | from sqlalchemy import and_ |
| 250 | from sqlalchemy import exists |
| 251 | from sqlalchemy import or_ |
| 252 | |
| 253 | filters = [] |
| 254 | |
| 255 | # Customer filtering |
| 256 | accessible_customers = await customer_access_handler.resolve_effective_customers(user, customer_codes, db) |
| 257 | if "*" not in accessible_customers: |
| 258 | filters.append(Alert.customer_code.in_(accessible_customers)) |
| 259 | |
| 260 | # Tag filtering |
| 261 | tag_filters = await tag_access_handler.build_alert_query_filters(user, db) |
| 262 | accessible_tags = tag_filters["accessible_tags"] |
| 263 | |
| 264 | if "*" not in accessible_tags: |
| 265 | tag_conditions = [] |
| 266 | if accessible_tags: |
| 267 | has_accessible_tag = exists( |
| 268 | select(AlertToTag.alert_id).where( |
| 269 | and_( |
| 270 | AlertToTag.alert_id == Alert.id, |
| 271 | AlertToTag.tag_id.in_(accessible_tags), |
| 272 | ), |
| 273 | ), |
| 274 | ) |
| 275 | tag_conditions.append(has_accessible_tag) |
| 276 | |
| 277 | if tag_filters["include_untagged"]: |
| 278 | is_untagged = ~exists( |
| 279 | select(AlertToTag.alert_id).where(AlertToTag.alert_id == Alert.id), |
| 280 | ) |
| 281 | tag_conditions.append(is_untagged) |
| 282 | |
| 283 | if tag_conditions: |
| 284 | filters.append(or_(*tag_conditions)) |
| 285 | else: |
| 286 | return 0 |
| 287 | |
| 288 | query = select(func.count(Alert.id)).where(*filters) if filters else select(func.count(Alert.id)) |
| 289 | result = await db.execute(query) |
| 290 | return result.scalar_one() |
| 291 | |
| 292 | |
| 293 | async def alerts_open_for_user(user: User, db: AsyncSession, customer_codes: Optional[List[str]] = None) -> int: |
| 294 | """Get open alerts count with customer and tag filtering""" |
| 295 | from sqlalchemy import and_ |
| 296 | from sqlalchemy import exists |
| 297 | from sqlalchemy import or_ |
| 298 | |
| 299 | filters = [Alert.status == "OPEN"] |
| 300 | |
| 301 | # Customer filtering |
| 302 | accessible_customers = await customer_access_handler.resolve_effective_customers(user, customer_codes, db) |
| 303 | if "*" not in accessible_customers: |
| 304 | filters.append(Alert.customer_code.in_(accessible_customers)) |
| 305 | |
| 306 | # Tag filtering |
| 307 | tag_filters = await tag_access_handler.build_alert_query_filters(user, db) |
| 308 | accessible_tags = tag_filters["accessible_tags"] |
| 309 | |
| 310 | if "*" not in accessible_tags: |
| 311 | tag_conditions = [] |
| 312 | if accessible_tags: |
| 313 | has_accessible_tag = exists( |
| 314 | select(AlertToTag.alert_id).where( |
| 315 | and_( |
| 316 | AlertToTag.alert_id == Alert.id, |
| 317 | AlertToTag.tag_id.in_(accessible_tags), |
| 318 | ), |
| 319 | ), |
| 320 | ) |
| 321 | tag_conditions.append(has_accessible_tag) |
| 322 | |
| 323 | if tag_filters["include_untagged"]: |
| 324 | is_untagged = ~exists( |
| 325 | select(AlertToTag.alert_id).where(AlertToTag.alert_id == Alert.id), |
| 326 | ) |
| 327 | tag_conditions.append(is_untagged) |
| 328 | |
| 329 | if tag_conditions: |
| 330 | filters.append(or_(*tag_conditions)) |
| 331 | else: |
| 332 | return 0 |
| 333 | |
| 334 | query = select(func.count(Alert.id)).where(*filters) |
| 335 | result = await db.execute(query) |
| 336 | return result.scalar_one() |
| 337 | |
| 338 | |
| 339 | async def alerts_in_progress_for_user(user: User, db: AsyncSession, customer_codes: Optional[List[str]] = None) -> int: |
| 340 | """Get in-progress alerts count with customer and tag filtering""" |
| 341 | from sqlalchemy import and_ |
| 342 | from sqlalchemy import exists |
| 343 | from sqlalchemy import or_ |
| 344 | |
| 345 | filters = [Alert.status == "IN_PROGRESS"] |
| 346 | |
| 347 | # Customer filtering |
| 348 | accessible_customers = await customer_access_handler.resolve_effective_customers(user, customer_codes, db) |
| 349 | if "*" not in accessible_customers: |
| 350 | filters.append(Alert.customer_code.in_(accessible_customers)) |
| 351 | |
| 352 | # Tag filtering |
| 353 | tag_filters = await tag_access_handler.build_alert_query_filters(user, db) |
| 354 | accessible_tags = tag_filters["accessible_tags"] |
| 355 | |
| 356 | if "*" not in accessible_tags: |
| 357 | tag_conditions = [] |
| 358 | if accessible_tags: |
| 359 | has_accessible_tag = exists( |
| 360 | select(AlertToTag.alert_id).where( |
| 361 | and_( |
| 362 | AlertToTag.alert_id == Alert.id, |
| 363 | AlertToTag.tag_id.in_(accessible_tags), |
| 364 | ), |
| 365 | ), |
| 366 | ) |
| 367 | tag_conditions.append(has_accessible_tag) |
| 368 | |
| 369 | if tag_filters["include_untagged"]: |
| 370 | is_untagged = ~exists( |
| 371 | select(AlertToTag.alert_id).where(AlertToTag.alert_id == Alert.id), |
| 372 | ) |
| 373 | tag_conditions.append(is_untagged) |
| 374 | |
| 375 | if tag_conditions: |
| 376 | filters.append(or_(*tag_conditions)) |
| 377 | else: |
| 378 | return 0 |
| 379 | |
| 380 | query = select(func.count(Alert.id)).where(*filters) |
| 381 | result = await db.execute(query) |
| 382 | return result.scalar_one() |
| 383 | |
| 384 | |
| 385 | async def alerts_closed_for_user(user: User, db: AsyncSession, customer_codes: Optional[List[str]] = None) -> int: |
| 386 | """Get closed alerts count with customer and tag filtering""" |
| 387 | from sqlalchemy import and_ |
| 388 | from sqlalchemy import exists |
| 389 | from sqlalchemy import or_ |
| 390 | |
| 391 | filters = [Alert.status == "CLOSED"] |
| 392 | |
| 393 | # Customer filtering |
| 394 | accessible_customers = await customer_access_handler.resolve_effective_customers(user, customer_codes, db) |
| 395 | if "*" not in accessible_customers: |
| 396 | filters.append(Alert.customer_code.in_(accessible_customers)) |
| 397 | |
| 398 | # Tag filtering |
| 399 | tag_filters = await tag_access_handler.build_alert_query_filters(user, db) |
| 400 | accessible_tags = tag_filters["accessible_tags"] |
| 401 | |
| 402 | if "*" not in accessible_tags: |
| 403 | tag_conditions = [] |
| 404 | if accessible_tags: |
| 405 | has_accessible_tag = exists( |
| 406 | select(AlertToTag.alert_id).where( |
| 407 | and_( |
| 408 | AlertToTag.alert_id == Alert.id, |
| 409 | AlertToTag.tag_id.in_(accessible_tags), |
| 410 | ), |
| 411 | ), |
| 412 | ) |
| 413 | tag_conditions.append(has_accessible_tag) |
| 414 | |
| 415 | if tag_filters["include_untagged"]: |
| 416 | is_untagged = ~exists( |
| 417 | select(AlertToTag.alert_id).where(AlertToTag.alert_id == Alert.id), |
| 418 | ) |
| 419 | tag_conditions.append(is_untagged) |
| 420 | |
| 421 | if tag_conditions: |
| 422 | filters.append(or_(*tag_conditions)) |
| 423 | else: |
| 424 | return 0 |
| 425 | |
| 426 | query = select(func.count(Alert.id)).where(*filters) |
| 427 | result = await db.execute(query) |
| 428 | return result.scalar_one() |
| 429 | |
| 430 | |
| 431 | async def alerts_total_multiple_filters( |
| 432 | db: AsyncSession, |
| 433 | assigned_to: Optional[str] = None, |
| 434 | alert_title: Optional[str] = None, |
| 435 | customer_code: Optional[str] = None, |
| 436 | source: Optional[str] = None, |
| 437 | asset_name: Optional[str] = None, |
| 438 | status: Optional[str] = None, |
| 439 | tags: Optional[List[str]] = None, |
| 440 | ioc_value: Optional[str] = None, |
| 441 | ) -> int: |
| 442 | # Build dynamic filters |
| 443 | filters = [] |
| 444 | if assigned_to: |
| 445 | filters.append(Alert.assigned_to == assigned_to) |
| 446 | if alert_title: |
| 447 | filters.append(Alert.alert_name.like(f"%{alert_title}%")) |
| 448 | if customer_code: |
| 449 | filters.append(Alert.customer_code == customer_code) |
| 450 | if source: |
| 451 | filters.append(Alert.source == source) |
| 452 | if asset_name: |
| 453 | filters.append(Asset.asset_name == asset_name) |
| 454 | if status: |
| 455 | filters.append(Alert.status == status) |
| 456 | if tags: |
| 457 | filters.append(AlertTag.tag.in_(tags)) |
| 458 | if ioc_value: |
| 459 | filters.append(IoC.value == ioc_value) |
| 460 | |
| 461 | # Build the query with dynamic filters |
| 462 | query = ( |
| 463 | select(func.count(distinct(Alert.id))) |
| 464 | .select_from(Alert) |
| 465 | .join(Asset, Asset.alert_linked == Alert.id, isouter=True) # Join with Asset table |
| 466 | .join(AlertToTag, AlertToTag.alert_id == Alert.id, isouter=True) # Join with AlertToTag table |
| 467 | .join(AlertTag, AlertToTag.tag_id == AlertTag.id, isouter=True) # Join with AlertTag table |
| 468 | .join(AlertToIoC, AlertToIoC.alert_id == Alert.id, isouter=True) # Join with AlertToIoC table |
| 469 | .join(IoC, AlertToIoC.ioc_id == IoC.id, isouter=True) # Join with IoC table |
| 470 | .where(*filters) |
| 471 | ) |
| 472 | |
| 473 | result = await db.execute(query) |
| 474 | total = result.scalar_one() |
| 475 | return total |
| 476 | |
| 477 | |
| 478 | async def alerts_closed_multiple_filters( |
| 479 | db: AsyncSession, |
| 480 | assigned_to: Optional[str] = None, |
| 481 | alert_title: Optional[str] = None, |
| 482 | customer_code: Optional[str] = None, |
| 483 | source: Optional[str] = None, |
| 484 | asset_name: Optional[str] = None, |
| 485 | status: Optional[str] = None, |
| 486 | tags: Optional[List[str]] = None, |
| 487 | ioc_value: Optional[str] = None, |
| 488 | ) -> int: |
| 489 | # Include the status filter |
| 490 | filters = [Alert.status == "CLOSED"] |
| 491 | if assigned_to: |
| 492 | filters.append(Alert.assigned_to == assigned_to) |
| 493 | if alert_title: |
| 494 | filters.append(Alert.alert_name.like(f"%{alert_title}%")) |
| 495 | if customer_code: |
| 496 | filters.append(Alert.customer_code == customer_code) |
| 497 | if source: |
| 498 | filters.append(Alert.source == source) |
| 499 | if asset_name: |
| 500 | filters.append(Asset.asset_name == asset_name) |
| 501 | if status: |
| 502 | filters.append(Alert.status == status) |
| 503 | if tags: |
| 504 | filters.append(AlertTag.tag.in_(tags)) |
| 505 | if ioc_value: |
| 506 | filters.append(IoC.value == ioc_value) |
| 507 | |
| 508 | # Build the query with dynamic filters |
| 509 | query = ( |
| 510 | select(func.count(distinct(Alert.id))) |
| 511 | .select_from(Alert) |
| 512 | .join(Asset, Asset.alert_linked == Alert.id, isouter=True) # Join with Asset table |
| 513 | .join(AlertToTag, AlertToTag.alert_id == Alert.id, isouter=True) # Join with AlertToTag table |
| 514 | .join(AlertTag, AlertToTag.tag_id == AlertTag.id, isouter=True) # Join with AlertTag table |
| 515 | .join(AlertToIoC, AlertToIoC.alert_id == Alert.id, isouter=True) # Join with AlertToIoC table |
| 516 | .join(IoC, AlertToIoC.ioc_id == IoC.id, isouter=True) # Join with IoC table |
| 517 | .where(*filters) |
| 518 | ) |
| 519 | |
| 520 | result = await db.execute(query) |
| 521 | closed_count = result.scalar_one() |
| 522 | return closed_count |
| 523 | |
| 524 | |
| 525 | async def alerts_in_progress_multiple_filters( |
| 526 | db: AsyncSession, |
| 527 | assigned_to: Optional[str] = None, |
| 528 | alert_title: Optional[str] = None, |
| 529 | customer_code: Optional[str] = None, |
| 530 | source: Optional[str] = None, |
| 531 | asset_name: Optional[str] = None, |
| 532 | status: Optional[str] = None, |
| 533 | tags: Optional[List[str]] = None, |
| 534 | ioc_value: Optional[str] = None, |
| 535 | ) -> int: |
| 536 | filters = [Alert.status == "IN_PROGRESS"] |
| 537 | if assigned_to: |
| 538 | filters.append(Alert.assigned_to == assigned_to) |
| 539 | if alert_title: |
| 540 | filters.append(Alert.alert_name.like(f"%{alert_title}%")) |
| 541 | if customer_code: |
| 542 | filters.append(Alert.customer_code == customer_code) |
| 543 | if source: |
| 544 | filters.append(Alert.source == source) |
| 545 | if asset_name: |
| 546 | filters.append(Asset.asset_name == asset_name) |
| 547 | if status: |
| 548 | filters.append(Alert.status == status) |
| 549 | if tags: |
| 550 | filters.append(AlertTag.tag.in_(tags)) |
| 551 | if ioc_value: |
| 552 | filters.append(IoC.value == ioc_value) |
| 553 | |
| 554 | query = ( |
| 555 | select(func.count(distinct(Alert.id))) |
| 556 | .select_from(Alert) |
| 557 | .join(Asset, Asset.alert_linked == Alert.id, isouter=True) # Join with Asset table |
| 558 | .join(AlertToTag, AlertToTag.alert_id == Alert.id, isouter=True) # Join with AlertToTag table |
| 559 | .join(AlertTag, AlertToTag.tag_id == AlertTag.id, isouter=True) # Join with AlertTag table |
| 560 | .join(AlertToIoC, AlertToIoC.alert_id == Alert.id, isouter=True) # Join with AlertToIoC table |
| 561 | .join(IoC, AlertToIoC.ioc_id == IoC.id, isouter=True) # Join with IoC table |
| 562 | .where(*filters) |
| 563 | ) |
| 564 | |
| 565 | result = await db.execute(query) |
| 566 | in_progress_count = result.scalar_one() |
| 567 | return in_progress_count |
| 568 | |
| 569 | |
| 570 | async def alerts_open_multiple_filters( |
| 571 | db: AsyncSession, |
| 572 | assigned_to: Optional[str] = None, |
| 573 | alert_title: Optional[str] = None, |
| 574 | customer_code: Optional[str] = None, |
| 575 | source: Optional[str] = None, |
| 576 | asset_name: Optional[str] = None, |
| 577 | status: Optional[str] = None, |
| 578 | tags: Optional[List[str]] = None, |
| 579 | ioc_value: Optional[str] = None, |
| 580 | ) -> int: |
| 581 | filters = [Alert.status == "OPEN"] |
| 582 | if assigned_to: |
| 583 | filters.append(Alert.assigned_to == assigned_to) |
| 584 | if alert_title: |
| 585 | filters.append(Alert.alert_name.like(f"%{alert_title}%")) |
| 586 | if customer_code: |
| 587 | filters.append(Alert.customer_code == customer_code) |
| 588 | if source: |
| 589 | filters.append(Alert.source == source) |
| 590 | if asset_name: |
| 591 | filters.append(Asset.asset_name == asset_name) |
| 592 | if status: |
| 593 | filters.append(Alert.status == status) |
| 594 | if tags: |
| 595 | filters.append(AlertTag.tag.in_(tags)) |
| 596 | if ioc_value: |
| 597 | filters.append(IoC.value == ioc_value) |
| 598 | |
| 599 | query = ( |
| 600 | select(func.count(distinct(Alert.id))) |
| 601 | .select_from(Alert) |
| 602 | .join(Asset, Asset.alert_linked == Alert.id, isouter=True) # Join with Asset table |
| 603 | .join(AlertToTag, AlertToTag.alert_id == Alert.id, isouter=True) # Join with AlertToTag table |
| 604 | .join(AlertTag, AlertToTag.tag_id == AlertTag.id, isouter=True) # Join with AlertTag table |
| 605 | .join(AlertToIoC, AlertToIoC.alert_id == Alert.id, isouter=True) # Join with AlertToIoC table |
| 606 | .join(IoC, AlertToIoC.ioc_id == IoC.id, isouter=True) # Join with IoC table |
| 607 | .where(*filters) |
| 608 | ) |
| 609 | |
| 610 | result = await db.execute(query) |
| 611 | open_count = result.scalar_one() |
| 612 | return open_count |
| 613 | |
| 614 | |
| 615 | async def alerts_total_by_ioc(db: AsyncSession, ioc_value: str) -> int: |
| 616 | result = await db.execute( |
| 617 | select(Alert) |
| 618 | .join(AlertToIoC, Alert.id == AlertToIoC.alert_id) |
| 619 | .join(IoC, AlertToIoC.ioc_id == IoC.id) |
| 620 | .where(IoC.value == ioc_value), |
| 621 | ) |
| 622 | return len(result.scalars().all()) |
| 623 | |
| 624 | |
| 625 | async def alerts_closed_by_ioc(db: AsyncSession, ioc_value: str) -> int: |
| 626 | result = await db.execute( |
| 627 | select(Alert) |
| 628 | .join(AlertToIoC, Alert.id == AlertToIoC.alert_id) |
| 629 | .join(IoC, AlertToIoC.ioc_id == IoC.id) |
| 630 | .where((Alert.status == "CLOSED") & (IoC.value == ioc_value)), |
| 631 | ) |
| 632 | return len(result.scalars().all()) |
| 633 | |
| 634 | |
| 635 | async def alerts_in_progress_by_ioc(db: AsyncSession, ioc_value: str) -> int: |
| 636 | result = await db.execute( |
| 637 | select(Alert) |
| 638 | .join(AlertToIoC, Alert.id == AlertToIoC.alert_id) |
| 639 | .join(IoC, AlertToIoC.ioc_id == IoC.id) |
| 640 | .where((Alert.status == "IN_PROGRESS") & (IoC.value == ioc_value)), |
| 641 | ) |
| 642 | return len(result.scalars().all()) |
| 643 | |
| 644 | |
| 645 | async def alerts_open_by_ioc(db: AsyncSession, ioc_value: str) -> int: |
| 646 | result = await db.execute( |
| 647 | select(Alert) |
| 648 | .join(AlertToIoC, Alert.id == AlertToIoC.alert_id) |
| 649 | .join(IoC, AlertToIoC.ioc_id == IoC.id) |
| 650 | .where((Alert.status == "OPEN") & (IoC.value == ioc_value)), |
| 651 | ) |
| 652 | return len(result.scalars().all()) |
| 653 | |
| 654 | |
| 655 | async def alerts_total_by_tag(db: AsyncSession, tag: str) -> int: |
| 656 | result = await db.execute( |
| 657 | select(Alert) |
| 658 | .join(AlertToTag, Alert.id == AlertToTag.alert_id) |
| 659 | .join(AlertTag, AlertToTag.tag_id == AlertTag.id) |
| 660 | .where(AlertTag.tag == tag), |
| 661 | ) |
| 662 | return len(result.scalars().all()) |
| 663 | |
| 664 | |
| 665 | async def alerts_closed_by_tag(db: AsyncSession, tag: str) -> int: |
| 666 | result = await db.execute( |
| 667 | select(Alert) |
| 668 | .join(AlertToTag, Alert.id == AlertToTag.alert_id) |
| 669 | .join(AlertTag, AlertToTag.tag_id == AlertTag.id) |
| 670 | .where((Alert.status == "CLOSED") & (AlertTag.tag == tag)), |
| 671 | ) |
| 672 | return len(result.scalars().all()) |
| 673 | |
| 674 | |
| 675 | async def alerts_in_progress_by_tag(db: AsyncSession, tag: str) -> int: |
| 676 | result = await db.execute( |
| 677 | select(Alert) |
| 678 | .join(AlertToTag, Alert.id == AlertToTag.alert_id) |
| 679 | .join(AlertTag, AlertToTag.tag_id == AlertTag.id) |
| 680 | .where((Alert.status == "IN_PROGRESS") & (AlertTag.tag == tag)), |
| 681 | ) |
| 682 | return len(result.scalars().all()) |
| 683 | |
| 684 | |
| 685 | async def alerts_open_by_tag(db: AsyncSession, tag: str) -> int: |
| 686 | result = await db.execute( |
| 687 | select(Alert) |
| 688 | .join(AlertToTag, Alert.id == AlertToTag.alert_id) |
| 689 | .join(AlertTag, AlertToTag.tag_id == AlertTag.id) |
| 690 | .where((Alert.status == "OPEN") & (AlertTag.tag == tag)), |
| 691 | ) |
| 692 | return len(result.scalars().all()) |
| 693 | |
| 694 | |
| 695 | async def validate_source_exists(source: str, session: AsyncSession): |
| 696 | # Check each of the FieldName tables and ensure each contains at least one entry for the source |
| 697 | field_names = await get_field_names(source, session) |
| 698 | asset_names = await get_asset_names(source, session) |
| 699 | timefield_names = await get_timefield_names(source, session) |
| 700 | alert_title_names = await get_alert_title_names(source, session) |
| 701 | |
| 702 | if not field_names or not asset_names or not timefield_names or not alert_title_names: |
| 703 | raise HTTPException(status_code=400, detail="Source does not exist") |
| 704 | |
| 705 | |
| 706 | async def get_field_names(source: str, session: AsyncSession): |
| 707 | result = await session.execute(select(FieldName.field_name).where(FieldName.source == source).distinct()) |
| 708 | return result.scalars().all() |
| 709 | |
| 710 | |
| 711 | async def get_asset_names(source: str, session: AsyncSession): |
| 712 | result = await session.execute(select(AssetFieldName.field_name).where(AssetFieldName.source == source).distinct()) |
| 713 | return result.scalars().first() |
| 714 | |
| 715 | |
| 716 | async def get_timefield_names(source: str, session: AsyncSession): |
| 717 | result = await session.execute(select(TimestampFieldName.field_name).where(TimestampFieldName.source == source).distinct()) |
| 718 | return result.scalars().first() |
| 719 | |
| 720 | |
| 721 | async def get_ioc_names(source: str, session: AsyncSession): |
| 722 | result = await session.execute(select(IoCFieldName.field_name).where(IoCFieldName.source == source).distinct()) |
| 723 | return result.scalars().all() |
| 724 | |
| 725 | |
| 726 | async def get_alert_title_names(source: str, session: AsyncSession): |
| 727 | result = await session.execute(select(AlertTitleFieldName.field_name).where(AlertTitleFieldName.source == source).distinct()) |
| 728 | return result.scalars().first() |
| 729 | |
| 730 | |
| 731 | # ! NOT USING FOR NOW. GETTING THE CUSTOMER CODE FROM THE ALERTS SOURCE FIELD INSTEAD ! # |
| 732 | async def get_customer_code_names(source: str, session: AsyncSession): |
| 733 | result = await session.execute(select(CustomerCodeFieldName.field_name).where(CustomerCodeFieldName.source == source).distinct()) |
| 734 | return result.scalars().first() |
| 735 | |
| 736 | |
| 737 | async def get_customer_ai_trigger(customer_code: str, session: AsyncSession): |
| 738 | result = await session.execute(select(AIAnalystTriggerEnabled).where(AIAnalystTriggerEnabled.customer_code == customer_code)) |
| 739 | notification = result.scalars().first() |
| 740 | logger.info(f"AI Notification: {notification}") |
| 741 | return [notification] if notification is not None else [] |
| 742 | |
| 743 | |
| 744 | async def put_customer_ai_trigger(notification: PutNotification, session: AsyncSession): |
| 745 | result = await session.execute( |
| 746 | select(AIAnalystTriggerEnabled).where(AIAnalystTriggerEnabled.customer_code == notification.customer_code), |
| 747 | ) |
| 748 | existing_notification = result.scalars().first() |
| 749 | if existing_notification is None: |
| 750 | new_notification = AIAnalystTriggerEnabled(**notification.model_dump()) |
| 751 | session.add(new_notification) |
| 752 | else: |
| 753 | existing_notification.customer_code = notification.customer_code |
| 754 | existing_notification.enabled = notification.enabled |
| 755 | await session.commit() |
| 756 | |
| 757 | |
| 758 | async def get_customer_notification(customer_code: str, session: AsyncSession): |
| 759 | result = await session.execute(select(Notification).where(Notification.customer_code == customer_code)) |
| 760 | notification = result.scalars().first() |
| 761 | logger.info(f"Notification: {notification}") |
| 762 | return [notification] if notification is not None else [] |
| 763 | |
| 764 | |
| 765 | async def put_customer_notification(notification: PutNotification, session: AsyncSession): |
| 766 | result = await session.execute(select(Notification).where(Notification.customer_code == notification.customer_code)) |
| 767 | existing_notification = result.scalars().first() |
| 768 | if existing_notification is None: |
| 769 | new_notification = Notification(**notification.model_dump()) |
| 770 | session.add(new_notification) |
| 771 | else: |
| 772 | existing_notification.customer_code = notification.customer_code |
| 773 | existing_notification.shuffle_workflow_id = notification.shuffle_workflow_id |
| 774 | existing_notification.enabled = notification.enabled |
| 775 | await session.commit() |
| 776 | |
| 777 | |
| 778 | async def add_field_name(source: str, field_name: str, session: AsyncSession): |
| 779 | result = await session.execute(select(FieldName).where((FieldName.source == source) & (FieldName.field_name == field_name))) |
| 780 | existing_field = result.scalars().first() |
| 781 | if existing_field is None: |
| 782 | field = FieldName(source=source, field_name=field_name) |
| 783 | session.add(field) |
| 784 | |
| 785 | |
| 786 | async def add_asset_name(source: str, asset_name: str, session: AsyncSession): |
| 787 | result = await session.execute( |
| 788 | select(AssetFieldName).where((AssetFieldName.source == source) & (AssetFieldName.field_name == asset_name)), |
| 789 | ) |
| 790 | existing_asset = result.scalars().first() |
| 791 | if existing_asset is None: |
| 792 | asset = AssetFieldName(source=source, field_name=asset_name) |
| 793 | session.add(asset) |
| 794 | |
| 795 | |
| 796 | async def add_timefield_name(source: str, timefield_name: str, session: AsyncSession): |
| 797 | result = await session.execute( |
| 798 | select(TimestampFieldName).where((TimestampFieldName.source == source) & (TimestampFieldName.field_name == timefield_name)), |
| 799 | ) |
| 800 | existing_timefield = result.scalars().first() |
| 801 | if existing_timefield is None: |
| 802 | timefield = TimestampFieldName(source=source, field_name=timefield_name) |
| 803 | session.add(timefield) |
| 804 | |
| 805 | |
| 806 | async def add_alert_title_name(source: str, alert_title_name: str, session: AsyncSession): |
| 807 | result = await session.execute( |
| 808 | select(AlertTitleFieldName).where((AlertTitleFieldName.source == source) & (AlertTitleFieldName.field_name == alert_title_name)), |
| 809 | ) |
| 810 | existing_alert_title = result.scalars().first() |
| 811 | if existing_alert_title is None: |
| 812 | alert_title = AlertTitleFieldName(source=source, field_name=alert_title_name) |
| 813 | session.add(alert_title) |
| 814 | |
| 815 | |
| 816 | async def add_ioc_name(source: str, ioc_name: str, session: AsyncSession): |
| 817 | result = await session.execute( |
| 818 | select(IoCFieldName).where((IoCFieldName.source == source) & (IoCFieldName.field_name == ioc_name)), |
| 819 | ) |
| 820 | existing_ioc = result.scalars().first() |
| 821 | if existing_ioc is None: |
| 822 | ioc = IoCFieldName(source=source, field_name=ioc_name) |
| 823 | session.add(ioc) |
| 824 | |
| 825 | |
| 826 | # ! NOT USING FOR NOW. GETTING THE CUSTOMER CODE FROM THE ALERTS SOURCE FIELD INSTEAD ! # |
| 827 | async def add_customer_code_name(source: str, customer_code_name: str, session: AsyncSession): |
| 828 | result = await session.execute( |
| 829 | select(CustomerCodeFieldName).where( |
| 830 | (CustomerCodeFieldName.source == source) & (CustomerCodeFieldName.field_name == customer_code_name), |
| 831 | ), |
| 832 | ) |
| 833 | existing_customer_code = result.scalars().first() |
| 834 | if existing_customer_code is None: |
| 835 | customer_code = CustomerCodeFieldName(source=source, field_name=customer_code_name) |
| 836 | session.add(customer_code) |
| 837 | |
| 838 | |
| 839 | async def replace_field_name(source: str, field_names: List[str], session: AsyncSession): |
| 840 | # First delete all the field names for this source, then add the new field names |
| 841 | result = await session.execute(select(FieldName).where(FieldName.source == source)) |
| 842 | fields = result.scalars().all() |
| 843 | |
| 844 | # Delete all the field names for this source |
| 845 | for field in fields: |
| 846 | await session.delete(field) |
| 847 | |
| 848 | # Add the new field names |
| 849 | for field_name in field_names: |
| 850 | await add_field_name(source, field_name, session) |
| 851 | |
| 852 | # Commit the changes |
| 853 | await session.commit() |
| 854 | |
| 855 | |
| 856 | async def replace_ioc_name(source: str, ioc_names: List[str], session: AsyncSession): |
| 857 | # First delete all the ioc names for this source, then add the new ioc names |
| 858 | result = await session.execute(select(IoCFieldName).where(IoCFieldName.source == source)) |
| 859 | iocs = result.scalars().all() |
| 860 | |
| 861 | # Delete all the ioc names for this source |
| 862 | for ioc in iocs: |
| 863 | await session.delete(ioc) |
| 864 | |
| 865 | # Add the new ioc names |
| 866 | for ioc_name in ioc_names: |
| 867 | await add_ioc_name(source, ioc_name, session) |
| 868 | |
| 869 | # Commit the changes |
| 870 | await session.commit() |
| 871 | |
| 872 | |
| 873 | async def replace_asset_name(source: str, asset_name: str, session: AsyncSession): |
| 874 | # Load the current asset for this source from the DB, then delete it and replace it with `asset_name` |
| 875 | result = await session.execute(select(AssetFieldName).where(AssetFieldName.source == source)) |
| 876 | assets = result.scalars().all() |
| 877 | |
| 878 | # Assuming you want to update the field_name for all assets matching the source |
| 879 | for asset in assets: |
| 880 | asset.field_name = asset_name |
| 881 | |
| 882 | # Commit the changes |
| 883 | await session.commit() |
| 884 | |
| 885 | |
| 886 | async def replace_timefield_name(source: str, timefield_name: str, session: AsyncSession): |
| 887 | # Load the current timefield for this source from the DB, then delete it and replace it with `timefield_name` |
| 888 | result = await session.execute(select(TimestampFieldName).where(TimestampFieldName.source == source)) |
| 889 | timefields = result.scalars().all() |
| 890 | |
| 891 | # Assuming you want to update the field_name for all timefields matching the source |
| 892 | for timefield in timefields: |
| 893 | timefield.field_name = timefield_name |
| 894 | |
| 895 | # Commit the changes |
| 896 | await session.commit() |
| 897 | |
| 898 | |
| 899 | async def replace_alert_title_name(source: str, alert_title_name: str, session: AsyncSession): |
| 900 | # Load the current alert_title for this source from the DB, then delete it and replace it with `alert_title_name` |
| 901 | result = await session.execute(select(AlertTitleFieldName).where(AlertTitleFieldName.source == source)) |
| 902 | alert_titles = result.scalars().all() |
| 903 | |
| 904 | # Assuming you want to update the field_name for all alert_titles matching the source |
| 905 | for alert_title in alert_titles: |
| 906 | alert_title.field_name = alert_title_name |
| 907 | |
| 908 | # Commit the changes |
| 909 | await session.commit() |
| 910 | |
| 911 | |
| 912 | # ! NOT USING FOR NOW. GETTING THE CUSTOMER CODE FROM THE ALERTS SOURCE FIELD INSTEAD ! # |
| 913 | async def replace_customer_code_name(source: str, customer_code_name: str, session: AsyncSession): |
| 914 | # Load the current customer_code for this source from the DB, then delete it and replace it with `customer_code_name` |
| 915 | result = await session.execute(select(CustomerCodeFieldName).where(CustomerCodeFieldName.source == source)) |
| 916 | customer_codes = result.scalars().all() |
| 917 | |
| 918 | # Assuming you want to update the field_name for all customer_codes matching the source |
| 919 | for customer_code in customer_codes: |
| 920 | customer_code.field_name = customer_code_name |
| 921 | |
| 922 | # Commit the changes |
| 923 | await session.commit() |
| 924 | |
| 925 | |
| 926 | async def delete_field_name(source: str, field_name: str, session: AsyncSession): |
| 927 | logger.info(f"Deleting field name {field_name} for source {source}") |
| 928 | field = await session.execute(select(FieldName).where((FieldName.source == source) & (FieldName.field_name == field_name))) |
| 929 | field = field.scalar_one_or_none() |
| 930 | if field: |
| 931 | await session.delete(field) |
| 932 | |
| 933 | |
| 934 | async def delete_ioc_name(source: str, ioc_name: str, session: AsyncSession): |
| 935 | logger.info(f"Deleting ioc name {ioc_name} for source {source}") |
| 936 | ioc = await session.execute( |
| 937 | select(IoCFieldName).where((IoCFieldName.source == source) & (IoCFieldName.field_name == ioc_name)), |
| 938 | ) |
| 939 | ioc = ioc.scalar_one_or_none() |
| 940 | if ioc: |
| 941 | await session.delete(ioc) |
| 942 | |
| 943 | |
| 944 | async def delete_asset_name(source: str, asset_name: str, session: AsyncSession): |
| 945 | logger.info(f"Deleting asset name {asset_name} for source {source}") |
| 946 | asset = await session.execute( |
| 947 | select(AssetFieldName).where((AssetFieldName.source == source) & (AssetFieldName.field_name == asset_name)), |
| 948 | ) |
| 949 | asset = asset.scalar_one_or_none() |
| 950 | if asset: |
| 951 | await session.delete(asset) |
| 952 | |
| 953 | |
| 954 | async def delete_timefield_name(source: str, timefield_name: str, session: AsyncSession): |
| 955 | logger.info(f"Deleting timefield name {timefield_name} for source {source}") |
| 956 | timefield = await session.execute( |
| 957 | select(TimestampFieldName).where((TimestampFieldName.source == source) & (TimestampFieldName.field_name == timefield_name)), |
| 958 | ) |
| 959 | timefield = timefield.scalar_one_or_none() |
| 960 | if timefield: |
| 961 | await session.delete(timefield) |
| 962 | |
| 963 | |
| 964 | async def delete_alert_title_name(source: str, alert_title_name: str, session: AsyncSession): |
| 965 | logger.info(f"Deleting alert title name {alert_title_name} for source {source}") |
| 966 | alert_title = await session.execute( |
| 967 | select(AlertTitleFieldName).where((AlertTitleFieldName.source == source) & (AlertTitleFieldName.field_name == alert_title_name)), |
| 968 | ) |
| 969 | alert_title = alert_title.scalar_one_or_none() |
| 970 | if alert_title: |
| 971 | await session.delete(alert_title) |
| 972 | |
| 973 | |
| 974 | # ! NOT USING FOR NOW. GETTING THE CUSTOMER CODE FROM THE ALERTS SOURCE FIELD INSTEAD ! # |
| 975 | async def delete_customer_code_name(source: str, customer_code_name: str, session: AsyncSession): |
| 976 | logger.info(f"Deleting customer code name {customer_code_name} for source {source}") |
| 977 | customer_code = await session.execute( |
| 978 | select(CustomerCodeFieldName).where( |
| 979 | (CustomerCodeFieldName.source == source) & (CustomerCodeFieldName.field_name == customer_code_name), |
| 980 | ), |
| 981 | ) |
| 982 | customer_code = customer_code.scalar_one_or_none() |
| 983 | if customer_code: |
| 984 | await session.delete(customer_code) |
| 985 | |
| 986 | |
| 987 | async def create_alert(alert: AlertCreate, db: AsyncSession) -> Alert: |
| 988 | db_alert = Alert(**alert.model_dump()) |
| 989 | db.add(db_alert) |
| 990 | try: |
| 991 | await db.flush() |
| 992 | await db.refresh(db_alert) |
| 993 | await db.commit() |
| 994 | except IntegrityError: |
| 995 | await db.rollback() |
| 996 | raise HTTPException(status_code=400, detail="Alert already exists") |
| 997 | return db_alert |
| 998 | |
| 999 | |
| 1000 | async def update_alert_status(update_alert_status: UpdateAlertStatus, db: AsyncSession) -> Alert: |
| 1001 | result = await db.execute(select(Alert).where(Alert.id == update_alert_status.alert_id)) |
| 1002 | alert = result.scalars().first() |
| 1003 | if not alert: |
| 1004 | raise HTTPException(status_code=404, detail="Alert not found") |
| 1005 | alert.status = update_alert_status.status |
| 1006 | |
| 1007 | # Set time_closed if status is CLOSED |
| 1008 | if update_alert_status.status == "CLOSED": |
| 1009 | alert.time_closed = datetime.utcnow() |
| 1010 | # Reset time_closed if status is OPEN |
| 1011 | elif update_alert_status.status == "OPEN": |
| 1012 | alert.time_closed = None |
| 1013 | |
| 1014 | await db.commit() |
| 1015 | return alert |
| 1016 | |
| 1017 | |
| 1018 | async def update_case_status(update_case_status: UpdateCaseStatus, db: AsyncSession) -> Case: |
| 1019 | result = await db.execute(select(Case).where(Case.id == update_case_status.case_id)) |
| 1020 | case = result.scalars().first() |
| 1021 | if not case: |
| 1022 | raise HTTPException(status_code=404, detail="Case not found") |
| 1023 | case.case_status = update_case_status.status |
| 1024 | |
| 1025 | # Set case_closed_time if status is CLOSED |
| 1026 | if update_case_status.status == "CLOSED": |
| 1027 | case.case_closed_time = datetime.utcnow() |
| 1028 | # Reset case_closed_time if status is OPEN |
| 1029 | elif update_case_status.status == "OPEN": |
| 1030 | case.case_closed_time = None |
| 1031 | |
| 1032 | await db.commit() |
| 1033 | return case |
| 1034 | |
| 1035 | |
| 1036 | async def update_case_assigned_to(case_id: int, assigned_to: str, db: AsyncSession) -> Case: |
| 1037 | result = await db.execute(select(Case).where(Case.id == case_id)) |
| 1038 | case = result.scalars().first() |
| 1039 | if not case: |
| 1040 | raise HTTPException(status_code=404, detail="Case not found") |
| 1041 | case.assigned_to = assigned_to |
| 1042 | await db.commit() |
| 1043 | return case |
| 1044 | |
| 1045 | |
| 1046 | async def update_case_customer_code(case_id: int, customer_code: str, db: AsyncSession) -> Case: |
| 1047 | await customer_code_valid(customer_code, db) |
| 1048 | result = await db.execute(select(Case).where(Case.id == case_id)) |
| 1049 | case = result.scalars().first() |
| 1050 | if not case: |
| 1051 | raise HTTPException(status_code=404, detail="Case not found") |
| 1052 | case.customer_code = customer_code |
| 1053 | await db.commit() |
| 1054 | return case |
| 1055 | |
| 1056 | |
| 1057 | async def update_alert_assigned_to(alert_id: int, assigned_to: str, db: AsyncSession) -> Alert: |
| 1058 | result = await db.execute(select(Alert).where(Alert.id == alert_id)) |
| 1059 | alert = result.scalars().first() |
| 1060 | if not alert: |
| 1061 | raise HTTPException(status_code=404, detail="Alert not found") |
| 1062 | alert.assigned_to = assigned_to |
| 1063 | await db.commit() |
| 1064 | return alert |
| 1065 | |
| 1066 | |
| 1067 | async def update_alert_escalated(alert_id: int, escalated: bool, db: AsyncSession) -> Alert: |
| 1068 | result = await db.execute(select(Alert).where(Alert.id == alert_id)) |
| 1069 | alert = result.scalars().first() |
| 1070 | if not alert: |
| 1071 | raise HTTPException(status_code=404, detail="Alert not found") |
| 1072 | alert.escalated = escalated |
| 1073 | await db.commit() |
| 1074 | return alert |
| 1075 | |
| 1076 | |
| 1077 | async def update_case_escalated(case_id: int, escalated: bool, db: AsyncSession) -> Case: |
| 1078 | result = await db.execute(select(Case).where(Case.id == case_id)) |
| 1079 | case = result.scalars().first() |
| 1080 | if not case: |
| 1081 | raise HTTPException(status_code=404, detail="Case not found") |
| 1082 | case.escalated = escalated |
| 1083 | await db.commit() |
| 1084 | return case |
| 1085 | |
| 1086 | |
| 1087 | async def increment_case_notification_count(case_id: int, db: AsyncSession) -> Case: |
| 1088 | result = await db.execute(select(Case).where(Case.id == case_id)) |
| 1089 | case = result.scalars().first() |
| 1090 | if not case: |
| 1091 | raise HTTPException(status_code=404, detail="Case not found") |
| 1092 | |
| 1093 | # Initialize notification_invoked_number to 0 if it is None |
| 1094 | if case.notification_invoked_number is None: |
| 1095 | case.notification_invoked_number = 0 |
| 1096 | |
| 1097 | case.notification_invoked_number += 1 |
| 1098 | await db.commit() |
| 1099 | return case |
| 1100 | |
| 1101 | |
| 1102 | async def create_comment(comment: CommentCreate, db: AsyncSession) -> Comment: |
| 1103 | # Check if the alert exists |
| 1104 | result = await db.execute(select(Alert).options(selectinload(Alert.comments)).where(Alert.id == comment.alert_id)) |
| 1105 | alert = result.scalars().first() |
| 1106 | if not alert: |
| 1107 | raise HTTPException(status_code=404, detail="Alert not found") |
| 1108 | |
| 1109 | # Create comment with automatic timestamp if not provided |
| 1110 | comment_data = comment.model_dump() |
| 1111 | if comment_data.get("created_at") is None: |
| 1112 | comment_data["created_at"] = datetime.utcnow() |
| 1113 | |
| 1114 | db_comment = Comment(**comment_data) |
| 1115 | db.add(db_comment) |
| 1116 | try: |
| 1117 | await db.commit() |
| 1118 | except IntegrityError: |
| 1119 | raise HTTPException(status_code=400, detail="Comment already exists") |
| 1120 | return db_comment |
| 1121 | |
| 1122 | |
| 1123 | async def edit_comment(comment: CommentEdit, db: AsyncSession) -> Comment: |
| 1124 | result = await db.execute(select(Comment).where(Comment.id == comment.comment_id)) |
| 1125 | db_comment = result.scalars().first() |
| 1126 | if not db_comment: |
| 1127 | raise HTTPException(status_code=404, detail="Comment not found") |
| 1128 | db_comment.comment = comment.comment |
| 1129 | db_comment.user_name = comment.user_name |
| 1130 | await db.commit() |
| 1131 | return db_comment |
| 1132 | |
| 1133 | |
| 1134 | async def delete_comment(comment_id: int, db: AsyncSession) -> Comment: |
| 1135 | result = await db.execute(select(Comment).where(Comment.id == comment_id)) |
| 1136 | comment = result.scalars().first() |
| 1137 | if not comment: |
| 1138 | raise HTTPException(status_code=404, detail="Comment not found") |
| 1139 | await db.execute(delete(Comment).where(Comment.id == comment_id)) |
| 1140 | await db.commit() |
| 1141 | return comment |
| 1142 | |
| 1143 | |
| 1144 | async def create_case_comment(comment: CaseCommentCreate, db: AsyncSession) -> CaseComment: |
| 1145 | # Check if the case exists |
| 1146 | result = await db.execute(select(Case).options(selectinload(Case.comments)).where(Case.id == comment.case_id)) |
| 1147 | case = result.scalars().first() |
| 1148 | if not case: |
| 1149 | raise HTTPException(status_code=404, detail="Case not found") |
| 1150 | |
| 1151 | # Create comment with automatic timestamp if not provided |
| 1152 | comment_data = comment.model_dump() |
| 1153 | if comment_data.get("created_at") is None: |
| 1154 | comment_data["created_at"] = datetime.utcnow() |
| 1155 | |
| 1156 | db_comment = CaseComment(**comment_data) |
| 1157 | db.add(db_comment) |
| 1158 | try: |
| 1159 | await db.commit() |
| 1160 | except IntegrityError: |
| 1161 | raise HTTPException(status_code=400, detail="Comment already exists") |
| 1162 | return db_comment |
| 1163 | |
| 1164 | |
| 1165 | async def edit_case_comment(comment: CaseCommentEdit, db: AsyncSession) -> CaseComment: |
| 1166 | result = await db.execute(select(CaseComment).where(CaseComment.id == comment.comment_id)) |
| 1167 | db_comment = result.scalars().first() |
| 1168 | if not db_comment: |
| 1169 | raise HTTPException(status_code=404, detail="Comment not found") |
| 1170 | db_comment.comment = comment.comment |
| 1171 | db_comment.user_name = comment.user_name |
| 1172 | await db.commit() |
| 1173 | return db_comment |
| 1174 | |
| 1175 | |
| 1176 | async def delete_case_comment(comment_id: int, db: AsyncSession) -> CaseComment: |
| 1177 | result = await db.execute(select(CaseComment).where(CaseComment.id == comment_id)) |
| 1178 | comment = result.scalars().first() |
| 1179 | if not comment: |
| 1180 | raise HTTPException(status_code=404, detail="Comment not found") |
| 1181 | await db.execute(delete(CaseComment).where(CaseComment.id == comment_id)) |
| 1182 | await db.commit() |
| 1183 | return comment |
| 1184 | |
| 1185 | |
| 1186 | async def create_asset(asset: AssetCreate, db: AsyncSession) -> Asset: |
| 1187 | # Check if the alert exists |
| 1188 | result = await db.execute(select(Alert).options(selectinload(Alert.assets)).where(Alert.id == asset.alert_linked)) |
| 1189 | alert = result.scalars().first() |
| 1190 | if not alert: |
| 1191 | raise HTTPException(status_code=404, detail="Alert not found") |
| 1192 | |
| 1193 | # Check that the alert_context exists |
| 1194 | result = await db.execute(select(AlertContext).where(AlertContext.id == asset.alert_context_id)) |
| 1195 | alert_context = result.scalars().first() |
| 1196 | if not alert_context: |
| 1197 | raise HTTPException(status_code=404, detail="Alert context not found") |
| 1198 | |
| 1199 | db_asset = Asset(**asset.model_dump()) |
| 1200 | db.add(db_asset) |
| 1201 | try: |
| 1202 | await db.commit() |
| 1203 | except IntegrityError: |
| 1204 | raise HTTPException(status_code=400, detail="Asset already exists") |
| 1205 | return db_asset |
| 1206 | |
| 1207 | |
| 1208 | async def create_alert_ioc(alert_ioc: AlertIoCCreate, db: AsyncSession) -> AlertToIoC: |
| 1209 | # Create the IoC instance |
| 1210 | db_alert_ioc = IoC( |
| 1211 | value=alert_ioc.ioc_value, |
| 1212 | type=alert_ioc.ioc_type, |
| 1213 | description=alert_ioc.ioc_description, |
| 1214 | ) |
| 1215 | db.add(db_alert_ioc) |
| 1216 | await db.flush() |
| 1217 | |
| 1218 | # Create the AlertToIoC instance |
| 1219 | db_alert_to_ioc = AlertToIoC(alert_id=alert_ioc.alert_id, ioc_id=db_alert_ioc.id) |
| 1220 | db.add(db_alert_to_ioc) |
| 1221 | |
| 1222 | try: |
| 1223 | await db.commit() |
| 1224 | except IntegrityError: |
| 1225 | await db.rollback() |
| 1226 | raise HTTPException(status_code=400, detail="Alert IoC already exists") |
| 1227 | return AlertToIoC(alert_id=db_alert_to_ioc.alert_id, ioc_id=db_alert_to_ioc.ioc_id) |
| 1228 | |
| 1229 | |
| 1230 | async def delete_alert_ioc(ioc: AlertIoCDelete, db: AsyncSession) -> AlertToIoC: |
| 1231 | result = await db.execute(select(AlertToIoC).where((AlertToIoC.alert_id == ioc.alert_id) & (AlertToIoC.ioc_id == ioc.ioc_id))) |
| 1232 | alert_ioc = result.scalars().first() |
| 1233 | if not alert_ioc: |
| 1234 | raise HTTPException(status_code=404, detail="Alert IoC not found") |
| 1235 | |
| 1236 | await db.execute(delete(AlertToIoC).where((AlertToIoC.alert_id == ioc.alert_id) & (AlertToIoC.ioc_id == ioc.ioc_id))) |
| 1237 | |
| 1238 | # Delete the IoC from the IoC table |
| 1239 | await db.execute(delete(IoC).where(IoC.id == ioc.ioc_id)) |
| 1240 | |
| 1241 | try: |
| 1242 | await db.commit() |
| 1243 | except IntegrityError: |
| 1244 | await db.rollback() |
| 1245 | raise HTTPException(status_code=400, detail="Error deleting alert IoC") |
| 1246 | |
| 1247 | return alert_ioc |
| 1248 | |
| 1249 | |
| 1250 | async def create_alert_tag(alert_tag: AlertTagCreate, db: AsyncSession) -> AlertTag: |
| 1251 | # Create the AlertTag instance |
| 1252 | db_alert_tag = AlertTag(**alert_tag.model_dump()) |
| 1253 | db.add(db_alert_tag) |
| 1254 | await db.flush() |
| 1255 | |
| 1256 | # Create the AlertToTag instance |
| 1257 | db_alert_to_tag = AlertToTag(alert_id=alert_tag.alert_id, tag_id=db_alert_tag.id) |
| 1258 | db.add(db_alert_to_tag) |
| 1259 | |
| 1260 | try: |
| 1261 | await db.commit() |
| 1262 | except IntegrityError: |
| 1263 | await db.rollback() |
| 1264 | raise HTTPException(status_code=400, detail="Alert tag already exists") |
| 1265 | return db_alert_tag |
| 1266 | |
| 1267 | |
| 1268 | async def add_alert_tag_if_not_exists(alert_tag: AlertTagCreate, db: AsyncSession) -> AlertTag: |
| 1269 | # Check if the tag already exists |
| 1270 | result = await db.execute(select(AlertTag).where(AlertTag.tag == alert_tag.tag)) |
| 1271 | existing_tag = result.scalars().first() |
| 1272 | |
| 1273 | if existing_tag: |
| 1274 | logger.info(f"Tag {alert_tag.tag} already exists with ID {alert_tag.alert_id}") |
| 1275 | return None |
| 1276 | |
| 1277 | # If it doesn't exist, create a new one |
| 1278 | return await create_alert_tag(alert_tag, db) |
| 1279 | |
| 1280 | |
| 1281 | async def delete_alert_tag(alert_id: int, tag_id: int, db: AsyncSession): |
| 1282 | result = await db.execute(select(AlertTag).where(AlertTag.id == tag_id)) |
| 1283 | alert_tag = result.scalars().first() |
| 1284 | if not alert_tag: |
| 1285 | raise HTTPException(status_code=404, detail="Alert tag not found") |
| 1286 | |
| 1287 | result = await db.execute(select(AlertToTag).where((AlertToTag.alert_id == alert_id) & (AlertToTag.tag_id == tag_id))) |
| 1288 | alert_to_tag = result.scalars().first() |
| 1289 | if not alert_to_tag: |
| 1290 | raise HTTPException(status_code=404, detail="Alert to tag link not found") |
| 1291 | |
| 1292 | await db.execute(delete(AlertToTag).where((AlertToTag.alert_id == alert_id) & (AlertToTag.tag_id == tag_id))) |
| 1293 | |
| 1294 | # Delete the tag from the AlertTag table |
| 1295 | await db.execute(delete(AlertTag).where(AlertTag.id == tag_id)) |
| 1296 | |
| 1297 | try: |
| 1298 | await db.commit() |
| 1299 | except IntegrityError: |
| 1300 | await db.rollback() |
| 1301 | raise HTTPException(status_code=400, detail="Error deleting alert tag") |
| 1302 | |
| 1303 | return alert_tag |
| 1304 | |
| 1305 | |
| 1306 | async def create_alert_context(alert_context: AlertContextCreate, db: AsyncSession) -> AlertContext: |
| 1307 | db_alert_context = AlertContext(**alert_context.model_dump()) |
| 1308 | db.add(db_alert_context) |
| 1309 | try: |
| 1310 | await db.flush() |
| 1311 | await db.refresh(db_alert_context) |
| 1312 | await db.commit() |
| 1313 | except IntegrityError: |
| 1314 | await db.rollback() |
| 1315 | raise HTTPException(status_code=400, detail="Alert context already exists") |
| 1316 | return db_alert_context |
| 1317 | |
| 1318 | |
| 1319 | async def get_alert_by_id(alert_id: int, db: AsyncSession, user: Optional[User] = None) -> AlertOut: |
| 1320 | """ |
| 1321 | Get alert by ID with optional tag-based access validation. |
| 1322 | |
| 1323 | Args: |
| 1324 | alert_id: The alert ID to retrieve |
| 1325 | db: Database session |
| 1326 | user: Optional user for tag access validation |
| 1327 | """ |
| 1328 | result = await db.execute( |
| 1329 | select(Alert) |
| 1330 | .where(Alert.id == alert_id) |
| 1331 | .options( |
| 1332 | selectinload(Alert.comments), |
| 1333 | selectinload(Alert.assets), |
| 1334 | selectinload(Alert.cases).selectinload(CaseAlertLink.case), |
| 1335 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 1336 | selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 1337 | ), |
| 1338 | ) |
| 1339 | alert = result.scalars().first() |
| 1340 | if not alert: |
| 1341 | raise HTTPException(status_code=404, detail="Alert not found") |
| 1342 | |
| 1343 | # Check tag access if user is provided |
| 1344 | if user: |
| 1345 | has_access = await tag_access_handler.check_alert_tag_access(user, alert, db) |
| 1346 | if not has_access: |
| 1347 | raise HTTPException(status_code=403, detail=f"Access denied to alert {alert_id} - insufficient tag permissions") |
| 1348 | |
| 1349 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 1350 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 1351 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 1352 | iocs = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 1353 | linked_cases = [LinkedCaseCreate(**case_alert_link.case.__dict__) for case_alert_link in alert.cases] |
| 1354 | |
| 1355 | alert_out = AlertOut( |
| 1356 | id=alert.id, |
| 1357 | alert_creation_time=alert.alert_creation_time, |
| 1358 | time_closed=alert.time_closed, |
| 1359 | alert_name=alert.alert_name, |
| 1360 | alert_description=alert.alert_description, |
| 1361 | status=alert.status, |
| 1362 | customer_code=alert.customer_code, |
| 1363 | source=alert.source, |
| 1364 | assigned_to=alert.assigned_to, |
| 1365 | escalated=alert.escalated, |
| 1366 | comments=comments, |
| 1367 | assets=assets, |
| 1368 | tags=tags, |
| 1369 | iocs=iocs, |
| 1370 | linked_cases=linked_cases, |
| 1371 | ) |
| 1372 | |
| 1373 | return alert_out |
| 1374 | |
| 1375 | |
| 1376 | async def list_alerts(db: AsyncSession, page: int = 1, page_size: int = 25, order: str = "desc") -> List[AlertOut]: |
| 1377 | offset = (page - 1) * page_size |
| 1378 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 1379 | |
| 1380 | result = await db.execute( |
| 1381 | select(Alert) |
| 1382 | .options( |
| 1383 | selectinload(Alert.comments), |
| 1384 | selectinload(Alert.assets), |
| 1385 | selectinload(Alert.cases).selectinload(CaseAlertLink.case), |
| 1386 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 1387 | selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 1388 | ) |
| 1389 | .order_by(order_by) |
| 1390 | .offset(offset) |
| 1391 | .limit(page_size), |
| 1392 | ) |
| 1393 | |
| 1394 | alerts = result.scalars().all() |
| 1395 | alerts_out = [] |
| 1396 | for alert in alerts: |
| 1397 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 1398 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 1399 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 1400 | iocs = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 1401 | linked_cases = [LinkedCaseCreate(**case_alert_link.case.__dict__) for case_alert_link in alert.cases] |
| 1402 | alert_out = AlertOut( |
| 1403 | id=alert.id, |
| 1404 | alert_creation_time=alert.alert_creation_time, |
| 1405 | time_closed=alert.time_closed, |
| 1406 | alert_name=alert.alert_name, |
| 1407 | alert_description=alert.alert_description, |
| 1408 | status=alert.status, |
| 1409 | customer_code=alert.customer_code, |
| 1410 | source=alert.source, |
| 1411 | assigned_to=alert.assigned_to, |
| 1412 | escalated=alert.escalated, |
| 1413 | comments=comments, |
| 1414 | assets=assets, |
| 1415 | tags=tags, |
| 1416 | iocs=iocs, |
| 1417 | linked_cases=linked_cases, |
| 1418 | ) |
| 1419 | alerts_out.append(alert_out) |
| 1420 | return alerts_out |
| 1421 | |
| 1422 | |
| 1423 | async def create_case( |
| 1424 | case: CaseCreate, |
| 1425 | db: AsyncSession, |
| 1426 | *, |
| 1427 | actor: Optional[str] = None, |
| 1428 | template_id: Optional[int] = None, |
| 1429 | ) -> Case: |
| 1430 | """ |
| 1431 | Create a Case manually (no originating alert). |
| 1432 | |
| 1433 | When ``template_id`` is supplied, the named CaseTemplate is applied |
| 1434 | immediately. Otherwise tasks are NOT auto-applied — the manual path |
| 1435 | has no source hint to pick from. Analysts can apply a template later |
| 1436 | via ``POST /case/{id}/apply-template/{template_id}``. |
| 1437 | """ |
| 1438 | db_case = Case(**case.model_dump()) |
| 1439 | db.add(db_case) |
| 1440 | try: |
| 1441 | await db.flush() |
| 1442 | await db.refresh(db_case) |
| 1443 | |
| 1444 | if template_id is not None: |
| 1445 | from app.incidents.services.case_tasks import apply_template_to_case |
| 1446 | |
| 1447 | await apply_template_to_case( |
| 1448 | case_id=db_case.id, |
| 1449 | template_id=template_id, |
| 1450 | actor=actor or "system", |
| 1451 | session=db, |
| 1452 | commit=False, |
| 1453 | ) |
| 1454 | |
| 1455 | await db.commit() |
| 1456 | except IntegrityError: |
| 1457 | await db.rollback() |
| 1458 | raise HTTPException(status_code=400, detail="Case already exists") |
| 1459 | return db_case |
| 1460 | |
| 1461 | |
| 1462 | async def create_case_from_alert( |
| 1463 | alert_id: int, |
| 1464 | db: AsyncSession, |
| 1465 | *, |
| 1466 | actor: Optional[str] = None, |
| 1467 | template_id: Optional[int] = None, |
| 1468 | ) -> Case: |
| 1469 | """ |
| 1470 | Create a Case from an Alert and (Phase 3, issue #792) auto-apply a |
| 1471 | matching CaseTemplate. |
| 1472 | |
| 1473 | Template selection (when ``template_id`` is not supplied): pick by |
| 1474 | ``(alert.customer_code, alert.source)`` using the priority order in |
| 1475 | ``app.incidents.services.case_tasks.pick_template_for_case``. The |
| 1476 | materialized tasks are stamped with the originating alert's id so |
| 1477 | the Tasks UI can group them under that alert. Subsequent alerts |
| 1478 | linked to this case retrigger per-alert auto-apply against their |
| 1479 | own source. If no template matches, no tasks are created and the |
| 1480 | case is returned unchanged. |
| 1481 | |
| 1482 | ``actor`` is the username performing the action; used as |
| 1483 | ``CaseTask.created_by`` for snapshot rows. Defaults to "system" when |
| 1484 | the caller doesn't have it (legacy code paths). |
| 1485 | """ |
| 1486 | logger.info(f"Creating case from alert {alert_id}") |
| 1487 | result = await db.execute(select(Alert).where(Alert.id == alert_id)) |
| 1488 | alert = result.scalars().first() |
| 1489 | if not alert: |
| 1490 | raise HTTPException(status_code=404, detail="Alert not found") |
| 1491 | case = Case( |
| 1492 | case_name=alert.alert_name, |
| 1493 | case_description=alert.alert_description, |
| 1494 | case_status=alert.status, |
| 1495 | assigned_to=alert.assigned_to, |
| 1496 | escalated=alert.escalated, |
| 1497 | customer_code=alert.customer_code, |
| 1498 | ) |
| 1499 | db.add(case) |
| 1500 | try: |
| 1501 | await db.flush() |
| 1502 | await db.refresh(case) |
| 1503 | |
| 1504 | # Apply a case template (Phase 3, issue #792). Imported lazily to |
| 1505 | # avoid a circular import — case_tasks pulls from this module too. |
| 1506 | # The originating alert id is stamped on the materialized tasks so |
| 1507 | # the Tasks UI can group them under that alert. |
| 1508 | from app.incidents.services.case_tasks import apply_template_to_case |
| 1509 | from app.incidents.services.case_tasks import auto_apply_template_for_new_case |
| 1510 | |
| 1511 | actor_name = actor or "system" |
| 1512 | if template_id is not None: |
| 1513 | await apply_template_to_case( |
| 1514 | case_id=case.id, |
| 1515 | template_id=template_id, |
| 1516 | actor=actor_name, |
| 1517 | session=db, |
| 1518 | alert_id=alert.id, |
| 1519 | commit=False, |
| 1520 | ) |
| 1521 | else: |
| 1522 | await auto_apply_template_for_new_case( |
| 1523 | case=case, |
| 1524 | alert=alert, |
| 1525 | actor=actor_name, |
| 1526 | session=db, |
| 1527 | ) |
| 1528 | |
| 1529 | await db.commit() |
| 1530 | except IntegrityError: |
| 1531 | await db.rollback() |
| 1532 | raise HTTPException(status_code=400, detail="Case already exists") |
| 1533 | return case |
| 1534 | |
| 1535 | |
| 1536 | async def create_case_alert_link( |
| 1537 | case_alert_link: CaseAlertLinkCreate, |
| 1538 | db: AsyncSession, |
| 1539 | *, |
| 1540 | actor: Optional[str] = None, |
| 1541 | auto_apply_template: bool = True, |
| 1542 | ) -> CaseAlertLink: |
| 1543 | """ |
| 1544 | Link an alert to a case. |
| 1545 | |
| 1546 | When ``auto_apply_template`` is True (the default for analyst-driven |
| 1547 | linking), the per-alert auto-apply rule runs: a matching CaseTemplate |
| 1548 | is picked against the case's customer_code and the linked alert's |
| 1549 | source, and any materialized tasks are stamped with that alert's id |
| 1550 | so the Tasks UI can group them under their originating alert. |
| 1551 | |
| 1552 | Pass ``auto_apply_template=False`` when the caller has already handled |
| 1553 | template application for this alert (e.g., ``/case/from-alert`` runs |
| 1554 | ``create_case_from_alert`` first, which does its own apply against the |
| 1555 | originating alert — re-applying on the subsequent link would double the |
| 1556 | tasks). |
| 1557 | """ |
| 1558 | # Check if the case exists |
| 1559 | result = await db.execute(select(Case).where(Case.id == case_alert_link.case_id)) |
| 1560 | case = result.scalars().first() |
| 1561 | if not case: |
| 1562 | raise HTTPException(status_code=404, detail="Case not found") |
| 1563 | |
| 1564 | # Check if the alert exists |
| 1565 | result = await db.execute(select(Alert).where(Alert.id == case_alert_link.alert_id)) |
| 1566 | alert = result.scalars().first() |
| 1567 | if not alert: |
| 1568 | raise HTTPException(status_code=404, detail="Alert not found") |
| 1569 | |
| 1570 | db_case_alert_link = CaseAlertLink(**case_alert_link.model_dump()) |
| 1571 | db.add(db_case_alert_link) |
| 1572 | try: |
| 1573 | await db.flush() |
| 1574 | if auto_apply_template: |
| 1575 | from app.incidents.services.case_tasks import ( |
| 1576 | auto_apply_template_for_new_case, |
| 1577 | ) |
| 1578 | |
| 1579 | await auto_apply_template_for_new_case( |
| 1580 | case=case, |
| 1581 | alert=alert, |
| 1582 | actor=actor or "system", |
| 1583 | session=db, |
| 1584 | ) |
| 1585 | await db.commit() |
| 1586 | except IntegrityError: |
| 1587 | await db.rollback() |
| 1588 | raise HTTPException(status_code=400, detail="Case alert link already exists") |
| 1589 | return db_case_alert_link |
| 1590 | |
| 1591 | |
| 1592 | async def case_alert_unlink(case_alert_unlink: CaseAlertUnLink, db: AsyncSession) -> CaseAlertUnLinkResponse: |
| 1593 | """ |
| 1594 | Unlink an alert from a case. |
| 1595 | |
| 1596 | Any CaseTask rows that were stamped with this alert id are *orphaned* |
| 1597 | (alert_id set NULL) rather than deleted — analysts already accumulated |
| 1598 | investigation evidence on them, and the snapshot-preserving spirit of |
| 1599 | the case-templates feature says that history outlives the link. |
| 1600 | """ |
| 1601 | result = await db.execute( |
| 1602 | select(CaseAlertLink).where( |
| 1603 | (CaseAlertLink.case_id == case_alert_unlink.case_id) & (CaseAlertLink.alert_id == case_alert_unlink.alert_id), |
| 1604 | ), |
| 1605 | ) |
| 1606 | case_alert_link = result.scalars().first() |
| 1607 | if not case_alert_link: |
| 1608 | raise HTTPException(status_code=404, detail="Case alert link not found") |
| 1609 | await db.execute( |
| 1610 | delete(CaseAlertLink).where( |
| 1611 | (CaseAlertLink.case_id == case_alert_unlink.case_id) & (CaseAlertLink.alert_id == case_alert_unlink.alert_id), |
| 1612 | ), |
| 1613 | ) |
| 1614 | |
| 1615 | from app.incidents.services.case_tasks import orphan_tasks_for_alert |
| 1616 | |
| 1617 | orphaned = await orphan_tasks_for_alert( |
| 1618 | case_id=case_alert_unlink.case_id, |
| 1619 | alert_id=case_alert_unlink.alert_id, |
| 1620 | session=db, |
| 1621 | commit=False, |
| 1622 | ) |
| 1623 | await db.commit() |
| 1624 | return CaseAlertUnLinkResponse( |
| 1625 | success=True, |
| 1626 | message=( |
| 1627 | f"Case alert link deleted successfully; {orphaned} task(s) orphaned" if orphaned else "Case alert link deleted successfully" |
| 1628 | ), |
| 1629 | tasks_orphaned=orphaned, |
| 1630 | ) |
| 1631 | |
| 1632 | |
| 1633 | async def create_case_alert_links_bulk( |
| 1634 | case_alert_links: CaseAlertLinksCreate, |
| 1635 | db: AsyncSession, |
| 1636 | *, |
| 1637 | actor: Optional[str] = None, |
| 1638 | auto_apply_template: bool = True, |
| 1639 | ) -> List[CaseAlertLink]: |
| 1640 | """ |
| 1641 | Bulk-link multiple alerts to a single case. |
| 1642 | |
| 1643 | Per-alert auto-apply runs once per linked alert (same matching rules as |
| 1644 | the single-link path). 50 alerts of the same source → 50 task batches; |
| 1645 | that's the model. Pass ``auto_apply_template=False`` to skip. |
| 1646 | """ |
| 1647 | db_case_alert_links = [CaseAlertLink(case_id=case_alert_links.case_id, alert_id=alert_id) for alert_id in case_alert_links.alert_ids] |
| 1648 | db.add_all(db_case_alert_links) |
| 1649 | try: |
| 1650 | await db.flush() |
| 1651 | if auto_apply_template and case_alert_links.alert_ids: |
| 1652 | from app.incidents.services.case_tasks import ( |
| 1653 | auto_apply_template_for_new_case, |
| 1654 | ) |
| 1655 | |
| 1656 | case_result = await db.execute(select(Case).where(Case.id == case_alert_links.case_id)) |
| 1657 | case = case_result.scalars().first() |
| 1658 | if case is None: |
| 1659 | raise HTTPException(status_code=404, detail="Case not found") |
| 1660 | |
| 1661 | alerts_result = await db.execute( |
| 1662 | select(Alert).where(Alert.id.in_(case_alert_links.alert_ids)), |
| 1663 | ) |
| 1664 | alerts = alerts_result.scalars().all() |
| 1665 | actor_name = actor or "system" |
| 1666 | for alert in alerts: |
| 1667 | await auto_apply_template_for_new_case( |
| 1668 | case=case, |
| 1669 | alert=alert, |
| 1670 | actor=actor_name, |
| 1671 | session=db, |
| 1672 | ) |
| 1673 | await db.commit() |
| 1674 | except IntegrityError: |
| 1675 | await db.rollback() |
| 1676 | raise HTTPException(status_code=400, detail="Case alert links already exist") |
| 1677 | return db_case_alert_links |
| 1678 | |
| 1679 | |
| 1680 | async def get_case_by_id(case_id: int, db: AsyncSession) -> CaseOut: |
| 1681 | result = await db.execute( |
| 1682 | select(Case) |
| 1683 | .where(Case.id == case_id) |
| 1684 | .options( |
| 1685 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.comments), |
| 1686 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.assets), |
| 1687 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 1688 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.cases).selectinload(CaseAlertLink.case), |
| 1689 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 1690 | selectinload(Case.comments), |
| 1691 | ), |
| 1692 | ) |
| 1693 | case = result.scalars().first() |
| 1694 | if not case: |
| 1695 | raise HTTPException(status_code=404, detail="Case not found") |
| 1696 | alerts_out = [] |
| 1697 | for case_alert_link in case.alerts: |
| 1698 | alert = case_alert_link.alert |
| 1699 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 1700 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 1701 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 1702 | linked_cases = [LinkedCaseCreate(**case_alert_link.case.__dict__) for case_alert_link in alert.cases] |
| 1703 | iocs = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 1704 | alert_out = AlertOut( |
| 1705 | id=alert.id, |
| 1706 | alert_creation_time=alert.alert_creation_time, |
| 1707 | time_closed=alert.time_closed, |
| 1708 | alert_name=alert.alert_name, |
| 1709 | alert_description=alert.alert_description, |
| 1710 | status=alert.status, |
| 1711 | customer_code=alert.customer_code, |
| 1712 | source=alert.source, |
| 1713 | assigned_to=alert.assigned_to, |
| 1714 | escalated=alert.escalated, |
| 1715 | comments=comments, |
| 1716 | assets=assets, |
| 1717 | tags=tags, |
| 1718 | linked_cases=linked_cases, |
| 1719 | iocs=iocs, |
| 1720 | ) |
| 1721 | alerts_out.append(alert_out) |
| 1722 | |
| 1723 | # Extract case comments |
| 1724 | case_comments = [CaseCommentBase(**comment.__dict__) for comment in case.comments] |
| 1725 | |
| 1726 | case_out = CaseOut( |
| 1727 | id=case.id, |
| 1728 | case_name=case.case_name, |
| 1729 | case_description=case.case_description, |
| 1730 | assigned_to=case.assigned_to, |
| 1731 | alerts=alerts_out, |
| 1732 | case_status=case.case_status, |
| 1733 | case_creation_time=case.case_creation_time, |
| 1734 | customer_code=case.customer_code, |
| 1735 | notification_invoked_number=case.notification_invoked_number or 0, |
| 1736 | comments=case_comments, |
| 1737 | escalated=case.escalated, |
| 1738 | ) |
| 1739 | return case_out |
| 1740 | |
| 1741 | |
| 1742 | async def list_cases(db: AsyncSession) -> List[CaseOut]: |
| 1743 | result = await db.execute( |
| 1744 | select(Case).options( |
| 1745 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.comments), |
| 1746 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.assets), |
| 1747 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 1748 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.cases).selectinload(CaseAlertLink.case), |
| 1749 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 1750 | selectinload(Case.comments), |
| 1751 | ), |
| 1752 | ) |
| 1753 | cases = result.scalars().all() |
| 1754 | cases_out = [] |
| 1755 | for case in cases: |
| 1756 | alerts_out = [] |
| 1757 | for case_alert_link in case.alerts: |
| 1758 | alert = case_alert_link.alert |
| 1759 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 1760 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 1761 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 1762 | linked_cases = [LinkedCaseCreate(**case_alert_link.case.__dict__) for case_alert_link in alert.cases] |
| 1763 | iocs = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 1764 | alert_out = AlertOut( |
| 1765 | id=alert.id, |
| 1766 | alert_creation_time=alert.alert_creation_time, |
| 1767 | time_closed=alert.time_closed, |
| 1768 | alert_name=alert.alert_name, |
| 1769 | alert_description=alert.alert_description, |
| 1770 | status=alert.status, |
| 1771 | customer_code=alert.customer_code, |
| 1772 | source=alert.source, |
| 1773 | assigned_to=alert.assigned_to, |
| 1774 | escalated=alert.escalated, |
| 1775 | comments=comments, |
| 1776 | assets=assets, |
| 1777 | tags=tags, |
| 1778 | linked_cases=linked_cases, |
| 1779 | iocs=iocs, |
| 1780 | ) |
| 1781 | alerts_out.append(alert_out) |
| 1782 | |
| 1783 | # Extract case comments |
| 1784 | case_comments = [CaseCommentBase(**comment.__dict__) for comment in case.comments] |
| 1785 | |
| 1786 | case_out = CaseOut( |
| 1787 | id=case.id, |
| 1788 | case_name=case.case_name, |
| 1789 | case_description=case.case_description, |
| 1790 | assigned_to=case.assigned_to, |
| 1791 | alerts=alerts_out, |
| 1792 | case_creation_time=case.case_creation_time, |
| 1793 | case_status=case.case_status, |
| 1794 | customer_code=case.customer_code, |
| 1795 | notification_invoked_number=case.notification_invoked_number or 0, |
| 1796 | comments=case_comments, |
| 1797 | escalated=case.escalated, |
| 1798 | ) |
| 1799 | cases_out.append(case_out) |
| 1800 | return cases_out |
| 1801 | |
| 1802 | |
| 1803 | async def list_cases_by_status(status: str, db: AsyncSession, page: int = 1, page_size: int = 25, order: str = "desc") -> List[CaseOut]: |
| 1804 | offset = (page - 1) * page_size |
| 1805 | order_by = asc(Case.id) if order == "asc" else desc(Case.id) |
| 1806 | |
| 1807 | result = await db.execute( |
| 1808 | select(Case) |
| 1809 | .where(Case.case_status == status) |
| 1810 | .options( |
| 1811 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.comments), |
| 1812 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.assets), |
| 1813 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 1814 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.cases).selectinload(CaseAlertLink.case), |
| 1815 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 1816 | selectinload(Case.comments), |
| 1817 | ) |
| 1818 | .order_by(order_by) |
| 1819 | .offset(offset) |
| 1820 | .limit(page_size), |
| 1821 | ) |
| 1822 | cases = result.scalars().all() |
| 1823 | cases_out = [] |
| 1824 | for case in cases: |
| 1825 | alerts_out = [] |
| 1826 | for case_alert_link in case.alerts: |
| 1827 | alert = case_alert_link.alert |
| 1828 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 1829 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 1830 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 1831 | linked_cases = [LinkedCaseCreate(**case_alert_link.case.__dict__) for case_alert_link in alert.cases] |
| 1832 | iocs = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 1833 | alert_out = AlertOut( |
| 1834 | id=alert.id, |
| 1835 | alert_creation_time=alert.alert_creation_time, |
| 1836 | time_closed=alert.time_closed, |
| 1837 | alert_name=alert.alert_name, |
| 1838 | alert_description=alert.alert_description, |
| 1839 | status=alert.status, |
| 1840 | customer_code=alert.customer_code, |
| 1841 | source=alert.source, |
| 1842 | assigned_to=alert.assigned_to, |
| 1843 | escalated=alert.escalated, |
| 1844 | comments=comments, |
| 1845 | assets=assets, |
| 1846 | tags=tags, |
| 1847 | linked_cases=linked_cases, |
| 1848 | iocs=iocs, |
| 1849 | ) |
| 1850 | alerts_out.append(alert_out) |
| 1851 | |
| 1852 | # Extract case comments |
| 1853 | case_comments = [CaseCommentBase(**comment.__dict__) for comment in case.comments] |
| 1854 | |
| 1855 | case_out = CaseOut( |
| 1856 | id=case.id, |
| 1857 | case_name=case.case_name, |
| 1858 | case_description=case.case_description, |
| 1859 | assigned_to=case.assigned_to, |
| 1860 | alerts=alerts_out, |
| 1861 | case_creation_time=case.case_creation_time, |
| 1862 | case_status=case.case_status, |
| 1863 | customer_code=case.customer_code, |
| 1864 | notification_invoked_number=case.notification_invoked_number or 0, |
| 1865 | comments=case_comments, |
| 1866 | escalated=case.escalated, |
| 1867 | ) |
| 1868 | cases_out.append(case_out) |
| 1869 | return cases_out |
| 1870 | |
| 1871 | |
| 1872 | async def list_cases_by_assigned_to(assigned_to: str, db: AsyncSession) -> List[CaseOut]: |
| 1873 | result = await db.execute( |
| 1874 | select(Case) |
| 1875 | .where(Case.assigned_to == assigned_to) |
| 1876 | .options( |
| 1877 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.comments), |
| 1878 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.assets), |
| 1879 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 1880 | selectinload(Case.comments), |
| 1881 | ), |
| 1882 | ) |
| 1883 | cases = result.scalars().all() |
| 1884 | cases_out = [] |
| 1885 | for case in cases: |
| 1886 | alerts_out = [] |
| 1887 | for case_alert_link in case.alerts: |
| 1888 | alert = case_alert_link.alert |
| 1889 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 1890 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 1891 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 1892 | alert_out = AlertOut( |
| 1893 | id=alert.id, |
| 1894 | alert_creation_time=alert.alert_creation_time, |
| 1895 | time_closed=alert.time_closed, |
| 1896 | alert_name=alert.alert_name, |
| 1897 | alert_description=alert.alert_description, |
| 1898 | status=alert.status, |
| 1899 | customer_code=alert.customer_code, |
| 1900 | source=alert.source, |
| 1901 | assigned_to=alert.assigned_to, |
| 1902 | escalated=alert.escalated, |
| 1903 | comments=comments, |
| 1904 | assets=assets, |
| 1905 | tags=tags, |
| 1906 | ) |
| 1907 | alerts_out.append(alert_out) |
| 1908 | |
| 1909 | # Handle case comments |
| 1910 | case_comments = [] |
| 1911 | for comment in case.comments: |
| 1912 | case_comment = CaseCommentBase( |
| 1913 | id=comment.id, |
| 1914 | case_id=comment.case_id, |
| 1915 | user_name=comment.user_name, |
| 1916 | comment=comment.comment, |
| 1917 | created_at=comment.created_at, |
| 1918 | ) |
| 1919 | case_comments.append(case_comment) |
| 1920 | |
| 1921 | case_out = CaseOut( |
| 1922 | id=case.id, |
| 1923 | case_name=case.case_name, |
| 1924 | case_description=case.case_description, |
| 1925 | assigned_to=case.assigned_to, |
| 1926 | alerts=alerts_out, |
| 1927 | case_status=case.case_status, |
| 1928 | case_creation_time=case.case_creation_time, |
| 1929 | customer_code=case.customer_code, |
| 1930 | notification_invoked_number=case.notification_invoked_number or 0, |
| 1931 | comments=case_comments, |
| 1932 | escalated=case.escalated, |
| 1933 | ) |
| 1934 | cases_out.append(case_out) |
| 1935 | return cases_out |
| 1936 | |
| 1937 | |
| 1938 | async def list_cases_by_asset_name(asset_name: str, db: AsyncSession) -> List[CaseOut]: |
| 1939 | result = await db.execute( |
| 1940 | select(Case) |
| 1941 | .join(CaseAlertLink) |
| 1942 | .join(Alert) |
| 1943 | .join(Asset) |
| 1944 | .where(Asset.asset_name == asset_name) |
| 1945 | .options( |
| 1946 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.comments), |
| 1947 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.assets), |
| 1948 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 1949 | selectinload(Case.comments), |
| 1950 | ), |
| 1951 | ) |
| 1952 | cases = result.scalars().all() |
| 1953 | cases_out = [] |
| 1954 | for case in cases: |
| 1955 | alerts_out = [] |
| 1956 | for case_alert_link in case.alerts: |
| 1957 | alert = case_alert_link.alert |
| 1958 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 1959 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 1960 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 1961 | alert_out = AlertOut( |
| 1962 | id=alert.id, |
| 1963 | alert_creation_time=alert.alert_creation_time, |
| 1964 | time_closed=alert.time_closed, |
| 1965 | alert_name=alert.alert_name, |
| 1966 | alert_description=alert.alert_description, |
| 1967 | status=alert.status, |
| 1968 | customer_code=alert.customer_code, |
| 1969 | source=alert.source, |
| 1970 | assigned_to=alert.assigned_to, |
| 1971 | escalated=alert.escalated, |
| 1972 | comments=comments, |
| 1973 | assets=assets, |
| 1974 | tags=tags, |
| 1975 | ) |
| 1976 | alerts_out.append(alert_out) |
| 1977 | |
| 1978 | # Handle case comments |
| 1979 | case_comments = [] |
| 1980 | for comment in case.comments: |
| 1981 | case_comment = CaseCommentBase( |
| 1982 | id=comment.id, |
| 1983 | case_id=comment.case_id, |
| 1984 | user_name=comment.user_name, |
| 1985 | comment=comment.comment, |
| 1986 | created_at=comment.created_at, |
| 1987 | ) |
| 1988 | case_comments.append(case_comment) |
| 1989 | |
| 1990 | case_out = CaseOut( |
| 1991 | id=case.id, |
| 1992 | case_name=case.case_name, |
| 1993 | case_description=case.case_description, |
| 1994 | assigned_to=case.assigned_to, |
| 1995 | alerts=alerts_out, |
| 1996 | case_status=case.case_status, |
| 1997 | case_creation_time=case.case_creation_time, |
| 1998 | customer_code=case.customer_code, |
| 1999 | notification_invoked_number=case.notification_invoked_number or 0, |
| 2000 | comments=case_comments, |
| 2001 | escalated=case.escalated, |
| 2002 | ) |
| 2003 | cases_out.append(case_out) |
| 2004 | return cases_out |
| 2005 | |
| 2006 | |
| 2007 | async def list_cases_by_customer_code(customer_code: str, db: AsyncSession) -> List[CaseOut]: |
| 2008 | result = await db.execute( |
| 2009 | select(Case) |
| 2010 | .where(Case.customer_code == customer_code) |
| 2011 | .options( |
| 2012 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.comments), |
| 2013 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.assets), |
| 2014 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2015 | selectinload(Case.comments), |
| 2016 | ), |
| 2017 | ) |
| 2018 | cases = result.scalars().all() |
| 2019 | cases_out = [] |
| 2020 | for case in cases: |
| 2021 | alerts_out = [] |
| 2022 | for case_alert_link in case.alerts: |
| 2023 | alert = case_alert_link.alert |
| 2024 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2025 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2026 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2027 | alert_out = AlertOut( |
| 2028 | id=alert.id, |
| 2029 | alert_creation_time=alert.alert_creation_time, |
| 2030 | time_closed=alert.time_closed, |
| 2031 | alert_name=alert.alert_name, |
| 2032 | alert_description=alert.alert_description, |
| 2033 | status=alert.status, |
| 2034 | customer_code=alert.customer_code, |
| 2035 | source=alert.source, |
| 2036 | assigned_to=alert.assigned_to, |
| 2037 | escalated=alert.escalated, |
| 2038 | comments=comments, |
| 2039 | assets=assets, |
| 2040 | tags=tags, |
| 2041 | ) |
| 2042 | alerts_out.append(alert_out) |
| 2043 | |
| 2044 | # Handle case comments |
| 2045 | case_comments = [] |
| 2046 | for comment in case.comments: |
| 2047 | case_comment = CaseCommentBase( |
| 2048 | id=comment.id, |
| 2049 | case_id=comment.case_id, |
| 2050 | user_name=comment.user_name, |
| 2051 | comment=comment.comment, |
| 2052 | created_at=comment.created_at, |
| 2053 | ) |
| 2054 | case_comments.append(case_comment) |
| 2055 | |
| 2056 | case_out = CaseOut( |
| 2057 | id=case.id, |
| 2058 | case_name=case.case_name, |
| 2059 | case_description=case.case_description, |
| 2060 | assigned_to=case.assigned_to, |
| 2061 | alerts=alerts_out, |
| 2062 | case_status=case.case_status, |
| 2063 | case_creation_time=case.case_creation_time, |
| 2064 | customer_code=case.customer_code, |
| 2065 | notification_invoked_number=case.notification_invoked_number or 0, |
| 2066 | comments=case_comments, |
| 2067 | escalated=case.escalated, |
| 2068 | ) |
| 2069 | cases_out.append(case_out) |
| 2070 | return cases_out |
| 2071 | |
| 2072 | |
| 2073 | async def get_alert_context_by_id(alert_context_id: int, db: AsyncSession) -> AlertContext: |
| 2074 | result = await db.execute(select(AlertContext).where(AlertContext.id == alert_context_id)) |
| 2075 | alert_context = result.scalars().first() |
| 2076 | if not alert_context: |
| 2077 | raise HTTPException(status_code=404, detail="Alert context not found") |
| 2078 | return alert_context |
| 2079 | |
| 2080 | |
| 2081 | async def list_alerts_by_ioc(ioc_value: str, db: AsyncSession, page: int = 1, page_size: int = 25, order: str = "desc") -> List[AlertOut]: |
| 2082 | offset = (page - 1) * page_size |
| 2083 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 2084 | logger.info(f"Listing alerts by IoC {ioc_value}") |
| 2085 | |
| 2086 | result = await db.execute( |
| 2087 | select(Alert) |
| 2088 | .join(AlertToIoC) |
| 2089 | .join(IoC) |
| 2090 | .where(IoC.value == ioc_value) |
| 2091 | .options( |
| 2092 | selectinload(Alert.comments), |
| 2093 | selectinload(Alert.assets), |
| 2094 | selectinload(Alert.cases), |
| 2095 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2096 | selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 2097 | ) |
| 2098 | .order_by(order_by) |
| 2099 | .offset(offset) |
| 2100 | .limit(page_size), |
| 2101 | ) |
| 2102 | |
| 2103 | alerts = result.scalars().all() |
| 2104 | alerts_out = [] |
| 2105 | for alert in alerts: |
| 2106 | comments: List[CommentBase] = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2107 | assets: List[AssetBase] = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2108 | tags: List[AlertTagBase] = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2109 | iocs: List[IoCBase] = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 2110 | alert_out = AlertOut( |
| 2111 | id=alert.id, |
| 2112 | alert_creation_time=alert.alert_creation_time, |
| 2113 | time_closed=alert.time_closed, |
| 2114 | alert_name=alert.alert_name, |
| 2115 | alert_description=alert.alert_description, |
| 2116 | status=alert.status, |
| 2117 | customer_code=alert.customer_code, |
| 2118 | source=alert.source, |
| 2119 | assigned_to=alert.assigned_to, |
| 2120 | escalated=alert.escalated, |
| 2121 | comments=comments, |
| 2122 | assets=assets, |
| 2123 | tags=tags, |
| 2124 | iocs=iocs, |
| 2125 | ) |
| 2126 | alerts_out.append(alert_out) |
| 2127 | return alerts_out |
| 2128 | |
| 2129 | |
| 2130 | async def list_alerts_by_tag(tag: str, db: AsyncSession, page: int = 1, page_size: int = 25, order: str = "desc") -> List[AlertOut]: |
| 2131 | offset = (page - 1) * page_size |
| 2132 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 2133 | |
| 2134 | result = await db.execute( |
| 2135 | select(Alert) |
| 2136 | .join(AlertToTag) |
| 2137 | .join(AlertTag) |
| 2138 | .where(AlertTag.tag == tag) |
| 2139 | .options( |
| 2140 | selectinload(Alert.comments), |
| 2141 | selectinload(Alert.assets), |
| 2142 | selectinload(Alert.cases), |
| 2143 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2144 | selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 2145 | ) |
| 2146 | .order_by(order_by) |
| 2147 | .offset(offset) |
| 2148 | .limit(page_size), |
| 2149 | ) |
| 2150 | alerts = result.scalars().all() |
| 2151 | alerts_out = [] |
| 2152 | for alert in alerts: |
| 2153 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2154 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2155 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2156 | iocs = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 2157 | alert_out = AlertOut( |
| 2158 | id=alert.id, |
| 2159 | alert_creation_time=alert.alert_creation_time, |
| 2160 | time_closed=alert.time_closed, |
| 2161 | alert_name=alert.alert_name, |
| 2162 | alert_description=alert.alert_description, |
| 2163 | status=alert.status, |
| 2164 | customer_code=alert.customer_code, |
| 2165 | source=alert.source, |
| 2166 | assigned_to=alert.assigned_to, |
| 2167 | escalated=alert.escalated, |
| 2168 | comments=comments, |
| 2169 | assets=assets, |
| 2170 | tags=tags, |
| 2171 | iocs=iocs, |
| 2172 | ) |
| 2173 | alerts_out.append(alert_out) |
| 2174 | return alerts_out |
| 2175 | |
| 2176 | |
| 2177 | async def list_alert_by_status(status: str, db: AsyncSession, page: int = 1, page_size: int = 25, order: str = "desc") -> List[AlertOut]: |
| 2178 | offset = (page - 1) * page_size |
| 2179 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 2180 | |
| 2181 | result = await db.execute( |
| 2182 | select(Alert) |
| 2183 | .where(Alert.status == status) |
| 2184 | .options( |
| 2185 | selectinload(Alert.comments), |
| 2186 | selectinload(Alert.assets), |
| 2187 | selectinload(Alert.cases), |
| 2188 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2189 | selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 2190 | ) |
| 2191 | .order_by(order_by) |
| 2192 | .offset(offset) |
| 2193 | .limit(page_size), |
| 2194 | ) |
| 2195 | |
| 2196 | alerts = result.scalars().all() |
| 2197 | alerts_out = [] |
| 2198 | for alert in alerts: |
| 2199 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2200 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2201 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2202 | iocs = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 2203 | alert_out = AlertOut( |
| 2204 | id=alert.id, |
| 2205 | alert_creation_time=alert.alert_creation_time, |
| 2206 | time_closed=alert.time_closed, |
| 2207 | alert_name=alert.alert_name, |
| 2208 | alert_description=alert.alert_description, |
| 2209 | status=alert.status, |
| 2210 | customer_code=alert.customer_code, |
| 2211 | source=alert.source, |
| 2212 | assigned_to=alert.assigned_to, |
| 2213 | escalated=alert.escalated, |
| 2214 | comments=comments, |
| 2215 | assets=assets, |
| 2216 | tags=tags, |
| 2217 | iocs=iocs, |
| 2218 | ) |
| 2219 | alerts_out.append(alert_out) |
| 2220 | return alerts_out |
| 2221 | |
| 2222 | |
| 2223 | async def list_alerts_by_asset_name( |
| 2224 | asset_name: str, |
| 2225 | db: AsyncSession, |
| 2226 | page: int = 1, |
| 2227 | page_size: int = 25, |
| 2228 | order: str = "desc", |
| 2229 | ) -> List[AlertOut]: |
| 2230 | offset = (page - 1) * page_size |
| 2231 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 2232 | |
| 2233 | result = await db.execute( |
| 2234 | select(Alert) |
| 2235 | .join(Asset) |
| 2236 | .where(Asset.asset_name == asset_name) |
| 2237 | .options( |
| 2238 | selectinload(Alert.comments), |
| 2239 | selectinload(Alert.assets), |
| 2240 | selectinload(Alert.cases), |
| 2241 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2242 | ) |
| 2243 | .order_by(order_by) |
| 2244 | .offset(offset) |
| 2245 | .limit(page_size), |
| 2246 | ) |
| 2247 | alerts = result.scalars().all() |
| 2248 | alerts_out = [] |
| 2249 | for alert in alerts: |
| 2250 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2251 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2252 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2253 | alert_out = AlertOut( |
| 2254 | id=alert.id, |
| 2255 | alert_creation_time=alert.alert_creation_time, |
| 2256 | time_closed=alert.time_closed, |
| 2257 | alert_name=alert.alert_name, |
| 2258 | alert_description=alert.alert_description, |
| 2259 | status=alert.status, |
| 2260 | customer_code=alert.customer_code, |
| 2261 | source=alert.source, |
| 2262 | assigned_to=alert.assigned_to, |
| 2263 | escalated=alert.escalated, |
| 2264 | comments=comments, |
| 2265 | assets=assets, |
| 2266 | tags=tags, |
| 2267 | ) |
| 2268 | alerts_out.append(alert_out) |
| 2269 | return alerts_out |
| 2270 | |
| 2271 | |
| 2272 | async def list_alert_by_assigned_to( |
| 2273 | assigned_to: str, |
| 2274 | db: AsyncSession, |
| 2275 | page: int = 1, |
| 2276 | page_size: int = 25, |
| 2277 | order: str = "desc", |
| 2278 | ) -> List[AlertOut]: |
| 2279 | offset = (page - 1) * page_size |
| 2280 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 2281 | |
| 2282 | result = await db.execute( |
| 2283 | select(Alert) |
| 2284 | .where(Alert.assigned_to == assigned_to) |
| 2285 | .options( |
| 2286 | selectinload(Alert.comments), |
| 2287 | selectinload(Alert.assets), |
| 2288 | selectinload(Alert.cases), |
| 2289 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2290 | ) |
| 2291 | .order_by(order_by) |
| 2292 | .offset(offset) |
| 2293 | .limit(page_size), |
| 2294 | ) |
| 2295 | alerts = result.scalars().all() |
| 2296 | alerts_out = [] |
| 2297 | for alert in alerts: |
| 2298 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2299 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2300 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2301 | alert_out = AlertOut( |
| 2302 | id=alert.id, |
| 2303 | alert_creation_time=alert.alert_creation_time, |
| 2304 | time_closed=alert.time_closed, |
| 2305 | alert_name=alert.alert_name, |
| 2306 | alert_description=alert.alert_description, |
| 2307 | status=alert.status, |
| 2308 | customer_code=alert.customer_code, |
| 2309 | source=alert.source, |
| 2310 | assigned_to=alert.assigned_to, |
| 2311 | escalated=alert.escalated, |
| 2312 | comments=comments, |
| 2313 | assets=assets, |
| 2314 | tags=tags, |
| 2315 | ) |
| 2316 | alerts_out.append(alert_out) |
| 2317 | return alerts_out |
| 2318 | |
| 2319 | |
| 2320 | async def list_alerts_by_title( |
| 2321 | alert_title: str, |
| 2322 | db: AsyncSession, |
| 2323 | page: int = 1, |
| 2324 | page_size: int = 25, |
| 2325 | order: str = "desc", |
| 2326 | ) -> List[AlertOut]: |
| 2327 | offset = (page - 1) * page_size |
| 2328 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 2329 | |
| 2330 | result = await db.execute( |
| 2331 | select(Alert) |
| 2332 | .where(Alert.alert_name.like(f"%{alert_title}%")) |
| 2333 | .options( |
| 2334 | selectinload(Alert.comments), |
| 2335 | selectinload(Alert.assets), |
| 2336 | selectinload(Alert.cases), |
| 2337 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2338 | ) |
| 2339 | .order_by(order_by) |
| 2340 | .offset(offset) |
| 2341 | .limit(page_size), |
| 2342 | ) |
| 2343 | alerts = result.scalars().all() |
| 2344 | alerts_out = [] |
| 2345 | for alert in alerts: |
| 2346 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2347 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2348 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2349 | alert_out = AlertOut( |
| 2350 | id=alert.id, |
| 2351 | alert_creation_time=alert.alert_creation_time, |
| 2352 | time_closed=alert.time_closed, |
| 2353 | alert_name=alert.alert_name, |
| 2354 | alert_description=alert.alert_description, |
| 2355 | status=alert.status, |
| 2356 | customer_code=alert.customer_code, |
| 2357 | source=alert.source, |
| 2358 | assigned_to=alert.assigned_to, |
| 2359 | escalated=alert.escalated, |
| 2360 | comments=comments, |
| 2361 | assets=assets, |
| 2362 | tags=tags, |
| 2363 | ) |
| 2364 | alerts_out.append(alert_out) |
| 2365 | return alerts_out |
| 2366 | |
| 2367 | |
| 2368 | async def list_alerts_by_customer_code( |
| 2369 | customer_code: str, |
| 2370 | db: AsyncSession, |
| 2371 | page: int = 1, |
| 2372 | page_size: int = 25, |
| 2373 | order: str = "desc", |
| 2374 | ) -> List[AlertOut]: |
| 2375 | offset = (page - 1) * page_size |
| 2376 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 2377 | |
| 2378 | result = await db.execute( |
| 2379 | select(Alert) |
| 2380 | .where(Alert.customer_code == customer_code) |
| 2381 | .options( |
| 2382 | selectinload(Alert.comments), |
| 2383 | selectinload(Alert.assets), |
| 2384 | selectinload(Alert.cases), |
| 2385 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2386 | ) |
| 2387 | .order_by(order_by) |
| 2388 | .offset(offset) |
| 2389 | .limit(page_size), |
| 2390 | ) |
| 2391 | alerts = result.scalars().all() |
| 2392 | alerts_out = [] |
| 2393 | for alert in alerts: |
| 2394 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2395 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2396 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2397 | alert_out = AlertOut( |
| 2398 | id=alert.id, |
| 2399 | alert_creation_time=alert.alert_creation_time, |
| 2400 | time_closed=alert.time_closed, |
| 2401 | alert_name=alert.alert_name, |
| 2402 | alert_description=alert.alert_description, |
| 2403 | status=alert.status, |
| 2404 | customer_code=alert.customer_code, |
| 2405 | source=alert.source, |
| 2406 | assigned_to=alert.assigned_to, |
| 2407 | escalated=alert.escalated, |
| 2408 | comments=comments, |
| 2409 | assets=assets, |
| 2410 | tags=tags, |
| 2411 | ) |
| 2412 | alerts_out.append(alert_out) |
| 2413 | return alerts_out |
| 2414 | |
| 2415 | |
| 2416 | async def list_alerts_by_source( |
| 2417 | source: str, |
| 2418 | db: AsyncSession, |
| 2419 | page: int = 1, |
| 2420 | page_size: int = 25, |
| 2421 | order: str = "desc", |
| 2422 | ) -> List[AlertOut]: |
| 2423 | offset = (page - 1) * page_size |
| 2424 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 2425 | |
| 2426 | result = await db.execute( |
| 2427 | select(Alert) |
| 2428 | .where(Alert.source == source) |
| 2429 | .options( |
| 2430 | selectinload(Alert.comments), |
| 2431 | selectinload(Alert.assets), |
| 2432 | selectinload(Alert.cases), |
| 2433 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2434 | ) |
| 2435 | .order_by(order_by) |
| 2436 | .offset(offset) |
| 2437 | .limit(page_size), |
| 2438 | ) |
| 2439 | alerts = result.scalars().all() |
| 2440 | alerts_out = [] |
| 2441 | for alert in alerts: |
| 2442 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2443 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2444 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2445 | alert_out = AlertOut( |
| 2446 | id=alert.id, |
| 2447 | alert_creation_time=alert.alert_creation_time, |
| 2448 | time_closed=alert.time_closed, |
| 2449 | alert_name=alert.alert_name, |
| 2450 | alert_description=alert.alert_description, |
| 2451 | status=alert.status, |
| 2452 | customer_code=alert.customer_code, |
| 2453 | source=alert.source, |
| 2454 | assigned_to=alert.assigned_to, |
| 2455 | escalated=alert.escalated, |
| 2456 | comments=comments, |
| 2457 | assets=assets, |
| 2458 | tags=tags, |
| 2459 | ) |
| 2460 | alerts_out.append(alert_out) |
| 2461 | return alerts_out |
| 2462 | |
| 2463 | |
| 2464 | async def list_alerts_multiple_filters( |
| 2465 | db: AsyncSession, |
| 2466 | assigned_to: Optional[str] = None, |
| 2467 | alert_title: Optional[str] = None, |
| 2468 | customer_code: Optional[str] = None, |
| 2469 | customer_codes: Optional[List[str]] = None, |
| 2470 | source: Optional[str] = None, |
| 2471 | asset_name: Optional[str] = None, |
| 2472 | status: Optional[str] = None, |
| 2473 | tags: Optional[List[str]] = None, |
| 2474 | ioc_value: Optional[str] = None, |
| 2475 | page: int = 1, |
| 2476 | page_size: int = 25, |
| 2477 | order: str = "desc", |
| 2478 | user: Optional[User] = None, # New parameter for tag filtering |
| 2479 | ) -> List[AlertOut]: |
| 2480 | """List alerts with multiple filters including tag-based RBAC. |
| 2481 | |
| 2482 | ``customer_code`` filters to a single customer; ``customer_codes`` filters to |
| 2483 | a set (used to constrain scoped users to their accessible customers — passing |
| 2484 | the caller's full accessible set prevents cross-tenant disclosure). |
| 2485 | """ |
| 2486 | from sqlalchemy import and_ |
| 2487 | from sqlalchemy import exists |
| 2488 | from sqlalchemy import or_ |
| 2489 | |
| 2490 | offset = (page - 1) * page_size |
| 2491 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 2492 | |
| 2493 | # Build dynamic filters |
| 2494 | filters = [] |
| 2495 | if assigned_to: |
| 2496 | filters.append(Alert.assigned_to == assigned_to) |
| 2497 | if alert_title: |
| 2498 | filters.append(Alert.alert_name.like(f"%{alert_title}%")) |
| 2499 | if customer_code: |
| 2500 | filters.append(Alert.customer_code == customer_code) |
| 2501 | if customer_codes: |
| 2502 | filters.append(Alert.customer_code.in_(customer_codes)) |
| 2503 | if source: |
| 2504 | filters.append(Alert.source == source) |
| 2505 | if asset_name: |
| 2506 | filters.append(Asset.asset_name == asset_name) |
| 2507 | if status: |
| 2508 | filters.append(Alert.status == status) |
| 2509 | if tags: |
| 2510 | filters.append(AlertTag.tag.in_(tags)) |
| 2511 | if ioc_value: |
| 2512 | filters.append(IoC.value == ioc_value) |
| 2513 | |
| 2514 | # Apply tag-based RBAC filtering if user is provided |
| 2515 | if user: |
| 2516 | tag_filters = await tag_access_handler.build_alert_query_filters(user, db) |
| 2517 | accessible_tags = tag_filters["accessible_tags"] |
| 2518 | |
| 2519 | if "*" not in accessible_tags: |
| 2520 | tag_conditions = [] |
| 2521 | |
| 2522 | if accessible_tags: |
| 2523 | # Alerts that have at least one accessible tag |
| 2524 | has_accessible_tag = exists( |
| 2525 | select(AlertToTag.alert_id).where( |
| 2526 | and_( |
| 2527 | AlertToTag.alert_id == Alert.id, |
| 2528 | AlertToTag.tag_id.in_(accessible_tags), |
| 2529 | ), |
| 2530 | ), |
| 2531 | ) |
| 2532 | tag_conditions.append(has_accessible_tag) |
| 2533 | |
| 2534 | if tag_filters["include_untagged"]: |
| 2535 | # Include untagged alerts |
| 2536 | is_untagged = ~exists( |
| 2537 | select(AlertToTag.alert_id).where(AlertToTag.alert_id == Alert.id), |
| 2538 | ) |
| 2539 | tag_conditions.append(is_untagged) |
| 2540 | |
| 2541 | if tag_conditions: |
| 2542 | filters.append(or_(*tag_conditions)) |
| 2543 | else: |
| 2544 | # No accessible tags and untagged not allowed - return empty |
| 2545 | return [] |
| 2546 | |
| 2547 | # Build the query with dynamic filters |
| 2548 | query = ( |
| 2549 | select(Alert) |
| 2550 | .distinct(Alert.id) |
| 2551 | .join(Asset, Asset.alert_linked == Alert.id, isouter=True) |
| 2552 | .join(AlertToTag, AlertToTag.alert_id == Alert.id, isouter=True) |
| 2553 | .join(AlertTag, AlertToTag.tag_id == AlertTag.id, isouter=True) |
| 2554 | .join(AlertToIoC, AlertToIoC.alert_id == Alert.id, isouter=True) |
| 2555 | .join(IoC, AlertToIoC.ioc_id == IoC.id, isouter=True) |
| 2556 | .where(*filters) |
| 2557 | .options( |
| 2558 | selectinload(Alert.comments), |
| 2559 | selectinload(Alert.assets), |
| 2560 | selectinload(Alert.cases).selectinload(CaseAlertLink.case), |
| 2561 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2562 | selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 2563 | ) |
| 2564 | .order_by(order_by) |
| 2565 | .offset(offset) |
| 2566 | .limit(page_size) |
| 2567 | ) |
| 2568 | |
| 2569 | result = await db.execute(query) |
| 2570 | alerts = result.scalars().all() |
| 2571 | |
| 2572 | alerts_out = [] |
| 2573 | for alert in alerts: |
| 2574 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2575 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2576 | tags_out = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2577 | iocs = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 2578 | linked_cases = [LinkedCaseCreate(**case_alert_link.case.__dict__) for case_alert_link in alert.cases] |
| 2579 | alert_out = AlertOut( |
| 2580 | id=alert.id, |
| 2581 | alert_creation_time=alert.alert_creation_time, |
| 2582 | time_closed=alert.time_closed, |
| 2583 | alert_name=alert.alert_name, |
| 2584 | alert_description=alert.alert_description, |
| 2585 | status=alert.status, |
| 2586 | customer_code=alert.customer_code, |
| 2587 | source=alert.source, |
| 2588 | assigned_to=alert.assigned_to, |
| 2589 | escalated=alert.escalated, |
| 2590 | comments=comments, |
| 2591 | assets=assets, |
| 2592 | tags=tags_out, |
| 2593 | iocs=iocs, |
| 2594 | linked_cases=linked_cases, |
| 2595 | ) |
| 2596 | alerts_out.append(alert_out) |
| 2597 | |
| 2598 | return alerts_out |
| 2599 | |
| 2600 | |
| 2601 | async def list_alerts_for_user( |
| 2602 | user: User, |
| 2603 | session: AsyncSession, |
| 2604 | page: int = 1, |
| 2605 | page_size: int = 25, |
| 2606 | order: str = "desc", |
| 2607 | customer_codes: Optional[List[str]] = None, |
| 2608 | ) -> List[AlertOut]: |
| 2609 | """List alerts filtered by user's customer access and tag access""" |
| 2610 | from sqlalchemy import and_ |
| 2611 | from sqlalchemy import exists |
| 2612 | from sqlalchemy import or_ |
| 2613 | |
| 2614 | offset = (page - 1) * page_size |
| 2615 | order_by = asc(Alert.id) if order == "asc" else desc(Alert.id) |
| 2616 | |
| 2617 | # Start building the query |
| 2618 | base_query = select(Alert).options( |
| 2619 | selectinload(Alert.comments), |
| 2620 | selectinload(Alert.assets), |
| 2621 | selectinload(Alert.cases).selectinload(CaseAlertLink.case), |
| 2622 | selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2623 | selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 2624 | ) |
| 2625 | |
| 2626 | filters = [] |
| 2627 | |
| 2628 | # 1. Apply customer filtering |
| 2629 | accessible_customers = await customer_access_handler.resolve_effective_customers(user, customer_codes, session) |
| 2630 | if "*" not in accessible_customers: |
| 2631 | filters.append(Alert.customer_code.in_(accessible_customers)) |
| 2632 | |
| 2633 | # 2. Apply tag filtering (new) |
| 2634 | tag_filters = await tag_access_handler.build_alert_query_filters(user, session) |
| 2635 | accessible_tags = tag_filters["accessible_tags"] |
| 2636 | |
| 2637 | if "*" not in accessible_tags: |
| 2638 | # User has tag restrictions |
| 2639 | tag_conditions = [] |
| 2640 | |
| 2641 | if accessible_tags: |
| 2642 | # Alerts that have at least one accessible tag |
| 2643 | has_accessible_tag = exists( |
| 2644 | select(AlertToTag.alert_id).where( |
| 2645 | and_( |
| 2646 | AlertToTag.alert_id == Alert.id, |
| 2647 | AlertToTag.tag_id.in_(accessible_tags), |
| 2648 | ), |
| 2649 | ), |
| 2650 | ) |
| 2651 | tag_conditions.append(has_accessible_tag) |
| 2652 | |
| 2653 | if tag_filters["include_untagged"]: |
| 2654 | # Include untagged alerts |
| 2655 | is_untagged = ~exists( |
| 2656 | select(AlertToTag.alert_id).where(AlertToTag.alert_id == Alert.id), |
| 2657 | ) |
| 2658 | tag_conditions.append(is_untagged) |
| 2659 | |
| 2660 | if tag_conditions: |
| 2661 | filters.append(or_(*tag_conditions)) |
| 2662 | else: |
| 2663 | # No accessible tags and untagged not allowed - return empty |
| 2664 | return [] |
| 2665 | |
| 2666 | # Apply all filters |
| 2667 | if filters: |
| 2668 | base_query = base_query.where(and_(*filters)) |
| 2669 | |
| 2670 | # Apply ordering and pagination |
| 2671 | final_query = base_query.order_by(order_by).offset(offset).limit(page_size) |
| 2672 | result = await session.execute(final_query) |
| 2673 | alerts = result.scalars().all() |
| 2674 | |
| 2675 | # Convert to AlertOut objects |
| 2676 | alerts_out = [] |
| 2677 | for alert in alerts: |
| 2678 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2679 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2680 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2681 | iocs = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 2682 | linked_cases = [LinkedCaseCreate(**case_alert_link.case.__dict__) for case_alert_link in alert.cases] |
| 2683 | |
| 2684 | alert_out = AlertOut( |
| 2685 | id=alert.id, |
| 2686 | alert_creation_time=alert.alert_creation_time, |
| 2687 | time_closed=alert.time_closed, |
| 2688 | alert_name=alert.alert_name, |
| 2689 | alert_description=alert.alert_description, |
| 2690 | status=alert.status, |
| 2691 | customer_code=alert.customer_code, |
| 2692 | source=alert.source, |
| 2693 | assigned_to=alert.assigned_to, |
| 2694 | escalated=alert.escalated, |
| 2695 | comments=comments, |
| 2696 | assets=assets, |
| 2697 | tags=tags, |
| 2698 | iocs=iocs, |
| 2699 | linked_cases=linked_cases, |
| 2700 | ) |
| 2701 | alerts_out.append(alert_out) |
| 2702 | |
| 2703 | return alerts_out |
| 2704 | |
| 2705 | |
| 2706 | async def case_total_for_user(user: User, session: AsyncSession, customer_codes: Optional[List[str]] = None) -> int: |
| 2707 | """Get total cases count with customer filtering""" |
| 2708 | base_query = select(func.count(Case.id)) |
| 2709 | |
| 2710 | accessible_customers = await customer_access_handler.resolve_effective_customers(user, customer_codes, session) |
| 2711 | if "*" not in accessible_customers: |
| 2712 | base_query = base_query.where(Case.customer_code.in_(accessible_customers)) |
| 2713 | |
| 2714 | result = await session.execute(base_query) |
| 2715 | return result.scalar_one() |
| 2716 | |
| 2717 | |
| 2718 | async def cases_open_for_user(user: User, session: AsyncSession, customer_codes: Optional[List[str]] = None) -> int: |
| 2719 | """Get open cases count with customer filtering""" |
| 2720 | base_query = select(func.count(Case.id)).where(Case.case_status == "OPEN") |
| 2721 | |
| 2722 | accessible_customers = await customer_access_handler.resolve_effective_customers(user, customer_codes, session) |
| 2723 | if "*" not in accessible_customers: |
| 2724 | base_query = base_query.where(Case.customer_code.in_(accessible_customers)) |
| 2725 | |
| 2726 | result = await session.execute(base_query) |
| 2727 | return result.scalar_one() |
| 2728 | |
| 2729 | |
| 2730 | async def cases_in_progress_for_user(user: User, session: AsyncSession, customer_codes: Optional[List[str]] = None) -> int: |
| 2731 | """Get in-progress cases count with customer filtering""" |
| 2732 | base_query = select(func.count(Case.id)).where(Case.case_status == "IN_PROGRESS") |
| 2733 | |
| 2734 | accessible_customers = await customer_access_handler.resolve_effective_customers(user, customer_codes, session) |
| 2735 | if "*" not in accessible_customers: |
| 2736 | base_query = base_query.where(Case.customer_code.in_(accessible_customers)) |
| 2737 | |
| 2738 | result = await session.execute(base_query) |
| 2739 | return result.scalar_one() |
| 2740 | |
| 2741 | |
| 2742 | async def cases_closed_for_user(user: User, session: AsyncSession, customer_codes: Optional[List[str]] = None) -> int: |
| 2743 | """Get closed cases count with customer filtering""" |
| 2744 | base_query = select(func.count(Case.id)).where(Case.case_status == "CLOSED") |
| 2745 | |
| 2746 | accessible_customers = await customer_access_handler.resolve_effective_customers(user, customer_codes, session) |
| 2747 | if "*" not in accessible_customers: |
| 2748 | base_query = base_query.where(Case.customer_code.in_(accessible_customers)) |
| 2749 | |
| 2750 | result = await session.execute(base_query) |
| 2751 | return result.scalar_one() |
| 2752 | |
| 2753 | |
| 2754 | async def list_cases_for_user( |
| 2755 | user: User, |
| 2756 | session: AsyncSession, |
| 2757 | page: int = 1, |
| 2758 | page_size: int = 25, |
| 2759 | order: str = "desc", |
| 2760 | customer_codes: Optional[List[str]] = None, |
| 2761 | ) -> List[CaseOut]: |
| 2762 | """List cases filtered by user's customer access with pagination""" |
| 2763 | |
| 2764 | offset = (page - 1) * page_size |
| 2765 | order_by = asc(Case.id) if order == "asc" else desc(Case.id) |
| 2766 | |
| 2767 | base_query = select(Case).options( |
| 2768 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.comments), |
| 2769 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.assets), |
| 2770 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.tags).selectinload(AlertToTag.tag), |
| 2771 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.cases).selectinload(CaseAlertLink.case), |
| 2772 | selectinload(Case.alerts).selectinload(CaseAlertLink.alert).selectinload(Alert.iocs).selectinload(AlertToIoC.ioc), |
| 2773 | selectinload(Case.comments), |
| 2774 | ) |
| 2775 | |
| 2776 | # Apply customer filtering (optionally narrowed to a requested subset) |
| 2777 | filtered_query = await customer_access_handler.filter_query_by_customer_access( |
| 2778 | user, |
| 2779 | session, |
| 2780 | base_query, |
| 2781 | Case.customer_code, |
| 2782 | requested_customers=customer_codes, |
| 2783 | ) |
| 2784 | |
| 2785 | # Apply ordering and pagination |
| 2786 | final_query = filtered_query.order_by(order_by).offset(offset).limit(page_size) |
| 2787 | |
| 2788 | result = await session.execute(final_query) |
| 2789 | cases = result.scalars().all() |
| 2790 | |
| 2791 | # Convert to CaseOut objects (using same logic as list_cases) |
| 2792 | cases_out = [] |
| 2793 | for case in cases: |
| 2794 | alerts_out = [] |
| 2795 | for case_alert_link in case.alerts: |
| 2796 | alert = case_alert_link.alert |
| 2797 | comments = [CommentBase(**comment.__dict__) for comment in alert.comments] |
| 2798 | assets = [AssetBase(**asset.__dict__) for asset in alert.assets] |
| 2799 | tags = [AlertTagBase(**alert_to_tag.tag.__dict__) for alert_to_tag in alert.tags] |
| 2800 | linked_cases = [LinkedCaseCreate(**case_alert_link.case.__dict__) for case_alert_link in alert.cases] |
| 2801 | iocs = [IoCBase(**alert_to_ioc.ioc.__dict__) for alert_to_ioc in alert.iocs] |
| 2802 | alert_out = AlertOut( |
| 2803 | id=alert.id, |
| 2804 | alert_creation_time=alert.alert_creation_time, |
| 2805 | time_closed=alert.time_closed, |
| 2806 | alert_name=alert.alert_name, |
| 2807 | alert_description=alert.alert_description, |
| 2808 | status=alert.status, |
| 2809 | customer_code=alert.customer_code, |
| 2810 | source=alert.source, |
| 2811 | assigned_to=alert.assigned_to, |
| 2812 | escalated=alert.escalated, |
| 2813 | comments=comments, |
| 2814 | assets=assets, |
| 2815 | tags=tags, |
| 2816 | linked_cases=linked_cases, |
| 2817 | iocs=iocs, |
| 2818 | ) |
| 2819 | alerts_out.append(alert_out) |
| 2820 | |
| 2821 | # Handle case comments |
| 2822 | case_comments = [] |
| 2823 | for comment in case.comments: |
| 2824 | case_comment = CaseCommentBase( |
| 2825 | id=comment.id, |
| 2826 | case_id=comment.case_id, |
| 2827 | user_name=comment.user_name, |
| 2828 | comment=comment.comment, |
| 2829 | created_at=comment.created_at, |
| 2830 | ) |
| 2831 | case_comments.append(case_comment) |
| 2832 | |
| 2833 | case_out = CaseOut( |
| 2834 | id=case.id, |
| 2835 | case_name=case.case_name, |
| 2836 | case_description=case.case_description, |
| 2837 | assigned_to=case.assigned_to, |
| 2838 | alerts=alerts_out, |
| 2839 | case_creation_time=case.case_creation_time, |
| 2840 | case_status=case.case_status, |
| 2841 | customer_code=case.customer_code, |
| 2842 | notification_invoked_number=case.notification_invoked_number or 0, |
| 2843 | comments=case_comments, |
| 2844 | escalated=case.escalated, |
| 2845 | ) |
| 2846 | cases_out.append(case_out) |
| 2847 | return cases_out |
| 2848 | |
| 2849 | |
| 2850 | async def delete_comments(alert_id: int, db: AsyncSession): |
| 2851 | result = await db.execute(select(Comment).where(Comment.alert_id == alert_id)) |
| 2852 | comments = result.scalars().all() |
| 2853 | for comment in comments: |
| 2854 | await db.execute(delete(Comment).where(Comment.id == comment.id)) |
| 2855 | |
| 2856 | |
| 2857 | async def delete_assets(alert_id: int, db: AsyncSession): |
| 2858 | result = await db.execute(select(Asset).where(Asset.alert_linked == alert_id)) |
| 2859 | assets = result.scalars().all() |
| 2860 | for asset in assets: |
| 2861 | await db.execute(delete(Asset).where(Asset.id == asset.id)) |
| 2862 | context_result = await db.execute(select(Asset).where(Asset.alert_context_id == asset.alert_context_id)) |
| 2863 | if not context_result.scalars().all(): |
| 2864 | await db.execute(delete(AlertContext).where(AlertContext.id == asset.alert_context_id)) |
| 2865 | |
| 2866 | |
| 2867 | async def delete_tags(alert_id: int, db: AsyncSession): |
| 2868 | result = await db.execute(select(AlertToTag).where(AlertToTag.alert_id == alert_id)) |
| 2869 | alert_to_tags = result.scalars().all() |
| 2870 | for alert_to_tag in alert_to_tags: |
| 2871 | await db.execute( |
| 2872 | delete(AlertToTag).where((AlertToTag.alert_id == alert_to_tag.alert_id) & (AlertToTag.tag_id == alert_to_tag.tag_id)), |
| 2873 | ) |
| 2874 | |
| 2875 | |
| 2876 | async def delete_iocs(alert_id: int, db: AsyncSession): |
| 2877 | result = await db.execute(select(AlertToIoC).where(AlertToIoC.alert_id == alert_id)) |
| 2878 | alert_to_iocs = result.scalars().all() |
| 2879 | for alert_to_ioc in alert_to_iocs: |
| 2880 | await db.execute( |
| 2881 | delete(AlertToIoC).where((AlertToIoC.alert_id == alert_to_ioc.alert_id) & (AlertToIoC.ioc_id == alert_to_ioc.ioc_id)), |
| 2882 | ) |
| 2883 | |
| 2884 | |
| 2885 | async def is_alert_linked_to_case(alert_id: int, db: AsyncSession) -> bool: |
| 2886 | result = await db.execute(select(CaseAlertLink).where(CaseAlertLink.alert_id == alert_id)) |
| 2887 | linked_cases = result.scalars().all() |
| 2888 | |
| 2889 | if linked_cases: |
| 2890 | raise HTTPException(status_code=400, detail="Alert is linked to a case, and cannot be deleted") |
| 2891 | return False |
| 2892 | |
| 2893 | |
| 2894 | async def delete_alert(alert_id: int, db: AsyncSession): |
| 2895 | """ |
| 2896 | Delete an alert from the database. |
| 2897 | |
| 2898 | Args: |
| 2899 | alert_id (int): The ID of the alert to be deleted. |
| 2900 | db (AsyncSession): The database session. |
| 2901 | |
| 2902 | Raises: |
| 2903 | HTTPException: If the alert is not found or there is an error deleting the alert. |
| 2904 | |
| 2905 | """ |
| 2906 | logger.info(f"Deleting alert {alert_id}") |
| 2907 | result = await db.execute( |
| 2908 | select(Alert) |
| 2909 | .options( |
| 2910 | selectinload(Alert.comments), |
| 2911 | selectinload(Alert.assets).selectinload(Asset.alert_context), |
| 2912 | selectinload(Alert.tags), |
| 2913 | selectinload(Alert.iocs), |
| 2914 | ) |
| 2915 | .where(Alert.id == alert_id), |
| 2916 | ) |
| 2917 | alert = result.scalars().first() |
| 2918 | if not alert: |
| 2919 | raise HTTPException(status_code=404, detail="Alert not found") |
| 2920 | |
| 2921 | await delete_comments(alert_id, db) |
| 2922 | await delete_assets(alert_id, db) |
| 2923 | await delete_tags(alert_id, db) |
| 2924 | await delete_iocs(alert_id, db) |
| 2925 | await db.execute(delete(ThresholdAlertMetadata).where(ThresholdAlertMetadata.alert_id == alert_id)) |
| 2926 | |
| 2927 | # Orphan any case tasks that were stamped with this alert id (matches the |
| 2928 | # alert-unlink behavior — tasks survive as case-wide so investigation |
| 2929 | # evidence isn't lost when an alert is purged). Spans all cases. |
| 2930 | await db.execute( |
| 2931 | update(CaseTask).where(CaseTask.alert_id == alert_id).values(alert_id=None, updated_at=datetime.utcnow()), |
| 2932 | ) |
| 2933 | |
| 2934 | await db.execute(delete(Alert).where(Alert.id == alert.id)) |
| 2935 | |
| 2936 | try: |
| 2937 | await db.commit() |
| 2938 | except IntegrityError: |
| 2939 | await db.rollback() |
| 2940 | raise HTTPException(status_code=400, detail="Error deleting alert") |
| 2941 | |
| 2942 | |
| 2943 | async def delete_case(case_id: int, db: AsyncSession): |
| 2944 | """ |
| 2945 | Delete a case and all its related records (comments, alert links, data store files, etc.) |
| 2946 | |
| 2947 | Args: |
| 2948 | case_id: The ID of the case to delete |
| 2949 | db: Database session |
| 2950 | """ |
| 2951 | try: |
| 2952 | # 1. Delete all case comments first |
| 2953 | logger.info(f"Deleting case comments for case {case_id}") |
| 2954 | await db.execute(delete(CaseComment).where(CaseComment.case_id == case_id)) |
| 2955 | |
| 2956 | # 2. Delete all case alert links |
| 2957 | logger.info(f"Deleting case alert links for case {case_id}") |
| 2958 | await db.execute(delete(CaseAlertLink).where(CaseAlertLink.case_id == case_id)) |
| 2959 | |
| 2960 | # 3. Delete case tasks and their audit-log events (both FK to Case). |
| 2961 | # Tasks first to avoid leaving dangling task_id references in payloads, |
| 2962 | # though the events table doesn't FK to case_task directly so order is |
| 2963 | # only a logical preference, not a correctness requirement. |
| 2964 | logger.info(f"Deleting case tasks for case {case_id}") |
| 2965 | await db.execute(delete(CaseTask).where(CaseTask.case_id == case_id)) |
| 2966 | logger.info(f"Deleting case timeline events for case {case_id}") |
| 2967 | await db.execute(delete(CaseEvent).where(CaseEvent.case_id == case_id)) |
| 2968 | |
| 2969 | # 4. Delete all data store files associated with the case |
| 2970 | logger.info(f"Deleting data store files for case {case_id}") |
| 2971 | files = await list_files_by_case_id(case_id, db) |
| 2972 | for file in files: |
| 2973 | try: |
| 2974 | await delete_file_from_case(case_id, file.file_name, db) |
| 2975 | except Exception as e: |
| 2976 | logger.warning(f"Failed to delete file {file.file_name} from case {case_id}: {e}") |
| 2977 | |
| 2978 | # 5. Finally delete the case itself |
| 2979 | logger.info(f"Deleting case {case_id}") |
| 2980 | await db.execute(delete(Case).where(Case.id == case_id)) |
| 2981 | |
| 2982 | # Commit all changes |
| 2983 | await db.commit() |
| 2984 | logger.info(f"Successfully deleted case {case_id} and all related records") |
| 2985 | |
| 2986 | except Exception as e: |
| 2987 | logger.error(f"Error deleting case {case_id}: {e}") |
| 2988 | await db.rollback() |
| 2989 | raise HTTPException(status_code=500, detail=f"Failed to delete case: {str(e)}") |
| 2990 | |
| 2991 | |
| 2992 | async def list_all_files(db: AsyncSession) -> List[CaseDataStore]: |
| 2993 | query = select(CaseDataStore) |
| 2994 | result = await db.execute(query) |
| 2995 | return result.scalars().all() |
| 2996 | |
| 2997 | |
| 2998 | async def list_files_by_case_id(case_id: int, db: AsyncSession) -> List[CaseDataStore]: |
| 2999 | query = select(CaseDataStore).where(CaseDataStore.case_id == case_id) |
| 3000 | result = await db.execute(query) |
| 3001 | return result.scalars().all() |
| 3002 | |
| 3003 | |
| 3004 | async def file_exists(case_id: int, file_name: str, db: AsyncSession) -> bool: |
| 3005 | query = select(CaseDataStore).where(CaseDataStore.case_id == case_id, CaseDataStore.file_name == file_name) |
| 3006 | result = await db.execute(query) |
| 3007 | return result.scalars().first() is not None |
| 3008 | |
| 3009 | |
| 3010 | async def report_template_exists(file_name: str, db: AsyncSession) -> bool: |
| 3011 | query = select(CaseReportTemplateDataStore).where(CaseReportTemplateDataStore.report_template_name == file_name) |
| 3012 | result = await db.execute(query) |
| 3013 | return result.scalars().first() is not None |
| 3014 | |
| 3015 | |
| 3016 | async def sha256_hash_file(file: UploadFile) -> str: |
| 3017 | await file.seek(0) |
| 3018 | file_content = await file.read() |
| 3019 | file_hash = hashlib.sha256(file_content).hexdigest() |
| 3020 | return file_hash |
| 3021 | |
| 3022 | |
| 3023 | async def get_file_size(file: UploadFile) -> int: |
| 3024 | await file.seek(0) |
| 3025 | content = await file.read() |
| 3026 | return len(content) |
| 3027 | |
| 3028 | |
| 3029 | async def add_file_to_db(case_id: int, file: UploadFile, file_size: int, file_hash: str, db: AsyncSession) -> None: |
| 3030 | db_file = CaseDataStore( |
| 3031 | case_id=case_id, |
| 3032 | bucket_name="copilot-cases", |
| 3033 | object_key=f"{case_id}/{file.filename}", |
| 3034 | file_name=file.filename, |
| 3035 | content_type=file.content_type, |
| 3036 | file_size=file_size, |
| 3037 | file_hash=file_hash, |
| 3038 | ) |
| 3039 | db.add(db_file) |
| 3040 | await db.commit() |
| 3041 | return db_file |
| 3042 | |
| 3043 | |
| 3044 | async def add_report_template_to_db(file: UploadFile, file_size: int, file_hash: str, db: AsyncSession) -> None: |
| 3045 | db_file = CaseReportTemplateDataStore( |
| 3046 | report_template_name=file.filename, |
| 3047 | bucket_name="copilot-case-report-templates", |
| 3048 | object_key=file.filename, |
| 3049 | content_type=file.content_type, |
| 3050 | file_name=file.filename, |
| 3051 | file_size=file_size, |
| 3052 | file_hash=file_hash, |
| 3053 | ) |
| 3054 | db.add(db_file) |
| 3055 | await db.commit() |
| 3056 | return db_file |
| 3057 | |
| 3058 | |
| 3059 | async def upload_file_to_case(case_id: int, file: UploadFile, db: AsyncSession) -> CaseDataStore: |
| 3060 | file_size = await get_file_size(file) |
| 3061 | file_hash = await sha256_hash_file(file) |
| 3062 | await file.seek(0) |
| 3063 | # Upload the file to Minio |
| 3064 | await upload_case_data_store( |
| 3065 | data=CaseDataStoreCreation( |
| 3066 | case_id=case_id, |
| 3067 | bucket_name="copilot-cases", |
| 3068 | object_key=file.filename, |
| 3069 | file_name=file.filename, |
| 3070 | content_type=file.content_type, |
| 3071 | file_hash=file_hash, |
| 3072 | ), |
| 3073 | file=file, |
| 3074 | ) |
| 3075 | |
| 3076 | # Add the file to the database |
| 3077 | return await add_file_to_db(case_id, file, file_size, file_hash, db) |
| 3078 | |
| 3079 | |
| 3080 | async def upload_report_template(file: UploadFile, db: AsyncSession) -> CaseReportTemplateDataStore: |
| 3081 | file_size = await get_file_size(file) |
| 3082 | file_hash = await sha256_hash_file(file) |
| 3083 | await file.seek(0) |
| 3084 | # Upload the file to Minio |
| 3085 | await upload_case_report_template_data_store( |
| 3086 | data=CaseReportTemplateDataStoreCreation( |
| 3087 | report_template_name=file.filename, |
| 3088 | bucket_name="copilot-case-report-templates", |
| 3089 | object_key=file.filename, |
| 3090 | file_name=file.filename, |
| 3091 | content_type=file.content_type, |
| 3092 | file_hash=file_hash, |
| 3093 | ), |
| 3094 | file=file, |
| 3095 | ) |
| 3096 | |
| 3097 | # Add the file to the database |
| 3098 | return await add_report_template_to_db(file, file_size, file_hash, db) |
| 3099 | |
| 3100 | |
| 3101 | async def get_file_by_case_id_and_name(case_id: int, file_name: str, db: AsyncSession) -> CaseDataStore: |
| 3102 | logger.info(f"Getting file {file_name} from case {case_id}") |
| 3103 | query = select(CaseDataStore).where(CaseDataStore.case_id == case_id, CaseDataStore.file_name == file_name) |
| 3104 | result = await db.execute(query) |
| 3105 | return result.scalars().first() |
| 3106 | |
| 3107 | |
| 3108 | async def get_report_template_by_name(file_name: str, db: AsyncSession) -> CaseReportTemplateDataStore: |
| 3109 | logger.info(f"Getting file {file_name}") |
| 3110 | query = select(CaseReportTemplateDataStore).where(CaseReportTemplateDataStore.report_template_name == file_name) |
| 3111 | result = await db.execute(query) |
| 3112 | return result.scalars().first() |
| 3113 | |
| 3114 | |
| 3115 | async def remove_file_from_db(file_id: int, db: AsyncSession) -> None: |
| 3116 | await db.execute(delete(CaseDataStore).where(CaseDataStore.id == file_id)) |
| 3117 | await db.commit() |
| 3118 | |
| 3119 | |
| 3120 | async def remove_report_template_from_db(file_id: int, db: AsyncSession) -> None: |
| 3121 | await db.execute(delete(CaseReportTemplateDataStore).where(CaseReportTemplateDataStore.id == file_id)) |
| 3122 | await db.commit() |
| 3123 | |
| 3124 | |
| 3125 | async def delete_file_from_case(case_id: int, file_name: str, db: AsyncSession) -> None: |
| 3126 | file = await get_file_by_case_id_and_name(case_id, file_name, db) |
| 3127 | if not file: |
| 3128 | raise HTTPException(status_code=404, detail="File not found") |
| 3129 | |
| 3130 | await delete_file(file.bucket_name, file.object_key) |
| 3131 | await remove_file_from_db(file.id, db) |
| 3132 | |
| 3133 | |
| 3134 | async def download_file_from_case(case_id: int, file_name: str, db: AsyncSession) -> Tuple[bytes, str]: |
| 3135 | file = await get_file_by_case_id_and_name(case_id, file_name, db) |
| 3136 | if not file: |
| 3137 | raise HTTPException(status_code=404, detail="File not found") |
| 3138 | |
| 3139 | file_content = await download_data_store(file.bucket_name, file.object_key) |
| 3140 | return file_content, file.content_type |
| 3141 | |
| 3142 | |
| 3143 | async def delete_report_template(file_name: str, db: AsyncSession) -> None: |
| 3144 | file = await get_report_template_by_name(file_name, db) |
| 3145 | if not file: |
| 3146 | raise HTTPException(status_code=404, detail="File not found") |
| 3147 | |
| 3148 | await delete_file(file.bucket_name, file.object_key) |
| 3149 | await remove_report_template_from_db(file.id, db) |
| 3150 | |
| 3151 | |
| 3152 | async def download_report_template(file_name: str, db: AsyncSession) -> Tuple[bytes, str]: |
| 3153 | file = await get_report_template_by_name(file_name, db) |
| 3154 | if not file: |
| 3155 | raise HTTPException(status_code=404, detail="File not found") |
| 3156 | |
| 3157 | file_content = await download_data_store(file.bucket_name, file.object_key) |
| 3158 | return file_content, file.content_type |
| 3159 | |
| 3160 | |
| 3161 | async def upload_report_template_to_data_store(db: AsyncSession) -> CaseReportTemplateDataStoreListResponse: |
| 3162 | current_dir = Path(os.getcwd()) |
| 3163 | templates_dir = current_dir.parent / "backend" / "app" / "incidents" / "templates" |
| 3164 | |
| 3165 | templates_list = [] |
| 3166 | |
| 3167 | for file in templates_dir.iterdir(): |
| 3168 | logger.info(f"Uploading report template {file.name} to Minio") |
| 3169 | if file.is_file(): |
| 3170 | if await report_template_exists(file.name, db): |
| 3171 | raise HTTPException(status_code=400, detail="File name already exists for this template") |
| 3172 | with open(file, "rb") as f: |
| 3173 | content = f.read() |
| 3174 | content_type = mimetypes.guess_type(file)[0] |
| 3175 | upload_file = UploadFile(filename=file.name, file=io.BytesIO(content)) |
| 3176 | await upload_case_report_template_data_store( |
| 3177 | data=CaseReportTemplateDataStoreCreation( |
| 3178 | report_template_name=file.name, |
| 3179 | bucket_name="copilot-case-report-templates", |
| 3180 | object_key=file.name, |
| 3181 | file_name=file.name, |
| 3182 | content_type=content_type, |
| 3183 | file_hash=hashlib.sha256(content).hexdigest(), |
| 3184 | ), |
| 3185 | file=upload_file, |
| 3186 | ) |
| 3187 | templates_list.append(file.name) |
| 3188 | await add_report_template_to_db(upload_file, len(content), hashlib.sha256(content).hexdigest(), db) |
| 3189 | |
| 3190 | return templates_list |
| 3191 | |
| 3192 | |
| 3193 | async def get_alert_filter_options(user: User, db: AsyncSession) -> dict: |
| 3194 | """Get distinct sources, assets, and tags from alerts the user has access to.""" |
| 3195 | from sqlalchemy import and_ |
| 3196 | from sqlalchemy import exists |
| 3197 | from sqlalchemy import or_ |
| 3198 | |
| 3199 | filters = [] |
| 3200 | |
| 3201 | # Customer filtering |
| 3202 | accessible_customers = await customer_access_handler.get_user_accessible_customers(user, db) |
| 3203 | if "*" not in accessible_customers: |
| 3204 | filters.append(Alert.customer_code.in_(accessible_customers)) |
| 3205 | |
| 3206 | # Tag filtering |
| 3207 | tag_filters = await tag_access_handler.build_alert_query_filters(user, db) |
| 3208 | accessible_tags = tag_filters["accessible_tags"] |
| 3209 | |
| 3210 | if "*" not in accessible_tags: |
| 3211 | tag_conditions = [] |
| 3212 | if accessible_tags: |
| 3213 | has_accessible_tag = exists( |
| 3214 | select(AlertToTag.alert_id) |
| 3215 | .where( |
| 3216 | and_( |
| 3217 | AlertToTag.alert_id == Alert.id, |
| 3218 | AlertToTag.tag_id.in_(accessible_tags), |
| 3219 | ), |
| 3220 | ) |
| 3221 | .correlate(Alert), |
| 3222 | ) |
| 3223 | tag_conditions.append(has_accessible_tag) |
| 3224 | |
| 3225 | if tag_filters["include_untagged"]: |
| 3226 | is_untagged = ~exists( |
| 3227 | select(AlertToTag.alert_id).where(AlertToTag.alert_id == Alert.id).correlate(Alert), |
| 3228 | ) |
| 3229 | tag_conditions.append(is_untagged) |
| 3230 | |
| 3231 | if tag_conditions: |
| 3232 | filters.append(or_(*tag_conditions)) |
| 3233 | else: |
| 3234 | return {"sources": [], "assets": [], "tags": []} |
| 3235 | |
| 3236 | where_clause = and_(*filters) if filters else True |
| 3237 | |
| 3238 | # Distinct sources |
| 3239 | sources_query = select(distinct(Alert.source)).where(where_clause).order_by(Alert.source) |
| 3240 | sources_result = await db.execute(sources_query) |
| 3241 | sources = [row[0] for row in sources_result if row[0]] |
| 3242 | |
| 3243 | # Build a subquery of accessible alert IDs to avoid auto-correlation issues |
| 3244 | # when joining Alert in asset/tag queries that also use exists() filters on Alert |
| 3245 | accessible_alert_ids = select(Alert.id).where(where_clause).subquery() |
| 3246 | |
| 3247 | # Distinct asset names |
| 3248 | assets_query = ( |
| 3249 | select(distinct(Asset.asset_name)).where(Asset.alert_linked.in_(select(accessible_alert_ids.c.id))).order_by(Asset.asset_name) |
| 3250 | ) |
| 3251 | assets_result = await db.execute(assets_query) |
| 3252 | assets = [row[0] for row in assets_result if row[0]] |
| 3253 | |
| 3254 | # Distinct tags |
| 3255 | tags_query = ( |
| 3256 | select(distinct(AlertTag.tag)) |
| 3257 | .join(AlertToTag, AlertToTag.tag_id == AlertTag.id) |
| 3258 | .where(AlertToTag.alert_id.in_(select(accessible_alert_ids.c.id))) |
| 3259 | .order_by(AlertTag.tag) |
| 3260 | ) |
| 3261 | tags_result = await db.execute(tags_query) |
| 3262 | tags = [row[0] for row in tags_result if row[0]] |
| 3263 | |
| 3264 | return {"sources": sources, "assets": assets, "tags": tags} |