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 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.
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 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)