Server.Utils.OrphanedFlows

name: Server.Utils.OrphanedFlows
description: |
  Sometimes flows are deleted but there is still outstanding data for
  them in flight. The server will continue to save this data after the
  flow is deleted.

  This happens when a hunt is deleted (thereby deleting all its flows)
  but there are in flight collections still outstanding.

type: SERVER

sources:
- query: |
     -- Calculate the space taken by each file (Does not count directories)
     LET _Du(OSPath, Accessor) =
       SELECT sum(item=Size) AS Sum
       FROM glob(globs="/**", root=OSPath, accessor=Accessor)
       GROUP BY 1
     LET Du(OSPath, Accessor) = _Du(OSPath=OSPath, Accessor=Accessor)[0].Sum

     -- Get all collection directories (that contain uploads, monitoring etc).
     LET FlowDirs =
         SELECT OSPath,
            OSPath[-1] AS FlowID, OSPath[-3] AS ClientId
         FROM glob(globs="/clients/*/collections/*", accessor="fs")
         WHERE NOT FlowID =~ ".db"

     -- An OrphanedFlows is a flow that does not have a metadata record
     -- but still has some data.
     LET OrphanedFlows = SELECT file_store(path=OSPath.String) AS Path,
         ClientId, FlowID, {
            SELECT * FROM flows(client_id=ClientId, flow_id=FlowID)
         } AS Details, Du(OSPath=OSPath, Accessor="fs") AS Size
         FROM FlowDirs
         WHERE NOT Details AND Size > 0
         ORDER BY Size DESC

     SELECT Path, ClientId, FlowID, Size, humanize(bytes=Size) AS HumanSize
     FROM OrphanedFlows