ExamNova

Correlate failed passwords with successful sign-ins

A hands-on CS0-004 lab. You produce the real artefact and 8 automated checks verify it behaves the way the exam expects.

Try this labAll CS0-004 practice

Certification
CS0-004
Format
SQL query
Difficulty
easy
Estimated time
25 min
Automated checks
8

The brief

Write one read-only query over auth_events, accounts and trusted_sources. Return exactly success_id, account_id, source_ip, success_second and failed_attempts, one row per qualifying password-method success. A candidate belongs to an enabled human account in accounts, has a source IP absent from trusted_sources, and follows at least three distinct event_id values with outcome failure, reason bad_password and method password for the same account AND source. Count only failures from success_second minus 300 inclusive to success_second exclusive. Use event time rather than arrival order. Count unique event IDs, preserve separate attempts that share a timestamp, and deduplicate replayed successes. Row order is unrestricted. NULL trusted-source entries match no IP. Derive the result for changing identifiers and empty or mixed populations. Repair failed checks without resetting your query.

What the checks verify

Your work is graded on 8 independent properties, not on matching one reference answer.

  • The artifact is a single safe read-only query over the normalized evidence tables.
  • The handover result exposes success_id, account_id, source_ip, success_second and failed_attempts in that column order.
  • Every qualifying success appears once with its actual number of distinct preceding failed password events, including multiple accounts and an empty result.
  • Failures belong to the same account and source IP as the success; neither another user nor another origin contributes.
  • Failures fall in the individual success window from success_second minus 300 inclusive to success_second exclusive.
  • Only password-method failures whose reason is bad_password count toward a password-method success.
  • Only enabled human accounts present in accounts and origins absent from trusted_sources enter this triage queue.
  • Collector replays do not inflate distinct failed-event counts or duplicate a success row, and the query derives evidence for unseen identifiers.

Where this sits in the CS0-004 blueprint

Domain
Security Operations
Objective
Security Analysis Tools
Skill
Evidence Queries and Tool Selection

Part of CS0-004 preparation

CS0-004

CompTIA Cybersecurity Analyst (CySA+)

All CS0-004 practiceAll labs

Labs are written by ExamNova to teach the decisions the exam tests. They are not reproductions of vendor lab content.