Windows.ADCS.CertificateAuthority.EDB

name: Windows.ADCS.CertificateAuthority.EDB
description: |
  Parses the Active Directory Certificate Services (ADCS) ESE database
  directly from disk.
  Reads the CA database at C:\Windows\System32\CertLog\*.edb using the
  ntfs accessor to handle the file lock while CertSvc is running.
  Returns all certificate requests with key forensic fields including
  disposition (issued/revoked/failed/pending), requester and caller
  identity, subject DN, template, timestamps, and serial number.
  Joins the Requests, Certificates, and RequestAttributes tables on
  RequestID to produce a single complete row per certificate.
  CertificateTemplate is resolved to the human-readable display name
  using a three-stage lookup:
    1. Display name from RequestAttributes for this RequestID
    2. Inline $RequestAttributes text CertificateTemplate:Name
    3. Raw OID from the Certificates table as last resort
  UPN is resolved with two-stage fallback:
    1. $UPN from the issued certificate (Certificates table)
    2. Requested UPN from SAN attribute (RequestAttributes table)
  Requires a build of Velociraptor compiled against the patched go-ese
  library (https://github.com/Velocidex/go-ese) where DateTime columns
  are always decoded as Windows FILETIMEs regardless of column Flags.
  Without the patch, all DateTime fields return 1899-12-30.
  Transaction log recovery: the CA service buffers writes in ESE
  transaction logs. esentutl /r is used to replay those logs into a
  temp copy of the EDB before parsing, ensuring all committed rows are
  visible and matching certutil output.
  Disposition values:
    9  = Issued
    11 = Pending
    15 = Revoked
    16 = Error
    20 = Failed
    21 = Denied
type: CLIENT

author: Paolo Coba, Lee Kirkpatrick, Rui Ataide - GuidePoint Security DFIR

required_permissions:
  - FILESYSTEM_READ
  - FILESYSTEM_WRITE
  - EXECVE

parameters:
  - name: EDBGlob
    default: C:/Windows/System32/CertLog/*.edb
    description: Glob to locate the CA database file
  - name: DispositionFilter
    description: |
      Filter by disposition value. Leave blank for all.
      Common values: 9=Issued, 15=Revoked, 20=Failed, 11=Pending
    type: int
  - name: RequesterRegex
    default: .
    description: Regex to filter on RequesterName or CallerName
    type: regex
  - name: SubjectRegex
    default: .
    description: Regex to filter on DistinguishedName or CommonName
    type: regex
  - name: TemplateRegex
    default: .
    description: Regex to filter on CertificateTemplate (display name or OID)
    type: regex
  - name: DateAfter
    description: Only return requests submitted after this date (ISO8601, e.g. 2026-01-01)
    type: timestamp
  - name: DateBefore
    description: Only return requests submitted before this date (ISO8601, e.g. 2026-12-31)
    type: timestamp

sources:
  - precondition: |
      SELECT * FROM wmi(
        query="SELECT * FROM Win32_Service WHERE Name='CertSvc'",
        namespace="root/CIMV2")
    query: |
      LET DispositionLookup <= dict(
        `9`  = "Issued",
        `11` = "Pending",
        `15` = "Revoked",
        `16` = "Error",
        `20` = "Failed",
        `21` = "Denied")
      LET edb_files = SELECT OSPath
        FROM glob(globs=EDBGlob, accessor="ntfs")
        WHERE NOT OSPath =~ "(?i)tmp\\.edb$"
      LET work_dirs <= SELECT
          OSPath AS OriginalPath,
          dirname(path=OSPath) AS CertLogDir,
          basename(path=OSPath) AS EDBName,
          tempdir(remove_last=TRUE) AS TempDir
        FROM edb_files
      LET copied_edbs <= SELECT
          OriginalPath, CertLogDir, EDBName, TempDir,
          copy(filename=OriginalPath,
               accessor="ntfs",
               dest=TempDir + "\\" + EDBName,
               create_directories=TRUE) AS CopiedEDB
        FROM work_dirs
      LET copied_chk <= SELECT
          OriginalPath, CertLogDir, EDBName, TempDir,
          copy(filename=CertLogDir + "\\edb.chk",
               accessor="ntfs",
               dest=TempDir + "\\edb.chk") AS CopiedChk
        FROM copied_edbs
      LET copied_jfm <= SELECT
          OriginalPath, CertLogDir, EDBName, TempDir,
          copy(filename=CertLogDir + "\\" + regex_replace(
               source=EDBName, re="\\.edb$", replace=".jfm"),
               accessor="ntfs",
               dest=TempDir + "\\" + regex_replace(
               source=EDBName, re="\\.edb$", replace=".jfm")) AS CopiedJfm
        FROM copied_chk
      LET copied_logs <= SELECT OriginalPath, CertLogDir, EDBName, TempDir
        FROM foreach(
          row=copied_jfm,
          query={
            SELECT OriginalPath, CertLogDir, EDBName, TempDir,
              copy(filename=OSPath,
                   accessor="ntfs",
                   dest=TempDir + "\\" + basename(path=OSPath)) AS CopiedLog
            FROM glob(
              globs=CertLogDir + "\\edb*.log",
              accessor="ntfs")
          })
      LET deduped_logs <= SELECT OriginalPath, TempDir, EDBName
        FROM copied_logs
        GROUP BY OriginalPath
      -- Run esentutl soft recovery to replay transaction logs, ensuring rows not yet checkpointed to the EDB are visible.
      LET recovered <= SELECT
          OriginalPath,
          TempDir + "\\" + EDBName AS RecoveredEDB
        FROM foreach(
          row=deduped_logs,
          query={
            SELECT OriginalPath, TempDir, EDBName,
                   Stdout, ReturnCode
            FROM execve(argv=[
              "esentutl.exe", "/r", "edb",
              "/l", TempDir,
              "/s", TempDir,
              "/d", TempDir],
              sep="\n")
          })
        GROUP BY OriginalPath
      LET req_attrs <= SELECT RequestID,
          `$AttributeName`  AS AttrName,
          `$AttributeValue` AS AttrValue
        FROM foreach(
          row=recovered,
          query={
            SELECT * FROM parse_ese(
              file=RecoveredEDB,
              table="RequestAttributes")
          })
        WHERE `$AttributeName` = "CertificateTemplate"
           OR `$AttributeName` = "SAN"
      LET attr_lookup <= to_dict(item={
        SELECT format(format="%d", args=RequestID) AS _key,
               AttrValue AS _value
        FROM req_attrs
        WHERE AttrName = "CertificateTemplate"
      })
      LET san_lookup <= to_dict(item={
        SELECT format(format="%d", args=RequestID) AS _key,
               regex_replace(
                 source=AttrValue,
                 re="(?i).*upn=([^,\n]+).*",
                 replace="$1") AS _value
        FROM req_attrs
        WHERE AttrName = "SAN" AND AttrValue =~ "(?i)upn="
      })
      LET inline_attrs <= SELECT RequestID,
          regex_replace(
            source=get(field="$RequestAttributes"),
            re="(?ms).*CertificateTemplate:([^\n\\\\]+).*",
            replace="$1") AS TemplateName
        FROM foreach(
          row=recovered,
          query={
            SELECT * FROM parse_ese(
              file=RecoveredEDB,
              table="Requests")
          })
        WHERE get(field="$RequestAttributes") =~ "CertificateTemplate:"
          AND NOT get(item=attr_lookup,
                      member=format(format="%d", args=RequestID))
      LET inline_lookup <= to_dict(item={
        SELECT format(format="%d", args=RequestID) AS _key,
               TemplateName AS _value
        FROM inline_attrs
      })
      -- Load Certificates table from the recovered EDB.
      -- With the patched go-ese, DateTime columns (NotBefore, NotAfter)
      -- are correct time.Time values decoded as Windows FILETIMEs.
      LET certs <= SELECT
          format(format="%d", args=RequestID)  AS _key,
          get(field="$CertificateTemplate")    AS CertificateTemplateOID,
          get(field="$SerialNumber")            AS SerialNumber,
          get(field="$UPN")                     AS UPN,
          NotBefore                             AS NotBefore,
          NotAfter                              AS NotAfter
        FROM foreach(
          row=recovered,
          query={
            SELECT * FROM parse_ese(
              file=RecoveredEDB,
              table="Certificates")
          })
      LET cert_lookup <= to_dict(item={
        SELECT _key,
               dict(
                 CertificateTemplateOID=CertificateTemplateOID,
                 SerialNumber=SerialNumber,
                 UPN=UPN,
                 NotBefore=NotBefore.Unix,
                 NotAfter=NotAfter.Unix) AS _value
        FROM certs
      })
      LET lookup(rid) = get(
        item=cert_lookup,
        member=format(format="%d", args=rid))
      -- Resolve template name
      LET resolve_template(rid) =
        if(condition=get(item=attr_lookup, member=format(format="%d", args=rid)),
           then=get(item=attr_lookup, member=format(format="%d", args=rid)),
           else=if(condition=get(item=inline_lookup, member=format(format="%d", args=rid)),
                  then=get(item=inline_lookup, member=format(format="%d", args=rid)),
                  else=get(item=cert_lookup,
                           member=format(format="%d", args=rid)).CertificateTemplateOID))
      -- Resolve UPN
      LET resolve_upn(rid) =
        if(condition=get(item=cert_lookup, member=format(format="%d", args=rid)).UPN,
           then=get(item=cert_lookup, member=format(format="%d", args=rid)).UPN,
           else=get(item=san_lookup, member=format(format="%d", args=rid)))
      -- Suppress zero/unset FILETIME values to NULL
      LET safe_ts(v) = if(condition=v AND v > 0,
                          then=timestamp(epoch=v),
                          else=NULL)
      SELECT
        RequestID,
        get(item=DispositionLookup,
            member=str(str=Disposition)) || str(str=Disposition) AS Disposition,
        get(field="$DispositionMessage")  AS DispositionMessage,
        `$RequesterName`                  AS RequesterName,
        `$CallerName`                     AS CallerName,
        SubmittedWhen,
        ResolvedWhen,
        get(field="RevokedWhen")          AS RevokedWhen,
        get(field="RevokedReason")        AS RevokedReason,
        get(field="$DistinguishedName")   AS DistinguishedName,
        get(field="$CommonName")          AS CommonName,
        resolve_template(rid=RequestID)            AS CertificateTemplate,
        lookup(rid=RequestID).SerialNumber         AS SerialNumber,
        resolve_upn(rid=RequestID)                 AS UPN,
        safe_ts(v=lookup(rid=RequestID).NotBefore) AS NotBefore,
        safe_ts(v=lookup(rid=RequestID).NotAfter)  AS NotAfter,
        OriginalPath                               AS DatabasePath
      FROM foreach(
        row=recovered,
        query={
          SELECT *, OriginalPath, RecoveredEDB
          FROM parse_ese(file=RecoveredEDB, table="Requests")
        })
      WHERE
        `$RequesterName` =~ RequesterRegex
        AND get(field="$DistinguishedName") =~ SubjectRegex
        AND resolve_template(rid=RequestID) =~ TemplateRegex
        AND if(condition=DispositionFilter,
               then=Disposition = DispositionFilter,
               else=TRUE)
        AND if(condition=DateAfter,
               then=SubmittedWhen > DateAfter,
               else=TRUE)
        AND if(condition=DateBefore,
               then=SubmittedWhen < DateBefore,
               else=TRUE)