Data analysis at the command line
You filter, count, join and summarise CSV and JSON data with cut, sort, uniq, awk, jq and csvkit, without installing anything heavy.
Level 101 · 17 lessons · 18 practice labs
Lessons
Text tools
- A first look at data: head, tail, wc
- Splitting columns: cut and delimiters
- Frequency tables: sort and uniq -c
- Filtering lines: grep and regular expressions
- Quick task: the ten IPs with the most requests
Calculating with awk
- Selecting and summing fields with awk
- Grouping with awk arrays
- Joining two files: join and paste
- Quick task: bytes transferred per user
Structured data tools
- CSV with csvkit: csvcut, csvgrep, csvstat
- SQL on CSV with csvsql
- Filtering JSON with jq
- Reshaping JSON into CSV with jq
- Record processing with Miller (mlr)
- Quick task: summarise a JSON alert file
Repeatable analysis
- Turning pipelines into scripts
- Capstone: a script that produces a daily login summary
Practice labs
- A first look at an unknown file: Using only head, tail and wc, find the line count, delimiter and header of an unnamed file from Kestrel Logistics and write them to notes.txt.
- The endpoints with the most errors: Pick out the requests that returned 5xx in an API access log and use cut, sort and uniq -c to rank the five endpoints with the most errors.
- Spaced fields and one-off visitors: Split the columns correctly in a log whose user-agent field contains spaces and count the IP addresses that appear only once.
- Trace one user's activity: Filter every failed login for a given user out of an authentication log with grep and report how many there were and when the first one happened.
- Separate internal addresses with a regex: Use grep -E to exclude sources in 10.0.0.0/8 and 192.168.0.0/16 and write only the requests from external IPs to external.log.
- A pattern that drops false matches: Fix a naive pattern that also matches version numbers and write a grep command that captures only valid IPv4 addresses.
- Column sum and average: Use awk to compute the total weight and the per-flight average in Pervane Airlines' baggage scale log.
- Requests per hour with an awk array: Extract the hour from a web log's timestamps and use an awk array to build a sorted table of requests per hour.
- Report users over a threshold: Use awk to total the bytes downloaded per user and list, in MB, everyone above three times the average.
- Join two lists on a common field: Join the staff list and the badge-reader log on employee number so every entry shows the employee's name.
- Find the unmatched rows: Compare an asset list with a scan result and write the unscanned machines and the unknown, unlisted machines to two separate files.
- Safe joins on unsorted files: Fix a join command that drops results because of sort order and locale and prove the correct number of matches.
- A quick profile with csvstat: Use csvstat on Selvi Municipality's application file to find the columns with empty values and the most common application type.
- Summarise alarms with jq: Use jq to count a JSON alarm feed by severity and export only the critical ones' sources and targets to CSV.
- A repeatable report script with Miller: Write a shell script using mlr and jq that takes the input file as an argument and produces a daily failed-login summary.
- Counting with SQL on a CSV: Use csvsql on Tessera Insurance's claims file to produce the number of claims and the average amount per city in one query.
- From JSON to CSV to a summary: Convert JSON events to CSV with jq and, in the same pipeline, produce a per-user event summary with csvkit.
- Harden a brittle pipeline: Add error checks and exit codes to a script that silently gives wrong results on empty input and missing columns and test three failure cases.
About this course
You filter, count, join and summarise CSV and JSON data with cut, sort, uniq, awk, jq and csvkit, without installing anything heavy.
What you will learn
- Text tools
- Calculating with awk
- Structured data tools
- Repeatable analysis
Prerequisites
No prior knowledge is needed; the course starts from zero.
Lab environment
Lessons run in your own Kali Linux terminal that opens in the browser. Nothing to install; the lab starts clean in every lesson.
Frequently asked questions
- Is this course right for my level?
- The course is at beginner level; every term is explained where it first appears.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.