Windows.Forensics.UserAccessLogs

Parse and collect the SUM database

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 builtin ESE parser. This means that you do not need to repair the files using eseutil.exe as is commonly explained in the references below. Velociraptor should have no trouble parsing these files on the live system.

name: Windows.Forensics.UserAccessLogs
description: |
  Parse and collect the SUM database

  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 builtin ESE parser. This means
  that you **do not need to repair** the files using `eseutil.exe` as
  is commonly explained in the references below. Velociraptor should
  have no trouble parsing these files on the 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 dont 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)