QA Portfolio – Log Analysis & Data Validation

This project shows my QA skills in analyzing logs, debugging, and validating data using Linux commands and SQL queries.

---

Table of Contents

• Project Overview
• Log Analysis
  • Exercise 1 – IP Filtering
  • Exercise 2 – HTTP Error Analysis
• Database
  • Exercise 1 – Total Cabs
  • Exercise 2 – Companies with Fewer than 100 Vehicles
  • Exercise 3 – Weather Conditions for Trips
  • Exercise 4 – Trips per Company (Nov 15-16, 2017)
• Skills Highlighted

---

Project Overview

This Sprint focused on analyzing Apache server logs and a Chicago taxi database to detect errors, unusual patterns, and potential bugs. The goal was to validate the system, identify problematic endpoints or companies, and prepare actionable insights for the development team.

---

Log Analysis

Exercise 1 – IP Filtering

Objective: Identify all log entries from IPs starting with 233.201.

> Command used:

grep '^233\.201' logs/2019/12/*

> Representative output:

logs/2019/12/apache_2019-12-18.txt:233.201.188.154 - - [18/12/2019:21:46:01 +0000] "DELETE /events HTTP/1.1" 403 3971 logs/2019/12/apache_2019-12-21.txt:233.201.182.9 - - [21/12/2019:21:56:20 +0000] "PATCH /users HTTP/1.1" 400 4118

> Analysis:

• Detected unauthorized or failing requests from multiple IPs.
• Highlights endpoints with potential security issues or misconfigurations.

---

Exercise 2 – HTTP Error Analysis

Objective: Organize log entries by HTTP status codes for a specific date (Dec 30, 2019).

> Directory setup:

mkdir -p ~/bug1/events

> Extract logs for the date:

grep '30/12/2019' logs/2019/12/* > ~/bug1/main.txt

> Separate logs by error code:

grep ' 400 ' ~/bug1/main.txt > ~/bug1/events/400.txt
grep ' 500 ' ~/bug1/main.txt > ~/bug1/events/500.txt

> Example of 400 errors (partial):

80.57.170.51 - - [30/12/2019:21:35:12 +0000] "DELETE /users HTTP/1.1" 400 3623 204.235.176.118 - - [30/12/2019:21:35:13 +0000] "POST /users HTTP/1.1" 400 4704

> Example of 500 errors (partial):

64.250.112.189 - - [30/12/2019:21:35:13 +0000] "PUT /parsers HTTP/1.1" 500 4639 193.253.101.180 - - [30/12/2019:21:35:31 +0000] "PATCH /alerts HTTP/1.1" 500 2944

> Analysis:

• Errors grouped by status code to prioritize debugging.
• 400 errors mostly involve bad requests to endpoints like /users and /events.
• 500 errors indicate server-side issues in endpoints like /parsers and /alerts.

---

Database

Exercise 1 – Total Cabs

Objective: Validate data integrity using SQL queries. Count total vehicles in the cabs table.

> Example queries:

SELECT COUNT(DISTINCT vehicle_id) FROM cabs;

> Result:

Total Cabs
5500

> Analysis:

• Confirms how many taxis are actually available on the streets.
• Helps identify service coverage compared to expected demand.

---

Exercise 2 – Companies with Fewer than 100 Vehicles

Objective: Identify taxi companies with fewer than 100 cars.

> Query:

SELECT company_name, COUNT(DISTINCT vehicle_id) AS cnt
FROM cabs
GROUP BY company_name
HAVING COUNT(DISTINCT vehicle_id) < 100
ORDER BY cnt DESC;

Companies with fewer than 100 vehicles (top 10 sample):

Company Name	Count
Nova Taxi Affiliation Llc	97
Patriot Taxi Dba Peace Taxi Associat	89
Blue Diamond	85
Checker Taxi Affiliation	81
Chicago Medallion Management	80
Chicago Independents	69
24 Seven Taxi	67
Checker Taxi	60
American United	55
Chicago Medallion Leasing INC	53

> Analysis:

• Reveals companies under capacity.
• Useful for prioritizing QA checks and reporting service issues.

---

Exercise 3 – Weather Conditions for Trips

Objective: Classify each hour as Good or Bad based on weather conditions.

> Query:

SELECT ts,
       CASE
           WHEN description ILIKE '%rain%' OR description ILIKE '%storm%' THEN 'Bad'
           ELSE 'Good'
       END AS weather_conditions
FROM weather_records
WHERE ts >= '2017-11-05 00:00:00' AND ts < '2017-11-06 00:00:00';

Weather conditions (Top 10 sample):

Timestamp	Weather Conditions
2017-11-05 00:00:00	Good
2017-11-05 01:00:00	Bad
2017-11-05 02:00:00	Good
2017-11-05 03:00:00	Good
2017-11-05 04:00:00	Bad
2017-11-05 05:00:00	Bad
2017-11-05 06:00:00	Good
2017-11-05 07:00:00	Good
2017-11-05 08:00:00	Good
2017-11-05 09:00:00	Good

> Analysis:

• Supports validation of trip cost coefficients depending on weather.
• Splits hours into Good vs Bad for testing business rules.

---

Exercise 4 – Trips per Company (Nov 15-16, 2017)

Objective: Count trips per taxi company for a specific period.

> Query:

SELECT c.company_name, COUNT(t.trip_id) AS trips_amount
FROM cabs AS c
JOIN trips AS t ON c.cab_id = t.cab_id
WHERE t.start_ts >= '2017-11-15 00:00:00' AND t.start_ts < '2017-11-17 00:00:00'
GROUP BY c.company_name
ORDER BY trips_amount DESC;

Trips per taxi company (top 10 sample):

Company Name	Trips Amount
Flash Cab	19558
Taxi Affiliation Services	11422
Medallion Leasin	10367
Yellow Cab	9888
Taxi Affiliation Service Yellow	9299
Chicago Carriage Cab Corp	9181
City Service	8448
Sun Taxi	7701
Star North Management LLC	7455
Blue Ribbon Taxi Association Inc.	5953

> Analysis:

• Shows which companies had the most trips.
• Useful for QA to detect inconsistencies between reported and actual trips.

---

Skills Highlighted

• Log analysis with grep, awk, sort, and Linux file operations

• Organizing and filtering large datasets by error type

• SQL queries for data validation and insight extraction

• Problem-solving mindset to detect patterns and root causes

• Documentation of analysis in a professional, reproducible format