Correlation Without a SIEM: Joining Logs by Hand and by Script
An alert fires: twenty-five failed logins for one account, then a success. You know what that probably means. What you do not know — and what the alert cannot tell you — is what happened next. Did that account open a file share? Connect over the VPN? Cause the firewall to log an unusually large outbound transfer? The answers are in your logs. They are just in four different files, in four different formats, with four different spellings of the username, and the clock is ticking.
Pulling those scattered lines into one ordered story is called correlation, and it is the job that makes a full SIEM feel intelligent. It is also, at small scale, a job you can do yourself. You need a shell, a couple of short scripts, and some discipline about how your logs are stored. This guide shows you how. It covers the three keys that let one log line be matched to another, and a method for building a timeline. It offers generic script sketches that merge sources by user or host. There is a worked example that starts from that failed-then-successful login. There is also an honest list of the points at which hand-joining stops being practical.
You will not need a product for any of it. You will need the logs to already be arriving in one place. The sources are ranked in the logs that matter most. You will also need to have done the small amount of normalization described below before the incident, not during it.
In this guide
- The three join keys
- Join keys by source: a reference table
- Normalize first, or every join is manual
- The timeline method
- Scripts that pull one user or host from every source
- A worked example: failed, then succeeded, then what?
- Where hand-joining breaks
- Keeping the scripts as a kit
- Where to go next
The three join keys
A join key is a value that two log lines from different sources share, which lets you say "these describe the same thing." In security logs there are only three that matter, and every correlation you will ever do uses at least two of them.
Timestamp
Time is the universal key. Suppose the VPN says a tunnel came up at 09:07:56 and the file server says a session opened at 09:09:30. You can place them in order without either system knowing the other exists. This only works if every source's clock agrees. A domain controller that is four minutes fast will put its events in the wrong place on every timeline you build. And you will draw the wrong conclusion about what caused what. Synchronized clocks are the prerequisite for everything else on this page. Make sure every source keeps time from a common NTP reference. Run a timestamp sanity check across your sources before you trust a single cross-source comparison. The other trap is time zones: convert everything to UTC at collection time. That way, "09:07:56" means the same instant in every file.
Hostname or IP address
The machine involved. Some sources record a hostname, some an address, and some both. So the key needs a translation step. DHCP lease logs tell you which address a machine held at a given time. DNS or your inventory tells you which hostname belongs to which machine. Be careful with addresses that move. A VPN client is assigned an internal address for the life of its tunnel and a different one next time. So an address is only a reliable key within a time window where you know who held it.
Username
The account involved, and the key with the most spelling problems. The directory writes JSMITH, and the VPN writes jsmith@corp.local. The file server writes CORP\jsmith. The firewall, if it knows the user at all, writes whatever the identity integration handed it. Before any join, reduce every spelling to one canonical form — lowercase, no domain prefix or suffix. Keep the raw form alongside so you can prove where a line came from.
Join keys by source: a reference table
| Source | Timestamp as logged | Host key | User key | Notes |
|---|---|---|---|---|
| Directory / domain controller | Local time, event log format | Workstation name and source address | Account name, often uppercase, domain separate | Logon type field distinguishes interactive, network, remote |
| VPN concentrator | Syslog, often local time | Outside source address plus assigned tunnel address | Usually user@domain | Tunnel address is the key for everything the session did inside |
| RADIUS | Syslog or event log | Access point or switch, client hardware address | Account name, sometimes with realm | Hardware address links to DHCP lease |
| Firewall | Syslog, may be UTC or local | Source and destination addresses | Only if identity-aware; often absent | Join to a user via the address the user's host held at the time |
| DNS server | Query log, local time typical | Client address | Absent | Same address-to-user step as the firewall |
| File server audit | Local time, event log format | Client address | Account name with domain | Object-access events are voluminous; filter by share |
| Endpoint protection | Console format, often UTC | Hostname | Logged-on user, sometimes | Hostname to address via DHCP or inventory |
| Email gateway | Export format, often UTC | Absent or sender address | Mailbox address | Mailbox address to account name via directory |
Normalize first, or every join is manual
Everything below assumes one small piece of preparation. Each source is transformed, as it arrives or on a schedule, into a common line format with the join keys in fixed positions. A tab-separated line with six fields is enough. They are UTC time, source name, canonical user, host or address, event type, and a free-text detail field. That last field holds whatever else the original line said. One short script per source does the transformation. Here is the shape of one for a VPN concentrator, showing the raw line it reads and the normalized line it writes:
# normalize-vpn.sh: raw syslog line in, common format out # in: Mar 14 09:07:56 vpn-gw sslvpn: user=JSmith@corp.local src=203.0.113.9 event=login-ok tunnel=10.99.0.41 # out: 09:07:56Z vpn jsmith 203.0.113.9 login-ok tunnel=10.99.0.41 host=vpn-gw # # fields: time(UTC) source user host_or_addr event detail # (date column omitted here for width; keep it in the real file)
If you skip this step, every correlation becomes a manual translation exercise done under pressure. Which column is the user in this file? What time zone is that? Is this the same account as that one? Ten minutes per source spent writing the normalizer saves an hour per incident, forever.
The timeline method
The output of correlation is a timeline: one list, oldest event first, containing every relevant line from every source, each tagged with where it came from. Building it is mechanical once the logs are normalized. Pick a starting event and choose the key you will follow (usually the user, sometimes the host). Pull every line matching that key from every source within a window on either side of the start. Sort by time and read from the top. As you read, you will find new keys — an assigned tunnel address, a second host. Pull those too, widening the picture. The general discipline of reconstructing what happened when, including how to handle gaps and conflicting clocks, is laid out in incident timeline reconstruction. This guide is the log-mechanics half of that method.
Two reading habits make a timeline useful rather than merely long. First, annotate as you go. Next to each line that matters, write one sentence in your own words about what it shows and which key led you to it. The raw lines are evidence; your sentences are the story. And they are what you will hand to a colleague, a manager, or an insurer. Second, treat gaps as findings. Suppose the account was active at 09:09 and again at 09:29 with nothing in between. Either nothing happened, or something happened on a source you are not collecting. Knowing which is often the most important thing the timeline tells you.
Scripts that pull one user or host from every source
With normalized files in one directory, the core correlation tool is a script that takes a key and a time window. It prints every matching line from every source, sorted. Here is a generic sketch in plain shell; it is illustrative, not a finished program, and the same logic is a few lines in any scripting language:
#!/bin/sh
# pull-user.sh <user> <from-time> <to-time>
# Every normalized event for one user, from every source, oldest first.
u=$(printf '%s' "$1" | tr 'A-Z' 'a-z')
cat /var/log/normalized/*.log \
| awk -F'\t' -v u="$u" -v from="$2" -v to="$3" \
'$3 == u && $1 >= from && $1 <= to' \
| sort -k1,1
Three variants cover almost every question you will ask. Pull by host is the same script matching the fourth field instead of the third, which is how you follow an address once you know who held it. Pull by window drops the key entirely and prints everything from every source between two times. That is what you want when you know when something happened but not yet who. And pull with expansion runs pull-by-user and harvests every address and host that appears in the result. It runs pull-by-host for each of them. A loop of a dozen lines does in seconds what takes a person twenty minutes of copying and pasting.
Keep the windows narrow at first. An hour on either side of the starting event is usually enough to see the shape of what happened. Widen it only once the shape suggests an earlier beginning.
A worked example: failed, then succeeded, then what?
The failures-then-success rule from the starter detections fires for the account jsmith. The alert gives you the account, the source address, and the time of the success. Run the pull-by-user script for that account from one hour before to one hour after, and read.
$ ./pull-user.sh jsmith 08:07:55Z 10:07:55Z 09:02:10Z directory jsmith 203.0.113.9 login-fail vpn-gw, bad password 09:02:14Z directory jsmith 203.0.113.9 login-fail vpn-gw, bad password ... 23 more failures, same source, every few seconds ... 09:07:55Z directory jsmith 203.0.113.9 login-ok vpn-gw 09:07:56Z vpn jsmith 203.0.113.9 tunnel-up tunnel=10.99.0.41 09:09:30Z directory jsmith 10.99.0.41 login-ok fs01, network logon 09:09:41Z fileserver jsmith 10.99.0.41 read \\fs01\finance\ (412 objects in 4 min) 09:31:17Z vpn jsmith 203.0.113.9 tunnel-down -
Walk through what the timeline tells you, line by line. The failures come every few seconds from one outside address — too fast and too regular for a person at a keyboard. The success comes from the same address, so whoever was guessing is the same party who got in. One second later the VPN assigns a tunnel address, 10.99.0.41. This is a new key, and everything that address did inside the network is now part of the story. Ninety seconds after that, the same account logs on to the file server from that tunnel address. Within four minutes it has read hundreds of objects under the finance share — far more than anyone reads by hand. The tunnel closes twenty minutes later.
The user key has taken you as far as it can, because the firewall and DNS do not record usernames. Now switch keys: pull by host for 10.99.0.41 over the life of the tunnel.
$ ./pull-host.sh 10.99.0.41 09:07:56Z 09:31:17Z 09:09:30Z directory jsmith 10.99.0.41 login-ok fs01, network logon 09:09:41Z fileserver jsmith 10.99.0.41 read \\fs01\finance\ (412 objects in 4 min) 09:14:02Z dns - 10.99.0.41 query files.transfer-host.example 09:14:03Z firewall - 10.99.0.41 allow -> 198.51.100.23:443 out=380 MB 09:29:50Z firewall - 10.99.0.41 allow -> 198.51.100.23:443 session end
Two new lines appear that the user key could never have found. Right after the file reads, the tunnel address looks up a name nobody on the network has looked up before. The firewall records a long outbound session to the address that name resolved to, with hundreds of megabytes leaving. You now have, from five sources and two keys, a complete story: guessed credential, VPN entry, bulk read of the finance share, outbound transfer, exit. That story took about five minutes to assemble. It tells you what to do next. Reset the credential, block the destination, and identify what was in those 412 objects. Check whether the same source address touched any other account.
Tip: save the exact commands and their output to a file the moment you run them, before you start acting on what they show. During an incident you will be tempted to fix things first and document later. Later, the log windows will have rotated, your memory will have blurred, and the auditor will ask for the timeline anyway.
Where hand-joining breaks
This method scales further than you might expect, but it does have edges, and it is better to know them before you hit one during an incident.
- Volume. Reading every normalized file for every query is fine while the files are a few hundred megabytes. When the DNS and firewall logs push the daily total into many gigabytes, each pull takes minutes. An investigation of thirty pulls then takes an afternoon. Splitting files by day and by source postpones this; it does not remove it.
- Many sources. Each new source means a new normalizer, a new spelling of the username, and a new column of quirks. Six sources is comfortable. Fifteen is a part-time job of maintenance before you have investigated anything.
- Time pressure. The scripts above are fast when they already exist. Writing a new one at 11 PM, while a file server is being encrypted, is not the moment for shell quoting. If you find yourself improvising queries under pressure more than occasionally, that is the signal.
- No normalization. If the preparation step was skipped, every join is a manual translation, and hand correlation degrades from minutes to hours. This is the most common reason the method fails, and it is entirely avoidable.
- Patterns across days. Hand-joining is good at "what happened around this event." It is poor at "has this happened slowly, over weeks" — a login from a new place once a fortnight, a few files a day. Nobody runs a pull for a question they have not thought to ask.
When two or more of these are true on a typical week, you have outgrown the method, not failed at it. The honest signs and the options are the subject of when a real SIEM is worth it.
Keeping the scripts as a kit
The difference between "some scripts Sam wrote" and a tool the team can rely on is entirely documentation and habit. Keep everything in one directory under version control, with a plain-text readme that a colleague could follow at 3 AM without calling you. Something like this is enough:
correlation-kit/
README.txt what each script does, usage, where the logs live
normalize/ one script per source; header comment shows in/out lines
normalize-directory.sh
normalize-vpn.sh
normalize-firewall.sh
normalize-dns.sh
normalize-fileserver.sh
pull-user.sh by user key
pull-host.sh by host or address key
pull-window.sh everything between two times
expand.sh pull-user, then pull-host for every address found
examples/ saved output from past investigations, dates removed
CHANGELOG.txt what changed and why (a normalizer fix, a new source)
Three habits keep the kit alive. Run each normalizer against a fresh sample of its source once a month. Vendors change formats without asking, and a normalizer that silently drops the user field breaks every join downstream. Rehearse the worked example above once a quarter against real, boring data. Pick a colleague's account, pull their morning, and confirm the timeline makes sense. Then the first time you use the kit in anger is not the first time you use it. And record every real investigation's commands and output in the examples directory. They become both the training material for the next person and the evidence trail for the auditor.
Where to go next
Correlation is the job that connects the other four. The sources come from the prioritized source list. The starting events come from the starter detections. And the whole picture of what a SIEM does — and what the lite version keeps — is in what a SIEM does. All of it lives under Security Log Monitoring on a Budget (SIEM-lite), and the last guide there is the one to read when the kit starts to feel small.
Put this into practice with SysPeep Monitor
Every join on this page depends on clocks that agree and sources that keep sending — two things that fail quietly. SysPeep Monitor keeps an eye on the domain controllers, VPN gateway, firewall, DNS and file servers behind your logs over SNMP and WMI. It alerts when one goes down or drifts out of reach. It gives you the uptime history to line up next to your timeline. The free trial takes minutes to install on a Windows machine.
Download Free TrialLearn About SysPeep