What Is an ESEDB File? How to Open the Windows Database Behind Search, SRUM and WebCache

from dissect.esedb import EseDB

with open('SRUDB.dat', 'rb') as fh:
    db = EseDB(fh)
    for table in db.tables():
        print(table.name, len(table.columns), sum(1 for _ in table.records()))

MSysObjects 28 161
MSysObjectsShadow 28 161
MSysObjids 3 28
MSysLocales 3 7
SruDbIdMapTable 3 106
SruDbCheckpointTable 5 0
{17F4D97B-F26A-5E79-3A82-90040A47D13D} 6 6
{841A7317-3805-518B-C2EA-AD224CB4AF84} 5 3
{D10CA2FE-6FCF-4F6D-848E-B2E99266FA89} 19 203
{DC3D3B50-BB90-5066-FA4E-A5F90DD8B677} 5 3
{DD6636C4-8929-4683-974E-22C046A43763} 9 3
{EEE2F477-0659-5C47-EF03-6D6BEFD441B3} 7 2

xxd -s 4 -l 4 SRUDB.dat
00000004: efcd ab89                                ....
file SRUDB.dat
SRUDB.dat: Extensible storage engine DataBase, version 0x620, checksum 0x4ab4ac4d, page size 4096, Windows version 10.0

#!/usr/bin/env python3
"""ese_who.py - say which Windows app an ESE file belongs to, list its tables, export to CSV.
Needs: pip install dissect.esedb
Usage: python3 ese_who.py FILE [--csv OUTDIR | --sqlite OUT.db]
"""
import csv, datetime, os, sqlite3, sys
from dissect.esedb import EseDB

OLE0 = datetime.datetime(1899, 12, 30)
FT0 = datetime.datetime(1601, 1, 1)

RULES = [  # (app, test on the set of table names)
    ("Windows User Access Logging (Current.mdb or GUID.mdb)", lambda t: {"ROLE_ACCESS", "CLIENTS", "DNS", "VIRTUALMACHINES"} <= t),
    ("Windows User Access Logging (SystemIdentity.mdb)", lambda t: {"SYSTEM_IDENTITY", "ROLE_IDS"} <= t),
    ("SRUM (SRUDB.dat)", lambda t: "SruDbIdMapTable" in t),
    ("Windows Search (Windows.edb)", lambda t: any(n.startswith("SystemIndex_") for n in t)),
    ("IE/Edge WebCache (WebCacheV01.dat)", lambda t: "Containers" in t and any(n.startswith("Container_") for n in t)),
    ("File History (Catalog1.edb)", lambda t: {"namespace", "backupset", "file", "library"} <= t),
]

def guess(names):
    t = set(names)
    for app, test in RULES:
        if test(t):
            return app
    return "no fingerprint matched (unknown app or test data)"

def when(value, ctype):
    """DateTime columns store an OLE day count (float bits) or a FILETIME; decide by range."""
    import struct
    raw = value if isinstance(value, int) else None
    if raw is None:
        return value
    as_double = struct.unpack("<d", struct.pack("<q", raw))[0] if -2**63 <= raw < 2**63 else None
    try:
        d = OLE0 + datetime.timedelta(days=as_double)
        if 1995 <= d.year <= 2030:
            return d.isoformat(sep=" ")
    except (OverflowError, TypeError, ValueError):
        pass
    try:
        d = FT0 + datetime.timedelta(microseconds=raw // 10)
        if 1995 <= d.year <= 2030:
            return d.isoformat(sep=" ")
    except (OverflowError, ValueError):
        pass
    return raw

def cell(v, col):
    if col.type.name == "DateTime":
        return when(v, col.type)
    if isinstance(v, (bytes, bytearray)):
        return v.hex()
    return v


def main(path, outdir=None, sqlite_path=None):
    lite = sqlite3.connect(sqlite_path) if sqlite_path else None
    with open(path, "rb") as fh:
        db = EseDB(fh)
        tables = list(db.tables())
        print(f"{os.path.basename(path)}: {guess(t.name for t in tables)}")
        for t in tables:
            rows = bad = deleted = 0
            writer = out = None
            batch = []
            cols = [c.name for c in t.columns]
            if lite and not t.name.startswith("MSys"):
                quoted = ", ".join('"%s"' % c.replace('"', '""') for c in ["_deleted_flag"] + cols)
                lite.execute('CREATE TABLE "%s" (%s)' % (t.name.replace('"', '""'), quoted))
            if outdir and not t.name.startswith("MSys"):
                os.makedirs(outdir, exist_ok=True)
                safe = "".join(ch if ch.isalnum() or ch in "-_" else "_" for ch in t.name)
                out = open(os.path.join(outdir, safe + ".csv"), "w", newline="", encoding="utf-8")
                writer = csv.writer(out)
                writer.writerow(["_deleted_flag"] + cols)
            for rec in t.records():
                flagged = "Deleted" in str(rec._node.tag.flags)
                deleted += flagged
                try:
                    d = rec.as_dict()
                except Exception:
                    bad += 1
                    continue
                rows += 1
                if writer or lite and not t.name.startswith("MSys"):
                    vals = []
                    for c in t.columns:
                        v = d.get(c.name)
                        if isinstance(v, list):  # multi-value column
                            v = "; ".join(str(cell(x, c)) for x in v)
                        else:
                            v = cell(v, c)
                        vals.append(v)
                    if writer:
                        writer.writerow([int(flagged)] + vals)
                    else:
                        batch.append([int(flagged)] + [int(v) if isinstance(v, bool) else v for v in vals])
            if out:
                out.close()
            if lite and batch:
                lite.executemany('INSERT INTO "%s" VALUES (%s)' % (t.name.replace('"', '""'), ",".join("?" * (len(cols) + 1))), batch)
            print(f"  {t.name:<40} cols {len(cols):>6} rows {rows:>7}  deleted-flag {deleted:>3}  unreadable {bad}")

    if lite:
        lite.commit()
        lite.close()


if __name__ == "__main__":
    if len(sys.argv) < 2:
        sys.exit(__doc__)
    flag = sys.argv[2] if len(sys.argv) > 3 else None
    main(sys.argv[1], sys.argv[3] if flag == "--csv" else None, sys.argv[3] if flag == "--sqlite" else None)

python3 ese_who.py plaso-Windows.edb
plaso-Windows.edb: Windows Search (Windows.edb)
  MSysObjects                              cols     25 rows     542  deleted-flag   0  unreadable 0
  MSysObjectsShadow                        cols     25 rows     542  deleted-flag   0  unreadable 0
  MSysUnicodeFixupVer2                     cols     10 rows       0  deleted-flag   0  unreadable 0
  __NameTable__                            cols      8 rows       1  deleted-flag   0  unreadable 0
  SystemIndex_Gthr                         cols     16 rows     353  deleted-flag  14  unreadable 0
  SystemIndex_GthrPth                      cols      3 rows     131  deleted-flag   8  unreadable 0
  SystemIndex_0P                           cols     10 rows     376  deleted-flag   0  unreadable 0
  SystemIndex_0A                           cols    381 rows     322  deleted-flag   0  unreadable 0

python3 ese_who.py plaso-WebCacheV01.dat
plaso-WebCacheV01.dat: IE/Edge WebCache (WebCacheV01.dat)
  LeakFiles                                cols      3 rows       0  deleted-flag   1  unreadable 1
  DependencyEntry_3                        cols      7 rows       0  deleted-flag   1  unreadable 1
  DependencyEntry_5                        cols      7 rows       0  deleted-flag   1  unreadable 1

python3 ese_who.py dissect-basic.edb
dissect-basic.edb: no fingerprint matched (unknown app or test data)
  MSysObjects                              cols     28 rows      87  deleted-flag   0  unreadable 0
  MSysObjectsShadow                        cols     28 rows      87  deleted-flag   0  unreadable 0

from dissect.esedb import EseDB

with open('SRUDB.dat', 'rb') as fh:
    db = EseDB(fh)
    print('page size', db.page_size)
    print('state', db.header.dbstate)

page size 4096
state 3

pip install dissect.esedb
python3 ese_who.py SRUDB.dat --csv srum_csv
python3 ese_who.py SRUDB.dat --sqlite srum.db

SELECT COUNT(*) AS files,
       MIN(datetime(fileCreated / 10000000 - 11644473600, 'unixepoch')) AS first_created,
       MAX(datetime(fileCreated / 10000000 - 11644473600, 'unixepoch')) AS last_created
FROM namespace
WHERE fileCreated > 0;

files | first_created | last_created
1340 | 2012-07-31 11:09:00 | 2013-10-22 16:33:54