Keep Microsoft Entra access request history past 30 days
The short answer
Microsoft Entra keeps audit logs for 30 days on P1 and P2. Archive them before they expire. Separately, keep your own daily export of access requests, with approver and reason. The five checks below run on that export. They catch a missing day, rows that did not load, repeats, and decisions with no approver or reason.
What you will have at the end
A copy of your audit logs and of your access requests, both held by you, and a script, entra_request_history_check.py, that tells you whether the request copy is whole. The script shows whether each delivered or denied request names an approver, has a reason, and is dated after the request. Download the full guide (PDF) for the same steps with the complete code and a checklist.
Two things are easy to confuse. Keeping the history means copying it out of Entra before it expires. Checking it means testing that the copy has every day, every row the manifest lists and, for every decision, an approver id and a reason. A copy with a missing day looks the same as a full one until someone asks for that day.
The 30 days is Microsoft’s audit-log retention. Microsoft’s resource page for access requests states no retention period, and we did not test one, so the request export is yours to keep and to check.
We ran the script on generated records, not on a tenant. They are invented, seeded (134) and use the column names Microsoft publishes for accessPackageAssignmentRequest and approvalStage (read 2026-11-04). Nothing here is a measurement of any organization.
Step 1. Send the logs somewhere that keeps them
Entra keeps audit logs for 30 days on Microsoft Entra ID P1 and P2 and for seven days on Free (Microsoft Learn: Microsoft Entra data retention (read 2026-11-04)). The same page says retention changes are not retroactive: data that has already expired can’t be recovered unless it was archived first. So the copy has to start before you need it.
In the Entra admin center, a diagnostic setting sends the logs to a Log Analytics workspace, an event hub or a storage account. You need the Security Administrator role, the destination must exist first, and logs can take up to three days to start appearing there (Microsoft Learn: Configure Microsoft Entra diagnostic settings (read 2026-11-04)). The retention page also notes that tenants with certain Microsoft 365 E5 licenses can use Microsoft Purview Audit (Premium) as an alternative.
That archive covers the audit log. The checks below run on a second export, your own daily file of access requests (Step 2). Once both are running, nothing in Entra tells you if a day went missing downstream. That is what the script is for.
Step 2. Shape what you keep into two tables
The script reads two tables. Both have fixed column names. The request columns follow the Graph fields in the third column.
| Table | Column | Graph field it follows |
|---|---|---|
| requests | request_id | accessPackageAssignmentRequest id |
| requests | created_at | createdDateTime |
| requests | state | state (delivered, denied, ...) |
| requests | reviewed_by | approvalStage reviewedBy, its id only (all zeros if nobody reviewed) |
| requests | reviewed_at | reviewedDateTime |
| requests | approver_justification | justification on the approval stage |
| manifest | export_day | the day one export file covers (yours) |
| manifest | record_count | the rows that export says it holds (yours) |
The manifest is the part people skip. A daily job writes one file of requests per day, and also records how many rows it wrote. Without that record there is nothing to compare the loaded rows with, and a short file passes unnoticed. Take the count from the source side (the query that built the file), not by counting the file afterwards.
The approval fields come from the approval stage, so your REQUESTS_SQL must return one row per request. We did not test requests with more than one approval stage; a join that repeats a request id will trip the duplicates check.
The audit log copy from Step 1 is a second source with its own fields, such as directoryAudit activityDateTime, result and correlationId (read 2026-11-04). The script as shipped checks the request table only. The resource page for the requests gives no retention period for them, and we did not test how far back a live tenant returns them, so check that before you rely on a live pull.
Step 3. Run five checks, each one plain SQL
The checks are ordinary DuckDB SQL over the two tables, with no AI call and no library beyond duckdb (DuckDB documentation (read 2026-11-04)). Each one has a planted fault in Run 2 that trips it.
| Check | It fails when | Planted fault that trips it |
|---|---|---|
depth | the oldest export is newer than REQUIRED_DAYS before AS_OF | exports before 2026-08-20 removed |
gap | a day from the oldest export to yesterday has no export, or a manifest row has a NULL | the export for 2026-09-15 removed |
reconcile | rows loaded for a day differ from the count that day’s export says it holds | two rows missing from the 2026-10-02 export |
duplicates | a request id is repeated or missing | one request exported twice |
evidence | a delivered or denied request has no approver, no reason, or a decision dated before the request | one all-zero approver, one blank reason, one decision dated before its request |
The gap check, copied from the script. It uses NOT EXISTS and not NOT IN, because a single NULL day in the manifest would make NOT IN match nothing and hide every gap:
SELECT CAST(d AS DATE)
FROM generate_series(CAST(? AS DATE), CAST(? AS DATE), INTERVAL 1 DAY) t(d)
WHERE NOT EXISTS (SELECT 1 FROM manifest m WHERE m.export_day = CAST(d AS DATE))The evidence check, which answers “who approved, and why”. A NULL approver or reason counts as missing, and so does an approver that is not a GUID. A state written Delivered or DENIED still counts as decided, and if no request is in a decided state the check fails rather than passing on nothing:
SELECT count(*),
sum(CASE WHEN reviewed_by IS NULL OR lower(trim(reviewed_by)) = '00000000-0000-0000-0000-000000000000'
OR NOT regexp_full_match(lower(trim(reviewed_by)), '[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12}')
THEN 1 ELSE 0 END) AS no_approver,
sum(CASE WHEN trim(coalesce(approver_justification, '')) = '' THEN 1 ELSE 0 END) AS no_reason,
sum(CASE WHEN reviewed_at IS NULL OR created_at IS NULL OR reviewed_at < created_at
THEN 1 ELSE 0 END) AS bad_date
FROM requests WHERE lower(trim(state)) IN ('delivered', 'denied')The whole file is entra_request_history_check.py. Install the one dependency with pip install duckdb.
Run 1: clean records, every check passes
We ran the script on 2026-11-04 with DuckDB 1.5.6. It generates 95 daily exports and 332 requests, and the five checks pass.
Command: run 1python3 guides/keep-entra-access-request-history-past-30-days/entra_request_history_check.pyduckdb 1.5.6; python 3.12.15; as of 2026-11-04; history required: 90 days generated records (seed 134): 332 requests, 95 daily exports PASS depth oldest export 2026-08-01, needed 2026-08-06 or earlier (90 days before 2026-11-04) PASS gap 0 day(s) with no export; 0 manifest row(s) with a NULL PASS reconcile 0 day(s) where loaded rows differ from the export's count PASS duplicates 0 request id(s) repeated; 0 row(s) with no id PASS evidence 332 decided request(s): 0 without an approver, 0 without a reason, 0 dated before the request or not dated every check passed
Run 2: five faults planted, the checks catch all five
Same script, same seed, with --plant-error. It plants the five faults in the table above before the checks run, and stops if one could not be planted. The output below is the script’s own: it fails every check and then counts failures against planted faults. Note the gap and reconcile lines: they name the day.
python3 guides/keep-entra-access-request-history-past-30-days/entra_request_history_check.py --plant-errorduckdb 1.5.6; python 3.12.15; as of 2026-11-04; history required: 90 days generated records (seed 134): 270 requests, 75 daily exports planted -> depth: exports before 2026-08-20 removed planted -> gap: the export for 2026-09-15 removed planted -> reconcile: two rows missing from the export for 2026-10-02 planted -> duplicates: one request id appears twice planted -> evidence: one approver is the all-zero id, one reason is blank, one decision is dated before its request FAIL depth oldest export 2026-08-20, needed 2026-08-06 or earlier (90 days before 2026-11-04) FAIL gap 1 day(s) with no export: 2026-09-15; 0 manifest row(s) with a NULL FAIL reconcile 1 day(s) where loaded rows differ from the export's count; 2026-10-02: export says 5, loaded 3 FAIL duplicates 1 request id(s) repeated; 0 row(s) with no id FAIL evidence 270 decided request(s): 1 without an approver, 1 without a reason, 1 dated before the request or not dated caught 5 of 5 planted errors: depth, gap, reconcile, duplicates, evidence
Read it as a person would. The reconcile line says the export for 2026-10-02 claimed 5 rows and 3 loaded: a file cut short. The evidence line says that among 270 decided requests there is one without an approver, one without a reason and one dated before the request. Those are the three gaps the evidence check looks for. The depth check fails because the planted run has no export before 2026-08-20. The clean run starts on 2026-08-01, five days earlier than the 90 days need.
What a person does with a failure: re-pull the named day from the source, reload it, and run the check again. A decision with no approver or no reason cannot be repaired from the copy; it goes back to whoever owns that access.
Use it on your own data
To run it on your own export, load your request rows and your list of export files into a DuckDB file. In the CONFIG block, set DATABASE to the path of that file, set REQUESTS_SQL to a SELECT that returns request_id, created_at, state, reviewed_by, reviewed_at and approver_justification under those exact names, with one row per request, set MANIFEST_SQL to a SELECT that returns export_day and record_count, set AS_OF to the date you run it, set REQUIRED_DAYS to the number of days your auditor needs, and check DECIDED_STATES (lowercase) against the states in your export. Change nothing else in the file.
Write AS_OF like "2026-11-04". The file opens your database read-only. A state in any capitalization is read as the same state, and a NULL approver, reason or request id is reported, never skipped. To stop a scheduled job from failing when a check does, run the file with --exit-zero; it still prints every failure. We built a different DuckDB file for this test, with other table and column names, mixed-case names and NULLs, a missing day, a short export, a repeated id, and a decision with no approver, no reason or a date before the request. The script failed all five checks.
One case to settle first. If an access package policy needs no approval, its delivered requests may have no approver, and the evidence check will flag them. Check how your tenant reports them, then leave them out with a WHERE clause in REQUESTS_SQL. We did not test how a tenant reports them. DECIDED_STATES lists the states that must have an approver; it is compared with the lowercase of your state column. The script reads created_at in UTC, the time zone Graph uses for createdDateTime, so the reconcile check counts each request on its UTC day. Set export_day to the UTC day each export file covers.
Limits
- This is not a tenant result. The records are generated. The run shows that the checks catch five kinds of fault in records shaped like Microsoft’s. It does not show what any tenant’s history looks like.
- The checks test completeness and shape, not truth. They cannot tell whether the approver was the right person or the reason was honest.
- The manifest is only as good as the job that writes it. If it counts the rows it received and not the rows the source held, a short pull can match a short manifest.
- We did not run Entra for this guide. Retention figures come from the linked Microsoft pages, read 2026-11-04. Microsoft changes them; check before you rely on them.
Tested on
Tested on
- Platform
- DuckDB 1.5.6, Python 3.12.15, run from Python with the
duckdbmodule - Edition
- Open-source DuckDB, in-memory
- Date
- 2026-11-04
- Dataset
- Generated records, fixed seed 134, with column names from Microsoft Graph (accessPackageAssignmentRequest, approvalStage). Not a run on a Microsoft Entra tenant.
- Code
- entra_request_history_check.py
When this is not enough
- A copy is not a decision record. If nobody wrote a reason when the request was decided, no check can add one afterward.
- Your copy needs its own rules. Who can change or delete the storage it sits in, and how long it is kept, are outside this script.
- Your tenant may differ. Run the check on your own export, and read how your tenant reports automatic approvals and removals.
- People still get access outside the process. A clean history covers requests that were made, not access given another way.
Keep who agreed, and why, with each decision
This guide keeps a copy of your access request history and checks it for gaps. It can show that a reason is missing. It can’t write one that nobody recorded when the request was decided.
In Data Access Concierge, the requester writes why they need access. The owner, or a stand-in, decides on one screen. The yes is on record with a name and a time. Access ends on the agreed date. We set it up in your systems and run it for you.