Manual Log Analysis Checklist
If you have read our previous posts on the five categories of healthcare privacy breaches and how to review audit logs, you understand the problem and the tool. What you need now is a process you can follow every time — a structured, repeatable checklist that takes you from raw audit data to confirmed findings.
That is what this post delivers.
Below is a four-step checklist for detecting privacy breaches in your EHR audit logs using Excel. Each step includes specific actions, Excel formulas, filter rules, and decision criteria. The checklist is designed to be printed, shared with your privacy team, and used as a working document during every review cycle.
Key Facts at a Glance
- This checklist covers all five privacy breach categories: curiosity snooping, personal relationship access, financially motivated breaches, malicious disclosure, and systemic/process failures.
- Each detection rule includes the specific Excel operation (sort, filter, pivot table, or formula) needed to surface the indicator.
- HIPAA requires audit log retention for six years with tamper-proof storage (Source: 45 CFR § 164.312(b)).
- Proactive audit review detects breaches weeks or months earlier than complaint-driven review (Source: Journal of AHIMA).
- The proposed 2025 HIPAA Security Rule update calls for measures that detect “suspicious activity or unusual patterns of data access” (Source: Federal Register, 2025-01-06).
- A mid-sized hospital generates millions of audit log events per month — targeted exports and structured analysis are essential (Source: Journal of AHIMA).
Export Audit Logs in CSV or Excel Format
Before you can analyze anything, you need the data out of your EHR and into a format you can work with. This step produces the raw material for every detection rule that follows.
- Define the review scope. Decide what this review cycle covers: a specific date range (e.g., past 30 days), a specific department, a specific set of patients (VIP list, recent complaints), or a specific set of users (new hires, departing employees). Do not attempt a full unrestricted dump — it will be too large to analyze effectively.
- Access your EHR’s audit reporting tool. In Epic, use Reporting Workbench to run an Access Log report. In Cerner (Oracle Health), use Audit Vault or CCL audit queries. In MEDITECH, use the Audit Trail Report. Consult your EHR administrator if you are unsure which module to use.
- Apply export filters. Set the date range, department, patient list, or user list you defined. The tighter your filters, the more manageable the output.
- Export as CSV. Select CSV (comma-separated values) as the output format. CSV is universally compatible with Excel, Google Sheets, and database tools. If your system offers only Excel (.xlsx) export, that works too.
- Verify the export includes required fields. Before closing your EHR, confirm the export contains these columns at minimum:
- User ID / User Name
- User Role / Department
- Patient ID / Patient Name (or MRN)
- Action Type (View, Edit, Print, Export, Copy, Delete)
- Timestamp (date and time)
- Workstation / Device identifier
- Data Elements Accessed (if available: demographics, notes, labs, meds, billing)
- Save the file securely. Store the export on an encrypted, access-controlled drive — not a shared desktop, personal device, or email attachment. Audit log exports contain sensitive access information and must be treated as confidential data.
Privacy_Audits/2026-04/ — with subfolders for raw exports, working files, and final documentation. This keeps your review organized and makes it easy to locate historical audits if a regulator requests them.
Open the Audit Logs in Excel and Prepare for Analysis
Raw CSV data is not analysis-ready. This step transforms it into a structured, filterable table with the helper columns you need for the detection rules in Step 3.
- Open the CSV in Excel. File > Open > select your CSV file. If prompted for delimiter settings, choose “Comma.” Confirm data displays correctly in columns.
- Format as a table. Select all data (
Ctrl+A), then Insert > Table (orCtrl+T). This activates drop-down filter arrows on every column header — essential for every analysis step that follows. - Freeze the header row. View > Freeze Panes > Freeze Top Row. This keeps column labels visible as you scroll through thousands of rows.
- Add a “Date” helper column. Insert a new column. Formula:
=INT([@Timestamp])or=DATE(YEAR([@Timestamp]),MONTH([@Timestamp]),DAY([@Timestamp])). Format as a short date. This lets you filter by specific days. - Add a “Time of Day” helper column. Formula:
=TEXT([@Timestamp],"HH:MM"). This isolates the time component for after-hours analysis. - Add an “After Hours?” flag column. Formula:
=IF(OR(HOUR([@Timestamp])<6, HOUR([@Timestamp])>=21),"YES",""). This automatically flags access before 6:00 AM and after 9:00 PM. Adjust the thresholds to match your organization’s shift patterns. - Add a “Weekend?” flag column. Formula:
=IF(OR(WEEKDAY([@Timestamp],2)=6, WEEKDAY([@Timestamp],2)=7),"YES",""). This flags Saturday and Sunday access. - Add a “High-Risk Action?” flag column. Formula:
=IF(OR([@Action]="Print", [@Action]="Export", [@Action]="Download", [@Action]="Copy"),"YES",""). Adjust the action names to match your EHR’s terminology. This instantly highlights data extraction events. - Add an “On Care Team? (Y/N)” column. Leave this blank for now — you will populate it during Step 3 as you investigate individual access events. This is where your verification findings are recorded.
- Add a “Flag / Notes” column. A free-text column for documenting your observations, questions, and preliminary findings as you work through the analysis.
Analyze Audit Logs to Detect the Five Categories of Privacy Breaches
This is the core of the checklist. Each subsection targets one breach category with specific rules, Excel operations, and criteria for flagging suspicious events. Work through each category in order.
3A. Curiosity-Driven Snooping
Goal: Identify employees who accessed patient records without a clinical purpose, driven by personal curiosity — typically triggered by a newsworthy event, celebrity admission, or community incident.
| Rule | Excel Operation | What to Flag |
|---|---|---|
| Rule 3A-1: Excess user count per patient | Filter by Patient ID (start with VIP or high-profile patients). Create a pivot table: Rows = User Name, Values = Count of accesses. Count distinct users. | Flag if the number of unique users accessing a patient’s record significantly exceeds the expected care team size (typically 5-15 people depending on acuity). A ratio of 2x or more is suspicious. |
| Rule 3A-2: Access from unrelated departments | Filter by Patient ID. Sort by User Role/Department. Highlight users whose department does not match the patient’s care unit. | Flag any user whose department has no clinical connection to the patient (e.g., a cafeteria clerk viewing an ICU patient, a dermatology nurse viewing an obstetric patient). |
| Rule 3A-3: View-only access without documentation | Filter by Patient ID and a specific User. Check whether the user has any “Edit” or documentation action for that patient — not just “View.” | Flag users who only viewed the record without documenting a note, order, or clinical action. View-only, single-visit access by non-care-team members is the primary signature of snooping. |
| Rule 3A-4: Cluster timing after a triggering event | Filter by Patient ID. Sort by Timestamp. Look for a burst of access — multiple users within a 24-48 hour window — that correlates with a news story, internal announcement, or community event. | Flag access clusters that coincide with a known triggering event (patient admission reported in media, celebrity sighting, workplace incident). |
- Identify your VIP / high-profile patient list for this review period.
- Run Rule 3A-1 for each VIP patient — record the unique user count vs. care team size.
- Run Rule 3A-2 — list all non-care-team users who accessed the record.
- Run Rule 3A-3 for each non-care-team user — note view-only vs. documented access.
- Check for cluster timing (Rule 3A-4) if a triggering event is known.
- Record all flagged users in the “Flag / Notes” column with “3A — Curiosity snooping indicator.”
3B. Personal Relationship Access
Goal: Identify employees who accessed records of people they know personally — family, friends, ex-partners, co-workers — for reasons unrelated to their job function.
| Rule | Excel Operation | What to Flag |
|---|---|---|
| Rule 3B-1: Shared surname match | Add a helper column: =IF(LEFT([@UserName], FIND(",",[@UserName])-1) = LEFT([@PatientName], FIND(",",[@PatientName])-1), "MATCH",""). Adjust the formula to match your name format. Alternatively, use VLOOKUP against an employee list. |
Flag any access where the employee and patient share a surname. Not all matches are breaches (common surnames), but all require investigation. |
| Rule 3B-2: Self-access | Filter for rows where User ID = Patient ID, or where User Name matches Patient Name. | Flag all self-access events. Employees accessing their own medical record through the EHR (rather than through a patient portal) is a policy violation in most organizations. |
| Rule 3B-3: Repeat access to a single non-assigned patient | Pivot table: Rows = User Name, Columns = Patient ID, Values = Count. Filter for counts > 1 where the user is not on the care team. | Flag any non-care-team user who accessed the same patient on multiple separate days. Repeated access without a care relationship is a strong relationship-access indicator. |
| Rule 3B-4: Sensitive section access by non-specialists | Filter Data Elements column for: “Mental Health,” “Psych,” “HIV,” “STI,” “Substance,” “Reproductive,” or equivalent terms. Then filter for users not in those specialty departments. | Flag non-specialist staff accessing sensitive record sections. A registration clerk viewing psychiatric notes or an orthopaedic nurse accessing HIV records requires an explanation. |
- Run Rule 3B-1 (surname match) across the full export — review all matches.
- Run Rule 3B-2 (self-access filter) — document every instance.
- Run Rule 3B-3 (repeat non-care-team access pivot) — investigate counts > 1.
- Run Rule 3B-4 (sensitive section access) — review each flagged event for clinical justification.
- Record all flagged users with “3B — Relationship access indicator.”
3C. Financially Motivated Breaches
Goal: Identify employees who accessed or extracted PHI for potential financial gain — identity theft, insurance fraud, or sale of information. These breaches involve data extraction, not just viewing.
| Rule | Excel Operation | What to Flag |
|---|---|---|
| Rule 3C-1: High-risk actions (print / export / copy) | Filter the “High-Risk Action?” helper column for “YES.” Sort by User. | Flag every print, export, download, or copy event. Review each for a legitimate administrative purpose (authorized records release, coding, billing task). Undocumented high-risk actions are priority flags. |
| Rule 3C-2: Volume outliers | Pivot table: Rows = User Name, Values = Count of distinct Patient IDs accessed. Sort descending. Calculate the average and standard deviation per role. | Flag any user whose patient access count exceeds 2x the average for their role. Example: if registration clerks average 45 patients per day and one clerk accessed 150, that is an outlier. |
| Rule 3C-3: Off-hours data extraction | Combine filters: “High-Risk Action?” = YES, AND “After Hours?” = YES, OR “Weekend?” = YES. | Flag all print/export actions occurring outside business hours or on weekends. Cross-reference with the employee’s scheduled shifts — extraction during unscheduled hours is the highest-priority financial breach indicator. |
| Rule 3C-4: Demographic/financial field focus | Filter Data Elements for “Demographics,” “Insurance,” “SSN,” “SIN,” “Billing,” or equivalents. Then filter for users whose role does not require that data. | Flag non-billing/non-registration staff who disproportionately accessed demographic and financial data. Clinicians rarely need insurance details — a nurse accessing insurance records for 30 patients is anomalous. |
- Run Rule 3C-1 — review every print/export/copy action in this review period.
- Run Rule 3C-2 (volume outlier pivot) — identify users exceeding 2x role average.
- Run Rule 3C-3 — list all off-hours extraction events and cross-reference with shift schedules.
- Run Rule 3C-4 — flag non-billing staff accessing demographic/financial fields.
- Record all flagged users with “3C — Financial breach indicator.”
=STDEV.S(IF(RoleColumn=TargetRole, CountColumn)) entered as an array formula (Ctrl+Shift+Enter). Any user more than 2 standard deviations above the role mean is a statistical outlier worth investigating.
3D. Malicious or Retaliatory Disclosure
Goal: Identify employees who deliberately accessed PHI to harm, embarrass, or intimidate a patient. These are often single, targeted events triggered by a personal grievance — the hardest category to detect proactively, but identifiable through correlation.
| Rule | Excel Operation | What to Flag |
|---|---|---|
| Rule 3D-1: Complaint-driven patient audit | If a patient has reported a suspected disclosure, filter for that Patient ID. List every user who accessed the record in the 30-60 days before the reported disclosure. Sort by date. | Flag every non-care-team user. The source of the disclosure is almost certainly among users who accessed the record in the relevant timeframe. |
| Rule 3D-2: HR-correlated access | Obtain from HR a list of employees currently involved in workplace conflicts, grievances, or disciplinary actions. Cross-reference their User IDs against the audit log to see if they accessed records belonging to the other party in the dispute. | Flag any access by a disputant to the other party’s medical record. This combination — active conflict + record access — is a high-severity indicator. |
| Rule 3D-3: Targeted sensitive-section access | Filter for sensitive data elements (HIV, psychiatric, substance use, reproductive) AND filter for users who accessed only one patient in those sections (not a pattern of clinical work). | Flag single, isolated access to a specific person’s sensitive records by a non-specialist. Unlike clinical access (which produces patterns across many patients), malicious access is typically a one-off targeted event. |
- Review any open patient privacy complaints — run Rule 3D-1 for each complainant.
- Request HR conflict/grievance list (appropriately de-identified) — run Rule 3D-2.
- Run Rule 3D-3 — identify isolated sensitive-section accesses by non-specialists.
- Record all flagged users with “3D — Malicious disclosure indicator.”
3E. Systemic and Process-Driven Breaches
Goal: Identify organizational failures — overly broad permissions, de-provisioning gaps, configuration errors — that expose patient records to unauthorized access at scale.
| Rule | Excel Operation | What to Flag |
|---|---|---|
| Rule 3E-1: Former employee access | Obtain from HR a list of employees who departed or changed roles in the past 90 days, with their departure dates. Use VLOOKUP or XLOOKUP to match departing employee User IDs against the audit log. Filter for access events after the departure date. |
Flag any access by a user after their recorded departure date. This indicates a de-provisioning failure — a reportable breach under HIPAA and most Canadian privacy statutes. |
| Rule 3E-2: Role-volume mismatch | Pivot table: Rows = User Role, Values = Average count of distinct patients accessed per user. Compare non-clinical roles (Admin, IT, Support) against clinical roles. | Flag non-clinical roles whose average patient access approaches or exceeds clinical roles. Administrative staff accessing 100+ patient records per day likely have permissions that are too broad. |
| Rule 3E-3: Post-change access spike | If a system upgrade, migration, or configuration change occurred during the review period, compare weekly access volumes before and after the change. Pivot table: Rows = Week Number, Columns = User Role, Values = Count. | Flag any role whose access volume increased significantly (>50%) after a system change. This suggests access controls were inadvertently reset or broadened. |
| Rule 3E-4: Cross-department access | Pivot table: Rows = User Name, Columns = Patient Department/Unit, Values = Count. Identify users accessing patients across multiple unrelated clinical units. | Flag users with access spanning 3+ unrelated clinical areas (e.g., Cardiology + Obstetrics + Psychiatry) without a role that justifies it (such as a hospitalist or social worker). This may indicate permission creep from prior role transfers. |
- Request the HR departure/transfer list — run Rule 3E-1 for post-departure access.
- Run Rule 3E-2 (role-volume pivot) — flag non-clinical roles with clinical-level access.
- If a system change occurred, run Rule 3E-3 — compare pre/post access volumes.
- Run Rule 3E-4 (cross-department pivot) — identify potential permission creep.
- Record all findings with “3E — Systemic breach indicator.”
Verify the Privacy Breaches
Step 3 produced a list of flagged access events — indicators that may represent privacy breaches. This step determines which flags are genuine breaches and which have legitimate explanations. Verification is essential: acting on unverified flags risks false accusations, which carry their own legal and reputational consequences.
For every flagged event from Step 3, work through the following verification checks in order. If the access is confirmed as legitimate at any stage, clear the flag and document the reason. If no legitimate purpose is established after all checks, the event proceeds to breach determination.
- Was the employee assigned to the patient’s care team at the time of access? Check your EHR’s care team assignment records, patient list reports, or consult the charge nurse / unit manager for the relevant shift.
- Was the employee involved through a consultation, referral, or float assignment? Float nurses, covering physicians, and consulting specialists may access records outside their home unit legitimately.
- If yes: mark “On Care Team?” as Y, note the assignment type, and clear the flag.
- Was the employee on shift at the time of access? Cross-reference the access timestamp against the employee’s scheduled shifts from staffing records or time-and-attendance systems.
- Was the employee assigned to the unit where the patient was located? A nurse scheduled on 4W Cardiology accessing a 4W patient is expected; the same nurse accessing an 8E Psychiatry patient is not, unless a float or consultation is documented.
- For after-hours or weekend flags: was the employee scheduled for night shift, weekend coverage, or on-call duty? If so, the timing flag may be explained.
- If the access occurred off-shift and off-unit with no documented assignment: the flag stands.
- Did the employee document a clinical note, enter an order, acknowledge a result, or perform any charting for this patient within a reasonable timeframe of the access (typically the same shift)?
- Check the audit log itself: is there an “Edit” action (not just “View”) for the same user + patient combination?
- If the access produced corresponding clinical documentation: the access is likely legitimate. Note the documentation in your findings and clear the flag.
- If the access was view-only with no corresponding documentation: this is the single strongest indicator of unauthorized access. The flag stands and the event should proceed to further investigation.
- Was there a legitimate administrative reason for the access? Check for authorized records requests, quality audits, coding or billing tasks, research protocols, or patient complaints being investigated.
- Did the employee use a “break-the-glass” emergency access override? If so, was the override justified and documented per your organization’s policy?
- If an administrative purpose is confirmed and documented: clear the flag and note the reason.
- If Checks 1-4 did not establish a legitimate purpose, the event is a confirmed indicator requiring further action.
- Document the finding: User, Patient, Date/Time, Action, Data Viewed, Category (3A-3E), and the fact that no legitimate purpose was identified through verification.
- Escalate to your organization’s investigation process: employee interview, HR involvement, and formal breach determination per policy.
- For systemic findings (3E): document the scope (number of affected records/patients) and escalate for immediate technical remediation.
- Retain all documentation — the raw audit log export, your working spreadsheet with flags, and your verification notes — in accordance with your retention policy (minimum six years under HIPAA).
Summary: The Complete Checklist at a Glance
| Step | Action | Output |
|---|---|---|
| 1. Export | Define scope, export audit logs as CSV, verify required fields, save securely | Clean CSV file with all required columns |
| 2. Prepare | Open in Excel, format as table, add 6 helper columns (Date, Time, After Hours?, Weekend?, High-Risk Action?, On Care Team?) | Analysis-ready spreadsheet |
| 3A. Curiosity | VIP patient user count, department mismatch, view-only filter, cluster timing | Flagged non-care-team access to VIP/high-profile patients |
| 3B. Relationship | Surname match, self-access, repeat non-assigned patient, sensitive section filter | Flagged personal-relationship access indicators |
| 3C. Financial | Print/export filter, volume outlier pivot, off-hours extraction, demographic field focus | Flagged data extraction anomalies |
| 3D. Malicious | Complaint-driven audit, HR conflict cross-reference, targeted sensitive-section access | Flagged retaliatory/malicious access indicators |
| 3E. Systemic | Former employee check, role-volume mismatch, post-change spike, cross-department access | Flagged organizational/process failures |
| 4. Verify | Care team check, scheduling check, documentation test, administrative purpose check, breach determination | Confirmed findings with documentation, or cleared flags with reasons |
Frequently Asked Questions
What is a healthcare privacy breach detection checklist?
A privacy breach detection checklist is a structured, repeatable procedure for reviewing EHR audit logs and identifying unauthorized access to patient records. It covers four steps: exporting audit logs, preparing the data in Excel, applying specific detection rules for each of the five breach categories (curiosity snooping, personal relationship access, financial, malicious, and systemic), and verifying flagged events through contextual investigation. It is designed to be used as a working document during each review cycle.
What Excel filters are most effective for detecting employee snooping?
The most effective filters for snooping detection are: (1) Filter by a high-risk patient, then count unique users — if the count far exceeds the care team, investigate the excess; (2) Filter for “View” actions without a corresponding “Edit” action by the same user for the same patient (the documentation test); (3) Use a pivot table (Rows=User, Columns=Date, Values=Count) to spot access clusters after triggering events. For relationship-based snooping, VLOOKUP matching employee surnames to patient names surfaces obvious cases quickly.
How do you verify whether a flagged audit log event is actually a privacy breach?
Verification follows four checks in sequence: (1) Was the employee on the patient’s care team, including float or consultation assignments? (2) Was the employee on shift and assigned to the relevant unit? (3) Did the employee produce clinical documentation (a note, order, or result acknowledgment) for that patient? (4) Was there a documented administrative purpose? If all four checks fail to establish a legitimate reason, the event is a confirmed indicator requiring formal investigation, including an employee interview.
How often should a privacy officer run this checklist?
Run the full checklist monthly as a baseline. Run VIP patient checks (Step 3A) within 48 hours of any high-profile admission. Run high-risk action reviews (Step 3C: print/export) weekly. Trigger immediate runs after newsworthy incidents, media reports involving patients, or internal complaints. Former employee audits (Step 3E) should run monthly in coordination with HR departure lists. Document every run, including null results — regulators value demonstrated process, not just findings.
What is the “documentation test” in audit log analysis?
The documentation test is the single most reliable heuristic for identifying unauthorized access. In legitimate clinical work, an employee who views a patient record will almost always also act on it — documenting a note, placing an order, or acknowledging a result. If the audit log shows only a “View” action with no corresponding clinical documentation by that user for that patient, the access may not have had a legitimate purpose. It is not proof alone, but it is the strongest signal for further investigation.
What are the limitations of running this checklist manually in Excel?
Manual execution is limited in five key ways: (1) Excel cannot handle the millions of rows a large hospital generates monthly; (2) it requires manual export and refresh each cycle; (3) it cannot automatically cross-reference access with care team assignments, schedules, or clinical documentation; (4) it provides no real-time alerting while a breach is occurring; (5) it depends entirely on the reviewer’s consistency and available time. These limitations mean manual review will miss breaches that continuous automated monitoring would catch — particularly low-frequency, single-event incidents.
Sources and References
- NIH / PMC — Using Electronic Health Record Audit Log Data for Research (2022) — audit log data elements and structure
- Journal of AHIMA — Shifting from Reactive to Proactive HIPAA Audits — proactive monitoring imperative
- Federal Register (2025-01-06) — HIPAA Security Rule To Strengthen the Cybersecurity of Electronic Protected Health Information — proposed rule on suspicious activity detection
- U.S. Department of Health & Human Services — HIPAA Audit Protocol (45 CFR § 164.312(b), § 164.530(j)) — audit log and retention requirements
- AccountableHQ — Audit Controls to Detect HIPAA Employee Snooping: A Practical Guide
- AccountableHQ — Investigating and Reporting Employee PHI Breaches: Step-by-Step Guide
- Bluesight — How to Monitor EHR Access Patterns for HIPAA Compliance
- Bluesight — Building a Repeatable Workflow for Privacy Investigations
- Kiteworks — HIPAA Audit Logs: Complete Requirements for Compliance
- Ontario Information and Privacy Commissioner — Stamping Out Snooping Once and for All
- HHS Office for Civil Rights — Enforcement actions and penalty data (2025)
This Checklist Takes Hours. RiskIntelligence Privacy Monitor Runs It Continuously.
Every rule in this checklist — user count analysis, care team cross-referencing, volume outlier detection, after-hours flagging, former employee auditing — is something RiskIntelligence Privacy Monitor does automatically, continuously, across every department and every access event, in near real-time.
Your privacy team’s time is better spent investigating confirmed indicators than building pivot tables. Let the checklist run itself.
Move from monthly spreadsheets to continuous detection. See what Privacy Monitor can find in your audit logs.
