labs/Lab-2/Lab-2-Practical/06_Capstone_Auth_Log_Analysis_Lab.ipynb

Module 2 — Lab 6: Capstone — Investigating Suspicious Logins

Topics: Pandas, NumPy, Matplotlib, export, analyst summary

Instructions: Run the cells in order. Complete every TODO, then run the Self-check cell. Do not open the Solution notebook before attempting the tasks yourself.

Mission

As a junior security analyst, you must build a complete workflow:

1. load and clean authentication logs;
2. identify IP addresses with repeated failed logins;
3. identify off-hours activity;
4. visualize the results;
5. export the data and write a short conclusion.

In [ ]:

from pathlib import Path
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

DATA_PATH = Path("data/auth_log.csv")
OUTPUT_DIR = Path("output")
OUTPUT_DIR.mkdir(exist_ok=True)

1. Write a reusable cleaning function

In [ ]:

def clean_auth_log(df):
    result = df.copy()
    # TODO 1: drop duplicate rows
    # TODO 2: parse timestamp using errors="coerce" and utc=True
    # TODO 3: drop rows missing timestamp, source_ip, or status
    # TODO 4: normalize source_ip, username, and status
    # TODO 5: retain SUCCESS and FAILED records only
    # TODO 6: create hour and is_off_hours (< 6 or >= 22)
    # TODO 7: sort by timestamp and reset the index
    return result


raw_df = pd.read_csv(DATA_PATH)
clean_df = clean_auth_log(raw_df)
print(f"Raw rows: {len(raw_df)} | Clean rows: {len(clean_df)}")
clean_df.head()

2. Analyze failures and rank IP addresses

In [ ]:

# TODO 8: filter FAILED records
failed_df = None

# TODO 9: group by source_ip, count, sort descending, and name the Series failed_attempts
failed_by_ip = None

THRESHOLD = 3
# TODO 10: retain IP addresses with count >= THRESHOLD
suspicious_ips = None

print(failed_by_ip)
print(suspicious_ips)

3. Calculate additional investigation metrics

In [ ]:

# TODO 11: create status_counts in SUCCESS, FAILED order
status_counts = None

# TODO 12: filter off-hours failed events
off_hours_failed = None

# TODO 13: determine top_ip, top_ip_count, and failure_rate (%)
top_ip = None
top_ip_count = None
failure_rate = None

print(f"Top suspicious IP: {top_ip} ({top_ip_count} failures)")
print(f"Failure rate: {failure_rate}%")
print("Off-hours failed attempts:", None if off_hours_failed is None else len(off_hours_failed))

4. Visualize the findings

In [ ]:

# TODO 14: create a figure with two charts:
# - a SUCCESS/FAILED bar chart
# - a horizontal bar chart of the top five IP addresses by failed attempts
# TODO 15: add titles and labels, save to output/auth_log_analysis.png, and call plt.show()

5. Export results and write a short report

In [ ]:

# TODO 16: export clean_df and suspicious_ips to CSV
# TODO 17: create a summary string containing clean event count, FAILED count,
# failure rate, top IP, off-hours failures, and recommendations.
# TODO 18: save summary to output/capstone_summary.txt

Self-check

In [ ]:

assert len(raw_df) > len(clean_df)
assert clean_df.duplicated().sum() == 0
assert failed_by_ip.index[0] == "198.51.100.14"
assert top_ip_count >= THRESHOLD
assert len(off_hours_failed) >= 1
assert (OUTPUT_DIR / "auth_log_analysis.png").exists()
assert (OUTPUT_DIR / "capstone_summary.txt").exists()
print("✓ Capstone lab completed")

Report questions

1. Which IP address should be investigated first, and what evidence supports that decision?
2. In what situations could a threshold of three failed attempts produce a false positive?
3. Which additional log sources should be reviewed before deciding to block an IP address?