Windows.Forensics.UserAccessLogs

name: Windows.Forensics.UserAccessLogs
description: |
  Parses the Windows User Access Logging (UAL) ESE database to
  enumerate client connections and server roles.

  UAL is a feature that can help server administrators quantify the
  number of unique client requests of roles and services on a local
  server.

  The UAL only exists on Windows Server edition 2012 and above.

  NOTE: Unlike other tools, Velociraptor DOES NOT use the JET API to
  access the database because it has a built-in ESE parser. This means
  that **you do not need to repair the files using `eseutil.exe`**
  even though this is a commonly recommended step in the references
  linked below. Velociraptor should have no trouble parsing these
  files on a live system.

reference:
  - https://advisory.kpmg.us/blog/2021/digital-forensics-incident-response.html
  - https://docs.microsoft.com/en-us/windows-server/administration/user-access-logging/manage-user-access-logging
  - https://www.crowdstrike.com/blog/user-access-logging-ual-overview/

export: |
    LET IPProfile = '''[
      ["IP4", 0, [
        ["A", 0, "uint8"],
        ["B", 1, "uint8"],
        ["C", 2, "uint8"],
        ["D", 3, "uint8"],
        ["IP", 0, "Value", {
           value: "x=> format(format='%d.%d.%d.%d', args=[x.A, x.B, x.C, x.D])"
        }]
      ]],
     ["IP6", 0, [
        ["A", 0, "uint16be"],
        ["B", 2, "uint16be"],
        ["C", 4, "uint16be"],
        ["D", 6, "uint16be"],
        ["E", 8, "uint16be"],
        ["F", 10, "uint16be"],
        ["G", 12, "uint16be"],
        ["H", 14, "uint16be"],
        ["IP", 0, "Value", {
           value: "x=> format(format='%04x:%04x:%04x:%04x:%04x:%04x:%04x:%04x', args=[x.A, x.B, x.C, x.D, x.E, x.F, x.G, x.H])"
        }]
      ]]
    ]'''

    -- Format the address - it can be IPv4, IPv6 or something else.
    LET FormatAddress(Address) = if(condition=len(list=Address) = 4,

         -- IPv4 address should be formatted in dot notation
         then=parse_binary(accessor="data",
                           filename=Address, struct="IP4",
                           profile=IPProfile).IP,

         else=if(condition=len(list=Address)=16,
           -- IPv6 addresses are usually shortened
           then=parse_binary(accessor="data",
                           filename=Address, struct="IP6",
                           profile=IPProfile).IP,

           -- We don't know what kind of address it is.
           else=format(format="%x", args=Address)))

    -- Get the Clients table from all snapshot files.
    LET SystemIdentity = SELECT OSPath FROM glob(globs=SUMGlob)
      WHERE Name =~ "SystemIdentity.mdb"

    -- Prepare a Role lookup to resolve the role GUID
    LET RoleLookup <= memoize(key="RoleGuid", query={
      SELECT * FROM foreach(row=SystemIdentity, query={
         SELECT * FROM parse_ese(file=OSPath, table="ROLE_IDS")
         WHERE log(message="RoleGuid " + RoleGuid)
      })
    })

parameters:
    - name: SUMGlob
      type: glob
      default: C:/Windows/System32/LogFiles/Sum/*
      description: A glob to file all SUM ESE databases on the system.
    - name: AlsoUpload
      type: bool
      description: If set we also upload the raw files.

sources:
    - name: SystemIdentity
      description: Parse the SystemIdentity database.
      query: |
        SELECT * FROM foreach(row=SystemIdentity, query={
           SELECT *, OSPath AS _OSPath
           FROM parse_ese(file=OSPath, table="SYSTEM_IDENTITY")
        })

    - name: Chained Databases
      query: |
        SELECT * FROM foreach(row=SystemIdentity, query={
          SELECT *, OSPath AS _OSPath
          FROM parse_ese(file=OSPath, table="CHAINED_DATABASES")
        })

    - name: RoleIds
      query: |
        SELECT * FROM foreach(row=SystemIdentity, query={
           SELECT *, OSPath AS _OSPath
           FROM parse_ese(file=OSPath, table="ROLE_IDS")
        })

    - name: Clients
      description: Dump the clients database from all ESE files
      query: |
        LET ContentDatabases =  SELECT * FROM glob(globs=SUMGlob)
           WHERE Name =~ ".mdb" AND NOT Name =~ "SystemIdentity"

        -- The clients table has potentially 365 columns (1 per day) so we
        -- format it a bit better by putting the Day* columns in their own dict.
        LET GetClients(OSPath) = SELECT *, OSPath AS _OSPath
             FROM foreach(row={
            SELECT to_dict(item={
                   SELECT _key, _value FROM items(item=_value)
                   WHERE NOT _key =~ "Day"
               })  +
               dict(Days=to_dict(item={
                   SELECT _key, _value FROM items(item=_value)
                   WHERE _key =~ "Day"
               })) AS Value
            FROM items(item={
               SELECT *, get(item=RoleLookup, field=RoleGuid).RoleName AS RoleName,
                  Address AS RawAddress,
                  FormatAddress(Address=unhex(string=Address)) AS Address
               FROM parse_ese(file=OSPath, table="CLIENTS")
            })
        }, column="Value")

        -- Get the Clients table from all snapshot files.
        SELECT * FROM foreach(row=ContentDatabases, query={
          SELECT * FROM GetClients(OSPath=OSPath)
        })

    - name: VIRTUALMACHINES
      query: |
        SELECT * FROM foreach(row=ContentDatabases, query={
           SELECT *, OSPath AS _OSPath
           FROM parse_ese(file=OSPath, table="VIRTUALMACHINES")
        })

    - name: DNS
      query: |
        SELECT * FROM foreach(row=ContentDatabases, query={
           SELECT *, OSPath AS _OSPath
           FROM parse_ese(file=OSPath, table="DNS")
        })

    - name: Uploads
      query: |
        SELECT OSPath, if(condition=AlsoUpload, then=upload(file=OSPath))
        FROM glob(globs=SUMGlob)